EF架构~一个规范，两个实现（续）~性能可以接受的批量增删改操作
上回主要把数据操作规范及EF两种基类下的实现说了一下，今天主要针对ObjectContext情况下的批量操作作一个详细的说明，首先，要说明一点，批量操作不用ObjectContext提供的方法，而是使用拼SQL串的方式，将列表拼成一个SQL串，一次的发给数据库，这样在性能上绝对是一个质的飞越，本人尝试过，无论是EF还是linq to sql，它们提供的方法，对于列表的操作会产生N条SQL串（N＝列表的长度）。
一个操作类型枚举：
1 /// <summary>2 /// 執行SQL的類型3 /// </summary>4 public enum SQLType5 {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 else105 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 : class127 {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 else149 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 : class165 {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环境下测试通过
TAG:
