(精华)2020年8月11日 C#基础知识点 手写ORM数据库增删改查
public class ServiceDal : IServiceDal
{<!-- -->
public static readonly string Customers = ConfigurationManager.ConnectionStrings["Customers"].ToString();//获取配置文件中连接字符串
/// <summary>
/// 增
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public bool Add<T>(T t) where T : BaseModel
{<!-- -->
Type type = typeof(T);
object oCompany = Activator.CreateInstance(type);
//Richard Id 是自动增长的，sql语句中应该去除Id的字段
// Richard GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly) 过滤掉继承自父类的属性
string props = string.Join(",", type.GetProperties().Where(p => !p.Name.Equals("Id"))
//GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly)
.Select(a => $"[{a.Name}]"));
string paraValues = string.Join(",", type.GetProperties().Where(p => !p.Name.Equals("Id"))
//GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly)
.Select(a => $"@[{a.Name}]"));
string sql = $"Insert [{type.Name}] ({props}) values({paraValues})";
var parameters = type.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly).Select(item => new SqlParameter()
{<!-- -->
ParameterName = $"@{item.Name}",
SqlValue = $"{item.GetValue(t)}"
});
// Richard 在拼接sql语句的时候，尽管ID 是Int类型，还是建议大家使用Sql语句参数化
//（防止sql注入）
using (SqlConnection connection = new SqlConnection(Customers))
{<!-- -->
SqlCommand sqlCommand = new SqlCommand(sql, connection);
sqlCommand.Parameters.AddRange(parameters.ToArray());
connection.Open();
return sqlCommand.ExecuteNonQuery() > 0;
}
}
/// <summary>
/// 删
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public bool Delete<T>(T t) where T : Advanced.Model.BaseModel
{<!-- -->
Type type = t.GetType();
string sql = SqlBuilder<T>.DeleteSql;
//string sql = $"Delete from [{type.Name}] where Id=@Id";
using (SqlConnection connection = new SqlConnection(Customers))
{<!-- -->
SqlCommand sqlCommand = new SqlCommand(sql, connection);
sqlCommand.Parameters.Add(new SqlParameter("@Id", t.Id));
connection.Open();
return sqlCommand.ExecuteNonQuery() > 0;
}
}
/// <summary>
/// 查
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public T Find<T>(int id) where T : BaseModel
{<!-- -->
Type type = typeof(T);
object oCompany = Activator.CreateInstance(type);
//string sql = $"SELECT {string.Join(",", type.GetProperties().Select(a => $"[{a.Name}]")) } FROM [{type.Name}] where Id=@Id";
string sql = SqlBuilder<T>.FindSql;
// Richard 在拼接sql语句的时候，尽管ID 是Int类型，还是建议大家使用Sql语句参数化
//（防止sql注入）
using (SqlConnection connection = new SqlConnection(Customers))
{<!-- -->
SqlCommand sqlCommand = new SqlCommand(sql, connection);
sqlCommand.Parameters.Add(new SqlParameter("@Id", id));
connection.Open();
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.Read()) //开始读取
{<!-- -->
ReaderToList(type, oCompany, reader);
return (T)oCompany;
}
else //Richard
{<!-- -->
return null;
}
}
}
#region Private
private static void ReaderToList(Type type, object oCompany, SqlDataReader reader)
{<!-- -->
foreach (var prop in type.GetProperties())
{<!-- -->
//Richard DBnull
prop.SetValue(oCompany, reader[prop.Name] is DBNull ? null : reader[prop.Name]);
}
}
#endregion
/// <summary>
/// 查所有
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public List<T> FindAll<T>() where T : Advanced.Model.BaseModel
{<!-- -->
Type type = typeof(T);
//string sql = $"SELECT {string.Join(",", type.GetProperties().Select(a => $"[{a.Name}]")) } FROM [{type.Name}]";
string sql = SqlBuilder<T>.FindAllSql;
using (SqlConnection connection = new SqlConnection(Customers))
{<!-- -->
SqlCommand sqlCommand = new SqlCommand(sql, connection);
connection.Open();
SqlDataReader reader = sqlCommand.ExecuteReader();
List<T> datalist = new List<T>();
while (reader.Read()) //开始读取
{<!-- -->
object oCompany = Activator.CreateInstance(type);
ReaderToList(type, oCompany, reader);
datalist.Add((T)oCompany);
}
return datalist;
}
}
/// <summary>
/// 改
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public bool Update<T>(T t) where T : Advanced.Model.BaseModel
{<!-- -->
Type type = typeof(T);
object oCompany = Activator.CreateInstance(type);
//string sql = $"update [{type.Name}] set {string.Join(",", type.GetProperties().Where(a => !a.Name.Equals("Id")).Select(a => $"[{a.Name}]=@ {a.Name}"))} where Id =@Id";
string sql = SqlBuilder<T>.UpdateSql;
var parameters = type.GetProperties().Select(item => new SqlParameter()
{<!-- -->
ParameterName = $"@{item.Name}",
SqlValue = $"{item.GetValue(t)}"
});
// Richard 在拼接sql语句的时候，尽管ID 是Int类型，还是建议大家使用Sql语句参数化
//（防止sql注入）
using (SqlConnection connection = new SqlConnection(Customers))
{<!-- -->
SqlCommand sqlCommand = new SqlCommand(sql, connection);
sqlCommand.Parameters.AddRange(parameters.ToArray());
connection.Open();
return sqlCommand.ExecuteNonQuery() > 0;
}
}
}
public class ConfigHelper
{<!-- -->
public static readonly string factoryConfig= ConfigurationManager.AppSettings["factoryConfig"];
}
}
/// <summary>
/// 针对于不同的类型，生成副本
/// </summary>
/// <typeparam name="T"></typeparam>
public class SqlBuilder<T> where T : BaseModel
{<!-- -->
public static readonly string FindSql = null;
public static readonly string DeleteSql = null;
public static readonly string FindAllSql = null;
public static readonly string UpdateSql = null;
static SqlBuilder()
{<!-- -->
Type type = typeof(T);
FindSql = $"SELECT {string.Join(",", type.GetProperties().Select(a => $"[{<!-- -->a.Name}]")) } FROM [{type.Name}] where Id=@Id";
DeleteSql = $"Delete from [{type.Name}] where Id=@Id"; ;
FindAllSql = $"SELECT {string.Join(",", type.GetProperties().Select(a => $"[{<!-- -->a.Name}]")) } FROM [{type.Name}]";
UpdateSql = $"update [{type.Name}] set {string.Join(",", type.GetProperties().Where(a => !a.Name.Equals("Id")).Select(a => $"[{<!-- -->a.Name}]=@ {<!-- -->a.Name}"))} where Id =@Id";
}
}
