對於linq to sql提供的CURD操作，給我們的感覺就是簡單，容易使用，更加物件導向，不用拼SQL語句了，這些好處都表示在處理單條實體或者集合長度小的情況下，如果有一個1000條的集合實體，希望進行update操作，如果你還用linq to sql提代的update，那你伺服器就快要掛了，呵呵。
為什麼呢？
對於LINQ提借的命令，如update(list)，它會把list進行foreache的遍歷，然後一條一條指令的向SQLSERVER傳送，好傢伙，這要是1000，1W條實體的集合，進行update操作，這個對IO的開銷和伺服器的效能來說都是沒法接受的，呵呵，應該是一個SQL連結，一個指令，就能解決問題呀！
自己封套效能更好的CURD集合操作（選自我的entity framework架構，linq to sql沒來的及實現）
/// <summary> /// SQL操作型別 /// </summary> protected enum SQLType { Insert, Update, Delete, } /// <summary> /// 構建Update語句串 /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="entity"></param> /// <returns></returns> private Tuple<string, object[]> CreateUpdateSQL<TEntity>(TEntity entity) where TEntity : class { if (entity == null) throw new ArgumentException("The database entity can not be null."); List<string> pkList = GetPrimaryKey<TEntity>().Select(i => i.Name).ToList(); Type entityType = entity.GetType(); var table = entityType.GetProperties().Where(i => !pkList.Contains(i.Name) && i.GetValue(entity, null) != null && i.PropertyType != typeof(EntityState) && !(i.GetCustomAttributes(false).Length > 0 && i.GetCustomAttributes(false).Where(j => j.GetType() == typeof(NavigationAttribute)) != null) && (i.PropertyType.IsValueType || i.PropertyType == typeof(string)) //過濾導航屬性 ).ToArray(); //過濾主鍵，航行屬性，狀態屬性等 if (pkList == null || pkList.Count == 0) throw new ArgumentException("The Table entity have not a primary key."); List<object> arguments = new List<object>(); StringBuilder builder = new StringBuilder(); foreach (var change in table) { if (pkList.Contains(change.Name)) continue; if (arguments.Count != 0) builder.Append(", "); builder.Append(change.Name + " = {" + arguments.Count + "}"); if (change.PropertyType == typeof(string) || change.PropertyType == typeof(DateTime)) arguments.Add("'" + change.GetValue(entity, null).ToString().Replace("'", "char(39)") + "'"); else arguments.Add(change.GetValue(entity, null)); } if (builder.Length == 0) throw new Exception("沒有任何屬性進行更新"); builder.Insert(0, " UPDATE " + string.Format("[{0}]", entityType.Name) + " SET "); builder.Append(" WHERE "); bool firstPrimaryKey = true; foreach (var primaryField in pkList) { if (firstPrimaryKey) firstPrimaryKey = false; else builder.Append(" AND "); object val = entityType.GetProperty(primaryField).GetValue(entity, null); builder.Append(GetEqualStatment(primaryField, arguments.Count)); arguments.Add(val); } return new Tuple<string, object[]>(builder.ToString(), arguments.ToArray()); } /// <summary> /// 構建Delete語句串 /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="entity"></param> /// <returns></returns> private Tuple<string, object[]> CreateDeleteSQL<TEntity>(TEntity entity) where TEntity : class { if (entity == null) throw new ArgumentException("The database entity can not be null."); Type entityType = entity.GetType(); List<string> pkList = GetPrimaryKey<TEntity>().Select(i => i.Name).ToList(); if (pkList == null || pkList.Count == 0) throw new ArgumentException("The Table entity have not a primary key."); List<object> arguments = new List<object>(); StringBuilder builder = new StringBuilder(); builder.Append(" Delete from " + string.Format("[{0}]", entityType.Name)); builder.Append(" WHERE "); bool firstPrimaryKey = true; foreach (var primaryField in pkList) { if (firstPrimaryKey) firstPrimaryKey = false; else builder.Append(" AND "); object val = entityType.GetProperty(primaryField).GetValue(entity, null); builder.Append(GetEqualStatment(primaryField, arguments.Count)); arguments.Add(val); } return new Tuple<string, object[]>(builder.ToString(), arguments.ToArray()); } /// <summary> /// 構建Insert語句串 /// 主鍵為自增時，如果主鍵值為0，我們將主鍵插入到SQL串中 /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="entity"></param> /// <returns></returns> private Tuple<string, object[]> CreateInsertSQL<TEntity>(TEntity entity) where TEntity : class { if (entity == null) throw new ArgumentException("The database entity can not be null."); Type entityType = entity.GetType(); var table = entityType.GetProperties().Where(i => i.PropertyType != typeof(EntityKey) && i.PropertyType != typeof(EntityState) && i.Name != "IsValid" && i.GetValue(entity, null) != null && !(i.GetCustomAttributes(false).Length > 0 && i.GetCustomAttributes(false).Where(j => j.GetType() == typeof(NavigationAttribute)) != null) && (i.PropertyType.IsValueType || i.PropertyType == typeof(string))).ToArray();//過濾主鍵，航行屬性，狀態屬性等 List<string> pkList = GetPrimaryKey<TEntity>().Select(i => i.Name).ToList(); List<object> arguments = new List<object>(); StringBuilder fieldbuilder = new StringBuilder(); StringBuilder valuebuilder = new StringBuilder(); fieldbuilder.Append(" INSERT INTO " + string.Format("[{0}]", entityType.Name) + " ("); foreach (var member in table) { if (pkList.Contains(member.Name) && Convert.ToString(member.GetValue(entity, null)) == "0") continue; object value = member.GetValue(entity, null); if (value != null) { if (arguments.Count != 0) { fieldbuilder.Append(", "); valuebuilder.Append(", "); } fieldbuilder.Append(member.Name); if (member.PropertyType == typeof(string) || member.PropertyType == typeof(DateTime)) valuebuilder.Append("'{" + arguments.Count + "}'"); else valuebuilder.Append("{" + arguments.Count + "}"); if (value.GetType() == typeof(string)) value = value.ToString().Replace("'", "char(39)"); arguments.Add(value); } } fieldbuilder.Append(") Values ("); fieldbuilder.Append(valuebuilder.ToString()); fieldbuilder.Append(");"); return new Tuple<string, object[]>(fieldbuilder.ToString(), arguments.ToArray()); } /// <summary> /// 執行ＳＱＬ，根據ＳＱＬ操作的型別 /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="list"></param> /// <param name="sqlType"></param> /// <returns></returns> protected string DoSQL<TEntity>(IEnumerable<TEntity> list, SQLType sqlType) where TEntity : class { StringBuilder sqlstr = new StringBuilder(); switch (sqlType) { case SQLType.Insert: list.ToList().ForEach(i => { Tuple<string, object[]> sql = CreateInsertSQL(i); sqlstr.AppendFormat(sql.Item1, sql.Item2); }); break; case SQLType.Update: list.ToList().ForEach(i => { Tuple<string, object[]> sql = CreateUpdateSQL(i); sqlstr.AppendFormat(sql.Item1, sql.Item2); }); break; case SQLType.Delete: list.ToList().ForEach(i => { Tuple<string, object[]> sql = CreateDeleteSQL(i); sqlstr.AppendFormat(sql.Item1, sql.Item2); }); break; default: throw new ArgumentException("請輸入正確的引數"); } return sqlstr.ToString(); }
前方永遠都是通往成功的路，只要你相信，它就會更快的實現...