SQLSERVER单表CRUD通用方法
一、适用场景
①当你书写简单的增删改查心累了
②当你的项目不考虑并发、高性能
③当你追求更快速的开发效率
④当你的业务只涉及单表
二、代码展示
①单表Insert
1 public bool Insert<T>(T t) 2 { 3 var o = t.GetType(); 4 var sBulider = new StringBuilder("insert into "); 5 sBulider.Append("[" + o.Name + "]"); 6 sBulider.Append("("); 7 var properties = o.GetProperties(); 8 foreach (var p in properties) 9 { 10 sBulider.Append(p.Name); 11 sBulider.Append(","); 12 } 13 sBulider.Length--; 14 sBulider.Append(") values("); 15 foreach (var p in properties) 16 { 17 sBulider.AppendFormat("'{0}'", p.GetValue(t, null)); 18 sBulider.Append(","); 19 } 20 sBulider.Length--; 21 sBulider.Append(")"); 22 return SqlDBHelper.ExecuteSql(sBulider.ToString()) > 0; 23 }
②单表Delete
1 public bool Delete<T>(string idList) where T : new() 2 { 3 var tableName = new T().GetType().Name; 4 var ids = new StringBuilder(); 5 foreach (var id in idList.Split(',')) 6 { 7 ids.Append("'"); 8 ids.Append(id); 9 ids.Append("',"); 10 } 11 ids.Length--; 12 var sql = string.Format("delete from [{0}] where Id in ({1})", tableName, ids.ToString()); 13 return SqlDBHelper.ExecuteSql(sql) > 0; 14 }
③单表Update
1 public bool Update<T>(T t) 2 { 3 var o = t.GetType(); 4 var sBulider = new StringBuilder("update "); 5 sBulider.Append("[" + o.Name + "]"); 6 sBulider.Append(" set "); 7 var id = ""; 8 var properties = o.GetProperties(); 9 foreach (var p in properties) 10 { 11 var columnName = p.Name; 12 var columnValue = p.GetValue(t, null); 13 if (columnName == "Id") 14 id = columnValue.ToString(); 15 sBulider.Append(columnName); 16 sBulider.AppendFormat("='{0}'", columnValue); 17 sBulider.Append(","); 18 } 19 sBulider.Length--; 20 sBulider.AppendFormat("where Id='{0}'", id); 21 return SqlDBHelper.ExecuteSql(sBulider.ToString()) > 0; 22 }
④单表Select
1 public List<T> SerarchList<T>(string where = "") where T : new() 2 { 3 var tableName = new T().GetType().Name; 4 var sqlString = new StringBuilder(); 5 sqlString.AppendFormat("select * from [{0}]", tableName); 6 if (!string.IsNullOrEmpty(where)) 7 { 8 sqlString.AppendFormat(" where '{0}'", where); 9 } 10 var list = new List<T>(); 11 using (var dataReader = SqlDBHelper.ExecuteReader(sqlString.ToString())) 12 { 13 while (dataReader.Read()) 14 { 15 var t = new T(); 16 var properties = t.GetType().GetProperties(); 17 foreach (var p in properties) 18 { 19 p.SetValue(t, dataReader[p.Name], null); 20 } 21 list.Add(t); 22 } 23 } 24 return list; 25 }
三、局限性
①实体类名字必须和表名一致
②主键名必须为Id
③不适合自增型主键
④出现问题难以调试(可以添加日志跟踪)
⑤安全性低(暴漏字段名)
⑥实现分页太暴力
⑦灵活性低(通用和灵活永远的矛盾体)
⑧Update操作时会全表更新
⑨Select操作会全字段查询
四、源码下载
Don't go too far and forget why starting!