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 }
View Code

  ②单表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  }
View Code

  ③单表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 }
View Code

  ④单表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 }
View Code

三、局限性

  ①实体类名字必须和表名一致

  ②主键名必须为Id

  ③不适合自增型主键

  ④出现问题难以调试(可以添加日志跟踪)

  ⑤安全性低(暴漏字段名)

  ⑥实现分页太暴力

  ⑦灵活性低(通用和灵活永远的矛盾体)

  ⑧Update操作时会全表更新

  ⑨Select操作会全字段查询

四、源码下载

  sourcecode

 

posted @ 2015-12-04 00:08  Dupont  阅读(641)  评论(0编辑  收藏  举报