EF架构~一个规范，两个实现（续）~性能可以接受的批量增删改操作
上回主要把数据操作规范及EF两种基类下的实现说了一下，今天主要针对ObjectContext情况下的批量操作作一个详细的说明，首先，要说明一点，批量操作不用ObjectContext提供的方法，而是使用拼SQL串的方式，将列表拼成一个SQL串，一次的发给数据库，这样在性能上绝对是一个质的飞越，本人尝试过，无论是EF还是linq to sql，它们提供的方法，对于列表的操作会产生N条SQL串（N＝列表的长度）。
一个操作类型枚举：
1 /// <summary> 2 /// 執行SQL的類型 3 /// </summary> 4 public enum SQLType 5 { 6 Insert, 7 Update, 8 Delete, 9 }
调用方法很容易：
public void Insert<TEntity>(List<TEntity> list) where TEntity : class { DoListSQL(list, SQLType.Insert); } public void Update<TEntity>(List<TEntity> list) where TEntity : class { DoListSQL<TEntity>(list, SQLType.Update); } public void Delete<TEntity>(List<TEntity> list) where TEntity : class { DoListSQL<TEntity>(list, SQLType.Delete); }
下面是实现这样方法的代码，即SQL串在ObjectContext环境下是如何拼接的：
1 /// <summary> 2 /// 构建Update语句串 3 /// </summary> 4 /// <typeparam name="TEntity"></typeparam> 5 /// <param name="entity"></param> 6 /// <returns></returns> 7 private Tuple<string, object[]> CreateUpdateSQL<TEntity>(TEntity entity) where TEntity : class 8 { 9 if (entity == null) 10 throw new ArgumentException("The database entity can not be null."); 11 12 Type entityType = entity.GetType(); 13 var table = entityType.GetProperties().Where(i => i.PropertyType != typeof(EntityKey) && i.PropertyType != typeof(EntityState)).ToArray(); 14 var primaryKeyColumns = (entity as EntityObject).GetPK(); 15 if (primaryKeyColumns == null || primaryKeyColumns.Count == 0) 16 throw new ArgumentException("The Table entity have not a primary key."); 17 List<object> arguments = new List<object>(); 18 StringBuilder builder = new StringBuilder(); 19 20 foreach (var change in table) 21 { 22 if (primaryKeyColumns.Contains(change)) 23 continue; 24 25 if (arguments.Count != 0) 26 builder.Append(", "); 27 28 if (change.GetValue(entity, null) != null) 29 { 30 builder.Append(change.Name + " = {" + arguments.Count + "}"); 31 32 if (change.PropertyType == typeof(string) || change.PropertyType == typeof(DateTime)) 33 arguments.Add("'" + change.GetValue(entity, null).ToString().Replace("'", "char(39)") + "'"); 34 else 35 arguments.Add(change.GetValue(entity, null)); 36 } 37 else 38 { 39 builder.Append(change.Name + " = NULL, "); 40 } 41 } 42 43 if (builder.Length == 0) 44 throw new Exception("没有任何属性进行更新"); 45 46 builder.Insert(0, " UPDATE " + string.Format("[{0}]", entityType.Name) + " SET "); 47 48 builder.Append(" WHERE "); 49 bool firstPrimaryKey = true; 50 51 foreach (var primaryField in primaryKeyColumns) 52 { 53 if (firstPrimaryKey) 54 firstPrimaryKey = false; 55 else 56 builder.Append(" AND "); 57 58 object val = entityType.GetProperty(primaryField.Name).GetValue(entity, null); 59 builder.Append(GetEqualStatment(primaryField.Name, arguments.Count)); 60 arguments.Add(val); 61 } 62 return new Tuple<string, object[]>(builder.ToString(), arguments.ToArray()); 63 64 } 65 66 /// <summary> 67 /// 构建Insert语句串 68 /// 主键为自增时，如果主键值为0，我们将主键插入到SQL串中 69 /// </summary> 70 /// <typeparam name="TEntity"></typeparam> 71 /// <param name="entity"></param> 72 /// <returns></returns> 73 private Tuple<string, object[]> CreateInsertSQL<TEntity>(TEntity entity) where TEntity : class 74 { 75 if (entity == null) 76 throw new ArgumentException("The database entity can not be null."); 77 78 Type entityType = entity.GetType(); 79 var table = entityType.GetProperties().Where(i => i.PropertyType != typeof(EntityKey) && i.PropertyType != typeof(EntityState)).ToArray(); 80 var primaryKeyColumns = (entity as EntityObject).GetPK(); 81 82 List<object> arguments = new List<object>(); 83 StringBuilder fieldbuilder = new StringBuilder(); 84 StringBuilder valuebuilder = new StringBuilder(); 85 86 fieldbuilder.Append(" INSERT INTO " + string.Format("[{0}]", entityType.Name) + " ("); 87 88 foreach (var member in table) 89 { 90 if (primaryKeyColumns.Contains(member) && Convert.ToString(member.GetValue(entity, null)) == "0") 91 continue; 92 object value = member.GetValue(entity, null); 93 if (value != null) 94 { 95 if (arguments.Count != 0) 96 { 97 fieldbuilder.Append(", "); 98 valuebuilder.Append(", "); 99 } 100 101 fieldbuilder.Append(member.Name); 102 if (member.PropertyType == typeof(string) || member.PropertyType == typeof(DateTime)) 103 valuebuilder.Append("'{" + arguments.Count + "}'"); 104 else 105 valuebuilder.Append("{" + arguments.Count + "}"); 106 if (value.GetType() == typeof(string)) 107 value = value.ToString().Replace("'", "char(39)"); 108 arguments.Add(value); 109 110 } 111 } 112 113 114 fieldbuilder.Append(") Values ("); 115 116 fieldbuilder.Append(valuebuilder.ToString()); 117 fieldbuilder.Append(");"); 118 return new Tuple<string, object[]>(fieldbuilder.ToString(), arguments.ToArray()); 119 } 120 /// <summary> 121 /// 构建Delete语句串 122 /// </summary> 123 /// <typeparam name="TEntity"></typeparam> 124 /// <param name="entity"></param> 125 /// <returns></returns> 126 private Tuple<string, object[]> CreateDeleteSQL<TEntity>(TEntity entity) where TEntity : class 127 { 128 if (entity == null) 129 throw new ArgumentException("The database entity can not be null."); 130 131 Type entityType = entity.GetType(); 132 var table = entityType.GetProperties().Where(i => i.PropertyType != typeof(EntityKey) && i.PropertyType != typeof(EntityState)).ToArray(); 133 var primaryKeyColumns = (entity as EntityObject).GetPK(); 134 if (primaryKeyColumns == null || primaryKeyColumns.Count == 0) 135 throw new ArgumentException("The Table entity have not a primary key."); 136 137 List<object> arguments = new List<object>(); 138 StringBuilder builder = new StringBuilder(); 139 builder.Append(" Delete from " + string.Format("[{0}]", entityType.Name)); 140 141 builder.Append(" WHERE "); 142 bool firstPrimaryKey = true; 143 144 foreach (var primaryField in primaryKeyColumns) 145 { 146 if (firstPrimaryKey) 147 firstPrimaryKey = false; 148 else 149 builder.Append(" AND "); 150 151 object val = entityType.GetProperty(primaryField.Name).GetValue(entity, null); 152 builder.Append(GetEqualStatment(primaryField.Name, arguments.Count)); 153 arguments.Add(val); 154 } 155 return new Tuple<string, object[]>(builder.ToString(), arguments.ToArray()); 156 } 157 158 159 /// <summary> 160 /// 执行实体列表以SQL串的方式 161 /// </summary> 162 /// <typeparam name="TEntity"></typeparam> 163 /// <param name="list"></param> 164 private void DoListSQL<TEntity>(IEnumerable<TEntity> list, SQLType sqlType) where TEntity : class 165 { 166 StringBuilder sqlstr = new StringBuilder(); 167 168 switch (sqlType) 169 { 170 case SQLType.Insert: 171 list.ToList().ForEach(i => 172 { 173 Tuple<string, object[]> sql = CreateInsertSQL(i); 174 sqlstr.AppendFormat(sql.Item1, sql.Item2); 175 }); 176 break; 177 case SQLType.Update: 178 list.ToList().ForEach(i => 179 { 180 Tuple<string, object[]> sql = CreateUpdateSQL(i); 181 sqlstr.AppendFormat(sql.Item1, sql.Item2); 182 }); 183 break; 184 case SQLType.Delete: 185 list.ToList().ForEach(i => 186 { 187 Tuple<string, object[]> sql = CreateDeleteSQL(i); 188 sqlstr.AppendFormat(sql.Item1, sql.Item2); 189 }); 190 break; 191 default: 192 throw new ArgumentException("请输入正确的参数"); 193 } 194 195 _db.ExecuteStoreCommand(sqlstr.ToString()); 196 }
代码在vs2010+sql2005+mvc3环境下测试通过
本文转自博客园张占岭（仓储大叔）的博客，原文链接：EF架构~一个规范，两个实现（续）~性能可以接受的批量增删改操作，如需转载请自行联系原博主。
