Sql增删改查和优化
文件源码https://files.cnblogs.com/files/pilgrim/StudentManage.rar
1、通过正常编写sql语句和顺序写代码
正常编写sql语句是常用的方式,也是初学者最易掌握的(比如我)。直接使用sql进行拼装,但当设计多个实体对象时,就需要写多个对应的sql语句,需要使用代码码。这个方法里,我是用了反射和属性字段的方式,来获取和设置相关的对象值。
/// <summary> /// 正常编写sql语句和顺序写代码 /// </summary> public class SqlHelper //: IDBHelper { /// <summary> /// Sql连接 /// </summary> protected SqlConnection connection = new SqlConnection(); public SqlHelper() { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); builder.DataSource = @"(LocalDB)\MSSQLLocalDB"; builder.AttachDBFilename = @"C:\Users\Nigel\Desktop\StudentManage\StudentManage\Database1.mdf"; builder.IntegratedSecurity = true; connection.ConnectionString = builder.ConnectionString; } /// <summary> /// 查询一个对象 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="id"></param> /// <returns></returns> public virtual T SelectOne<T>(int id) where T : class, IModel, new() { T t = new T(); Type type = typeof(T); //没有应用特性的情况:此时,表名与类名相同、属性名和数据库字段名相同 string sql = $"select * from [{type.Name}] where id = {id}"; try { connection.Open(); IDbCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.Text; IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (reader.Read()) { PropertyInfo[] infos = type.GetProperties(); foreach (var info in infos) { if (info.CanWrite) { //设置对应的值。注意:此处GUID和枚举的赋值有问题 info.SetValue(t, reader[info.Name] is DBNull ? null : reader[info.Name]); } } } } finally { connection.Close(); } return t; } }
2、使用参数化---防注入
正常编写sql语句会导致一个问题,那就是sql注入。意味着,其他编程人员可以未通过你的代码,而是在sql语句后面加上他的sql语句,使得数据库发生了注入操作。因此,参数化数据传入,是防止sql注入的有效方式。
public override T SelectOne<T>(int id) { T t = new T(); Type type = typeof(T);//获取类型 string sql = $"select * from [{type.Name}] where [id]=@id"; try { connection.Open(); IDbCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; cmd.Parameters.Add(new SqlParameter("@id", id)); cmd.CommandType = CommandType.Text; IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); if(reader.Read()) { PropertyInfo[] properties = type.GetProperties(); foreach (var prop in properties) { if(prop.CanWrite) { prop.SetValue(t, reader[prop.Name] is DBNull ? null : reader[prop.Name]); } } } } finally { connection.Close(); } return t; }
3、使用特性标记、反射数据库对象和字段
前面所有的反射操作,均是基于类名和数据库表名一致、类的属性名和数据库表中的字段一直的情况。那如果不一样呢?又应该如何操作。以前学到了特性,所以这个地方也需要用到特性进行标记。
public override T SelectOne<T>(int id) { T t = new T(); Type type = typeof(T); //应用特性后: 表名与类名,或者属性名与数据库字段名可能不相同,并查询指定的列 Type attrType = typeof(DbNameAttribute); IEnumerable<PropertyInfo> lstPrimaryKey = type.GetProperties().Where(p => p.IsDefined(attrType, true)).Where(p => (p.GetCustomAttribute(attrType) as DbNameAttribute).IsPrimaryKey); string primaryKeyName = GetPropertyDBName(lstPrimaryKey.First()); //使用特性 但不使用参数化 { string sql = string.Format("select {0} from {1} where [{2}]={3}", string.Join(",", GetColumnNames(type)), GetTableName(type), primaryKeyName, id); try { connection.Open(); IDbCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.Text; IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (reader.Read()) { PropertyInfo[] infos = type.GetProperties(); foreach (var prop in infos) { if (prop.CanWrite) { string dbName = GetPropertyDBName(prop); //设置对应的值。注意:此处GUID和枚举的赋值有问题 prop.SetValue(t, reader[dbName] is DBNull ? null : reader[dbName]); } } } } finally { connection.Close(); } } //使用特性和参数化 { string sql = string.Format("select {0} from {1} where [{2}]={3}", string.Join(",", GetColumnNames(type)), GetTableName(type), primaryKeyName, $"@{primaryKeyName}"); try { connection.Open(); IDbCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; cmd.Parameters.Add(new SqlParameter($"@{primaryKeyName}", id)); cmd.CommandType = CommandType.Text; IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); if(reader.Read()) { PropertyInfo[] infos = type.GetProperties(); foreach (var prop in infos) { if(prop.CanWrite) { string dbName = GetPropertyDBName(prop); //设置对应的值。注意:此处GUID和枚举的赋值有问题 prop.SetValue(t, reader[dbName] is DBNull ? null : reader[dbName]); } } } } finally { connection.Close(); } } return t; }
反射特性的代码:
/// <summary> /// 获取表名称 /// </summary> /// <param name="type">类型</param> /// <returns>如果有标记就返回标记,如果没有标记则返回类名</returns> protected string GetTableName(Type type) { Attribute attribute = type.GetCustomAttribute(typeof(DbNameAttribute), true); if (attribute != null && attribute is DbNameAttribute) { string name = (attribute as DbNameAttribute).Name; if (string.IsNullOrEmpty(name) == false) { return name; } } return type.Name; } /// <summary> /// 获实体对应数据库中的列名称 /// </summary> /// <param name="type">获取的类型</param> /// <returns>所有被标记的名称和没有被标记的字段名</returns> protected string[] GetColumnNames(Type type) { PropertyInfo[] propertyInfos = type.GetProperties(); List<string> names = new List<string>(); foreach (PropertyInfo property in propertyInfos) { if (property.CanWrite)//只读取能写入数据的 { names.Add(GetPropertyDBName(property)); } } return names.ToArray(); } /// <summary> /// 获取属性的数据库名称 /// </summary> /// <param name="property">属性</param> /// <returns>数据库名称</returns> protected string GetPropertyDBName(PropertyInfo property) { string name = string.Empty; Attribute attribute = property.GetCustomAttribute(typeof(DbNameAttribute), true); if (attribute != null && attribute is DbNameAttribute) { name = (attribute as DbNameAttribute).Name; } if (string.IsNullOrEmpty(name)) { return property.Name; } else { return name; } }
4、关于其他方法的编写
①直接使用sql语句进行操作

/// <summary> /// 正常编写sql语句和顺序写代码 /// </summary> public class SqlHelper //: IDBHelper { /// <summary> /// Sql连接 /// </summary> protected SqlConnection connection = new SqlConnection(); public SqlHelper() { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); builder.DataSource = @"(LocalDB)\MSSQLLocalDB"; builder.AttachDBFilename = @"C:\Users\Nigel\Desktop\StudentManage\StudentManage\Database1.mdf"; builder.IntegratedSecurity = true; connection.ConnectionString = builder.ConnectionString; } /// <summary> /// 查询一个对象 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="id"></param> /// <returns></returns> public virtual T SelectOne<T>(int id) where T : class, IModel, new() { T t = new T(); Type type = typeof(T); //没有应用特性的情况:此时,表名与类名相同、属性名和数据库字段名相同 string sql = $"select * from [{type.Name}] where id = {id}"; try { connection.Open(); IDbCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.Text; IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (reader.Read()) { PropertyInfo[] infos = type.GetProperties(); foreach (var info in infos) { if (info.CanWrite) { //设置对应的值。注意:此处GUID和枚举的赋值有问题 info.SetValue(t, reader[info.Name] is DBNull ? null : reader[info.Name]); } } } } finally { connection.Close(); } return t; } /// <summary> /// 查询所有对象 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public virtual List<T> SelectAll<T>() where T : class, IModel, new() { List<T> lstResult = new List<T>(); Type type = typeof(T); //没有应用特性的情况:此时,表名与类名相同、属性名和数据库字段名相同 string sql = $"select * from [{type.Name}]"; try { connection.Open(); IDbCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.Text; IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); PropertyInfo[] infos = type.GetProperties(); while (reader.Read()) { T t = new T(); foreach (var info in infos) { if (info.CanWrite) { //设置对应的值。注意:此处GUID和枚举的赋值有问题 info.SetValue(t, reader[info.Name] is DBNull ? null : reader[info.Name]); } } lstResult.Add(t); } } finally { connection.Close(); } return lstResult; } /// <summary> /// 插入一个对象 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <returns></returns> public virtual int Insert<T>(T t) { Type type = typeof(T); //获取写入的键值 Dictionary<string, object> keyValues = new Dictionary<string, object>(); PropertyInfo[] properties = type.GetProperties(); for (int i = 0; i < properties.Length; i++) { if (properties[i].CanWrite) { keyValues.Add(properties[i].Name, properties[i].GetValue(t)); } } string sql = $"insert [{type.Name}]({string.Join(",", keyValues.Keys)}) values('{string.Join("','", keyValues.Values)}')"; int result = 0; //SqlTransaction transaction; try { connection.Open(); //transaction = connection.BeginTransaction();//事务处理 IDbCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.Text; result= cmd.ExecuteNonQuery(); //transaction.Commit(); } catch(Exception ex) { //transaction.Rollback(); throw ex; } finally { connection.Close(); } return result; } /// <summary> /// 删除数据 /// </summary> /// <typeparam name="T">需要删除的对象</typeparam> /// <param name="t"></param> /// <returns></returns> public virtual int Delete<T>(T t) { return 0; } /// <summary> /// 删除数据 /// </summary> /// <param name="id">需要删除的id</param> /// <returns>受影响行数</returns> public virtual int Delete<T>(int id) { return 0; } /// <summary> /// 删除数据 /// </summary> /// <param name="id">需要删除的id</param> /// <param name="t">更新数据</param> /// <returns>受影响行数</returns> public virtual int Update<T>(T t) { return 0; } }
②使用参数化---防注入

/// <summary> /// 使用参数化的方式进行--防止注入 /// </summary> public class SqlHelperExtand1: SqlHelper { public override T SelectOne<T>(int id) { T t = new T(); Type type = typeof(T);//获取类型 string sql = $"select * from [{type.Name}] where [id]=@id"; try { connection.Open(); IDbCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; cmd.Parameters.Add(new SqlParameter("@id", id)); cmd.CommandType = CommandType.Text; IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); if(reader.Read()) { PropertyInfo[] properties = type.GetProperties(); foreach (var prop in properties) { if(prop.CanWrite) { prop.SetValue(t, reader[prop.Name] is DBNull ? null : reader[prop.Name]); } } } } finally { connection.Close(); } return t; } public override List<T> SelectAll<T>() { return base.SelectAll<T>(); } public override int Insert<T>(T t) { Type type = t.GetType(); //获取写入的键值 Dictionary<string, SqlParameter> keyValues = new Dictionary<string, SqlParameter>(); PropertyInfo[] properties = type.GetProperties(); for (int i = 0; i < properties.Length; i++) { if (properties[i].CanWrite) { keyValues.Add(properties[i].Name, new SqlParameter($"@{properties[i].Name}", properties[i].GetValue(t))); } } string sql = $"insert [{type.Name}]({string.Join(",", keyValues.Keys)}) values(@{string.Join(",@", keyValues.Keys)})"; int result = 0; SqlTransaction transaction = null; try { connection.Open(); //transaction = connection.BeginTransaction();//开启事务 SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.Text; cmd.Parameters.AddRange(keyValues.Values.ToArray()); result = cmd.ExecuteNonQuery(); //transaction.Commit(); } catch(Exception ex) { //transaction.Rollback(); throw ex; } finally { connection.Close(); } return result ; } }
③使用特性标记、反射数据库对象和字段

/// <summary> /// 当类名与表名,或者属性名与列名不相同时,使用特性进行标记 /// </summary> public class SqlHelperExtand2 : /*SqlHelper*/ SqlHelperExtand1 { public override T SelectOne<T>(int id) { T t = new T(); Type type = typeof(T); //应用特性后: 表名与类名,或者属性名与数据库字段名可能不相同,并查询指定的列 Type attrType = typeof(DbNameAttribute); IEnumerable<PropertyInfo> lstPrimaryKey = type.GetProperties().Where(p => p.IsDefined(attrType, true)).Where(p => (p.GetCustomAttribute(attrType) as DbNameAttribute).IsPrimaryKey); string primaryKeyName = GetPropertyDBName(lstPrimaryKey.First()); //使用特性 但不使用参数化 { string sql = string.Format("select {0} from {1} where [{2}]={3}", string.Join(",", GetColumnNames(type)), GetTableName(type), primaryKeyName, id); try { connection.Open(); IDbCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.Text; IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (reader.Read()) { PropertyInfo[] infos = type.GetProperties(); foreach (var prop in infos) { if (prop.CanWrite) { string dbName = GetPropertyDBName(prop); //设置对应的值。注意:此处GUID和枚举的赋值有问题 prop.SetValue(t, reader[dbName] is DBNull ? null : reader[dbName]); } } } } finally { connection.Close(); } } //使用特性和参数化 { string sql = string.Format("select {0} from {1} where [{2}]={3}", string.Join(",", GetColumnNames(type)), GetTableName(type), primaryKeyName, $"@{primaryKeyName}"); try { connection.Open(); IDbCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; cmd.Parameters.Add(new SqlParameter($"@{primaryKeyName}", id)); cmd.CommandType = CommandType.Text; IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); if(reader.Read()) { PropertyInfo[] infos = type.GetProperties(); foreach (var prop in infos) { if(prop.CanWrite) { string dbName = GetPropertyDBName(prop); //设置对应的值。注意:此处GUID和枚举的赋值有问题 prop.SetValue(t, reader[dbName] is DBNull ? null : reader[dbName]); } } } } finally { connection.Close(); } } return t; } public override List<T> SelectAll<T>() { List<T> lstResult = new List<T>(); Type type = typeof(T); //应用特性后: 表名与类名,或者属性名与数据库字段名可能不相同,并查询指定的列 string sql = string.Format("select {0} from {1}", string.Join(",", GetColumnNames(type)), GetTableName(type)); try { connection.Open(); IDbCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.Text; IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); PropertyInfo[] infos = type.GetProperties(); while (reader.Read()) { T t = new T(); foreach (var info in infos) { if (info.CanWrite) { string name = GetPropertyDBName(info); //设置对应的值。注意:此处GUID和枚举的赋值有问题 info.SetValue(t, reader[name] is DBNull ? null : reader[name]); } } lstResult.Add(t); } } finally { connection.Close(); } return lstResult; } #region 参数化查询(应用特性) --- 防注入 /// <summary> /// 查找某一个特定的对象 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public virtual T SelectObject<T>(T t) { var KeyInfo = KeyWhere( t); Type type = t.GetType(); string sql = $@"select * from [{GetTableName(type)}] {KeyInfo.Item1}"; using (SqlCommand cmd = connection.CreateCommand()) { //connection.BeginTransaction();//事务处理 cmd.CommandText = sql; cmd.Parameters.AddRange(KeyInfo.Item2.ToArray()); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (reader.Read()) { foreach (PropertyInfo prop in type.GetProperties()) { if (prop.CanWrite) { string dbName = GetPropertyDBName(prop); prop.SetValue(t, reader[dbName] is DBNull ? null : reader[dbName]);//这儿还可以映射,特性 } } } connection.Close(); } return t; } /// <summary> /// 返回主键条件查询字符串 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="type"></param> /// <param name="t"></param> /// <returns></returns> private Tuple<string, IEnumerable<SqlParameter>> KeyWhere<T>( T t) { List<string> lstWhere = new List<string>(); List<SqlParameter> lstParameter = new List<SqlParameter>(); Type attrType = typeof(DbNameAttribute); IEnumerable<PropertyInfo> keysInfo =t.GetType().GetProperties().Where(prop => prop.IsDefined(attrType, true)).Where(p => { return (p.GetCustomAttribute(attrType) as DbNameAttribute).IsPrimaryKey; }); foreach (PropertyInfo property in keysInfo) { DbNameAttribute key = property.GetCustomAttribute(attrType) as DbNameAttribute; if (string.IsNullOrEmpty(key.Name)) { lstWhere.Add($@"[{property.Name}] = @{property.Name}"); lstParameter.Add(new SqlParameter($@"@{property.Name}", property.GetValue(t))); } else { lstWhere.Add($@"[{key.Name}] = @{key.Name}");//这儿可以采用传参的形式 lstParameter.Add(new SqlParameter($@"@{key.Name}", property.GetValue(t))); } //if (key.IsDefinedKey) //{ // lstWhere.Add($@"[{key.Name}] = @{key.Name}");//这儿可以采用传参的形式 // lstParameter.Add(new SqlParameter($@"@{key.Name}", property.GetValue(t))); //} //else //{ // lstWhere.Add($@"[{property.Name}] = @{property.Name}"); // lstParameter.Add(new SqlParameter($@"@{property.Name}", property.GetValue(t))); //} } //if(lstWhere.Count>0) //{ // string strWhere= $@"where {string.Join(" and ", lstWhere)}"; // return new Tuple<string, IEnumerable<SqlParameter>>(strWhere, lstParameter); //} string strWhere = $@"where {string.Join(" and ", lstWhere)}"; return new Tuple<string, IEnumerable<SqlParameter>>(strWhere, lstParameter); } #endregion public override int Insert<T>(T t) { Type type = t.GetType(); Dictionary<string, SqlParameter> keyValues = new Dictionary<string, SqlParameter>(); PropertyInfo[] properties = type.GetProperties(); foreach (PropertyInfo prop in properties) { if(prop.CanWrite) { string dbName = GetPropertyDBName(prop); SqlParameter parameter = new SqlParameter($"@{dbName}", prop.GetValue(t)); keyValues.Add(dbName, parameter); } } string sql = $"insert [{GetTableName(type)}]({string.Join(",", keyValues.Keys)}) values(@{string.Join(",@", keyValues.Keys)})"; //SqlTransaction transaction = null; int result = 0; try { connection.Open(); //transaction = connection.BeginTransaction(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; cmd.Parameters.AddRange(keyValues.Values.ToArray()); // transaction.Commit(); result = cmd.ExecuteNonQuery(); } catch(Exception ex) { // transaction.Rollback(); throw ex; } finally { connection.Close(); } return result; } #region 获取特性的标记 /// <summary> /// 获取表名称 /// </summary> /// <param name="type">类型</param> /// <returns>如果有标记就返回标记,如果没有标记则返回类名</returns> protected string GetTableName(Type type) { Attribute attribute = type.GetCustomAttribute(typeof(DbNameAttribute), true); if (attribute != null && attribute is DbNameAttribute) { string name = (attribute as DbNameAttribute).Name; if (string.IsNullOrEmpty(name) == false) { return name; } } return type.Name; } /// <summary> /// 获实体对应数据库中的列名称 /// </summary> /// <param name="type">获取的类型</param> /// <returns>所有被标记的名称和没有被标记的字段名</returns> protected string[] GetColumnNames(Type type) { PropertyInfo[] propertyInfos = type.GetProperties(); List<string> names = new List<string>(); foreach (PropertyInfo property in propertyInfos) { if (property.CanWrite)//只读取能写入数据的 { names.Add(GetPropertyDBName(property)); } } return names.ToArray(); } /// <summary> /// 获取属性的数据库名称 /// </summary> /// <param name="property">属性</param> /// <returns>数据库名称</returns> protected string GetPropertyDBName(PropertyInfo property) { string name = string.Empty; Attribute attribute = property.GetCustomAttribute(typeof(DbNameAttribute), true); if (attribute != null && attribute is DbNameAttribute) { name = (attribute as DbNameAttribute).Name; } if (string.IsNullOrEmpty(name)) { return property.Name; } else { return name; } } #endregion }
5、代码优化
阅读前面的代码,发现有一部分能够提取出来,因此进行相关优化。部分代码,在第3点拷贝
/// <summary> /// 优化程序代码,提取公用部分 /// </summary> public class SqlHelperExtand3 : SqlHelperExtand2 { public override T SelectOne<T>(int id) { T t = new T(); Type type = typeof(T); //应用特性后: 表名与类名,或者属性名与数据库字段名可能不相同,并查询指定的列 Type attrType = typeof(DbNameAttribute); IEnumerable<PropertyInfo> lstPrimaryKey = type.GetProperties().Where(p => p.IsDefined(attrType, true)).Where(p => (p.GetCustomAttribute(attrType) as DbNameAttribute).IsPrimaryKey); string primaryKeyName = GetPropertyDBName(lstPrimaryKey.First()); //使用特性和参数化 string sql = string.Format("select {0} from {1} where [{2}]={3}", string.Join(",", GetColumnNames(type)), GetTableName(type), primaryKeyName, $"@{primaryKeyName}"); List<SqlParameter> parameters = new List<SqlParameter>() { new SqlParameter($"@{primaryKeyName}", id), }; Action act = () => { Func<IDbCommand, T> func = cmd => { IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (reader.Read()) { PropertyInfo[] infos = type.GetProperties(); foreach (var prop in infos) { if (prop.CanWrite) { string dbName = GetPropertyDBName(prop); //设置对应的值。注意:此处GUID和枚举的赋值有问题 prop.SetValue(t, reader[dbName] is DBNull ? null : reader[dbName]); } } } return t; }; t = ExecuteCmd(sql, parameters, func); }; act.Invoke(); return t; } public override List<T> SelectAll<T>() { List<T> lstResult = new List<T>(); Type type = typeof(T); //应用特性后: 表名与类名,或者属性名与数据库字段名可能不相同,并查询指定的列 string sql = string.Format("select {0} from {1}", string.Join(",", GetColumnNames(type)), GetTableName(type)); Action act = () => { Func<IDbCommand, List<T>> func = cmd => { List<T> lst = new List<T>(); IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); PropertyInfo[] infos = type.GetProperties(); while (reader.Read()) { T t = new T(); foreach (PropertyInfo prop in infos) { if (prop.CanWrite) { string name = GetPropertyDBName(prop); prop.SetValue(t, reader[name] is DBNull ? null : reader[name]); } } lst.Add(t); } return lst; }; lstResult = ExecuteCmd(sql, null, func); }; act.Invoke(); return lstResult; } #region /// <summary> /// 查找某一个特定的对象 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public override T SelectObject<T>(T t) { Type type = t.GetType(); var KeyInfo = KeyWhere(t); string sql = $@"select * from [{GetTableName(type)}] {KeyInfo.Item1}"; Action action = () => { Func<IDbCommand, T> func = cmd => { IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (reader.Read()) { foreach (PropertyInfo prop in type.GetProperties()) { if (prop.CanWrite) { string dbName = GetPropertyDBName(prop); prop.SetValue(t, reader[dbName] is DBNull ? null : reader[dbName]);//这儿还可以映射,特性 } } } return t; }; t = ExecuteCmd<T>(sql, KeyInfo.Item2, func); }; action.Invoke(); return t; } /// <summary> /// 返回主键条件查询字符串 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="type"></param> /// <param name="t"></param> /// <returns></returns> private Tuple<string, IEnumerable<SqlParameter>> KeyWhere<T>(T t) { List<string> lstWhere = new List<string>(); List<SqlParameter> lstParameter = new List<SqlParameter>(); Type attrType = typeof(DbNameAttribute); IEnumerable<PropertyInfo> keysInfo = t.GetType().GetProperties(). Where(prop => prop.IsDefined(attrType, true)).Where(p => { return (p.GetCustomAttribute(attrType) as DbNameAttribute).IsPrimaryKey; }); foreach (PropertyInfo property in keysInfo) { DbNameAttribute key = property.GetCustomAttribute(attrType) as DbNameAttribute; if (string.IsNullOrEmpty(key.Name)) { lstWhere.Add($@"[{property.Name}] = @{property.Name}"); lstParameter.Add(new SqlParameter($@"@{property.Name}", property.GetValue(t))); } else { lstWhere.Add($@"[{key.Name}] = @{key.Name}");//这儿可以采用传参的形式 lstParameter.Add(new SqlParameter($@"@{key.Name}", property.GetValue(t))); } //if (key.IsDefinedKey) //{ // lstWhere.Add($@"[{key.Name}] = @{key.Name}");//这儿可以采用传参的形式 // lstParameter.Add(new SqlParameter($@"@{key.Name}", property.GetValue(t))); //} //else //{ // lstWhere.Add($@"[{property.Name}] = @{property.Name}"); // lstParameter.Add(new SqlParameter($@"@{property.Name}", property.GetValue(t))); //} } //if(lstWhere.Count>0) //{ // string strWhere= $@"where {string.Join(" and ", lstWhere)}"; // return new Tuple<string, IEnumerable<SqlParameter>>(strWhere, lstParameter); //} string strWhere = $@"where {string.Join(" and ", lstWhere)}"; return new Tuple<string, IEnumerable<SqlParameter>>(strWhere, lstParameter); } #endregion public override int Insert<T>(T t) { Type type = t.GetType(); Dictionary<string, SqlParameter> keyValues = new Dictionary<string, SqlParameter>(); PropertyInfo[] properties = type.GetProperties(); foreach (PropertyInfo prop in properties) { if (prop.CanWrite) { string dbName = GetPropertyDBName(prop); SqlParameter parameter = new SqlParameter($"@{dbName}", prop.GetValue(t)); keyValues.Add(dbName, parameter); } } string sql = $"insert [{GetTableName(type)}]({string.Join(",", keyValues.Keys)}) values(@{string.Join(",@", keyValues.Keys)})"; //SqlTransaction transaction = null; int result = 0; Action act = () => { result = ExecuteCmd(sql, keyValues.Values.ToArray(), cmd=> cmd.ExecuteNonQuery()); }; act.Invoke(); return result; } public override int Delete<T>(int id) { string sql = "delect…… where [ID]=" + id;//通过反射等方法构造删除语句 int result = ExecuteCmd(sql, null, cmd => cmd.ExecuteNonQuery()); return result; } public override int Update<T>(T t) { string sql = "update…… ";//通过反射等方法构造更新语句 int result = ExecuteCmd(sql,null, cmd => cmd.ExecuteNonQuery()); return result; } /// <summary> /// 执行语句 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="lstParameter"></param> /// <param name="func"></param> /// <returns></returns> private T ExecuteCmd<T>(string sql, IEnumerable<SqlParameter> lstParameter, Func<IDbCommand, T> func) { //SqlTransaction transaction = null; try { using (SqlCommand cmd = connection.CreateCommand()) { connection.Open(); //transaction=connection.BeginTransaction();//事务处理 cmd.CommandText = sql; if (lstParameter != null) { cmd.Parameters.AddRange(lstParameter.ToArray()); } T t = func.Invoke(cmd); return t; } } catch (Exception ex) { Console.WriteLine(ex); throw ex; } finally { connection.Close(); } } }
凡所有相,皆是虚妄。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器