Linq To Sql 之批量更新

      最近才来研究Linq To Sql，有点落后了，写了个 Linq To Sql 扩展实现批量删除，给大家分享下。。。
      没有经过太多测试，可能有很多错误，要是哪位高手修改了也给我一份，分享一下，我的QQ：66047391，Mail： kuiyouli@126.com

Code
  1using System;
  2using System.Collections.Generic;
  3using System.Linq;
  4using System.Text;
  5using System.Data.Common;
  6using System.Linq.Expressions;
  7using System.Reflection;
  8using System.Data.SqlClient;
  9using System.Data.Linq;
 10using System.Text.RegularExpressions;
 11
 12namespace OC.LinqToSqlEx
 13{
 14
 15    public static class Extend
 16    {
 17        Update#region Update
 18
 19        批量更新#region 批量更新
 20        public static int Update<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class
 21        {
 22            return Update(table, evaluator, o => true);
 23        }
 24
 25        public static int Update<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, TEntity>> evaluator, Expression<Func<TEntity, bool>> predicate) where TEntity : class
 26        {
 27            var bindings = ((System.Linq.Expressions.MemberInitExpression)(((System.Linq.Expressions.LambdaExpression)(evaluator)).Body)).Bindings;
 28            var custs = table.AsQueryable();
 29            ParameterExpression param = evaluator.Parameters[0];
 30
 31            Expression allFilter = null;
 32            PropertyInfo firstMember = null;
 33            foreach (var b in bindings)
 34            {
 35                PropertyInfo member = (PropertyInfo)b.Member;
 36                var right = ((System.Linq.Expressions.MemberAssignment)(b)).Expression;
 37                if (right is System.Linq.Expressions.ConstantExpression)
 38                {
 39                    right = Expression.Constant(((ConstantExpression)right).Value, member.PropertyType);
 40                }
 41
 42                if (firstMember == null)
 43                {
 44                    firstMember = member;
 45                }
 46
 47                var left = Expression.Property(param, member);
 48                var filter = Expression.Equal(left, right);
 49
 50                if (allFilter == null)
 51                {
 52                    allFilter = filter;
 53                }
 54                else
 55                {
 56                    var spilthFilter = Expression.Equal(Expression.Property(param, firstMember), Expression.Property(param, firstMember));
 57                    allFilter = Expression.And(allFilter, spilthFilter);
 58                    allFilter = Expression.And(allFilter, filter);
 59                }
 60            }
 61
 62            List<DbParameter> parameters = new List<DbParameter>();
 63            var alias = "";
 64            var setStr = "";
 65
 66            if (allFilter != null)
 67            {
 68                Expression pred = Expression.Lambda(allFilter, param);
 69                Expression expr = Expression.Call(typeof(Queryable), "Where", new Type[] { param.Type }, Expression.Constant(custs), pred);
 70                var query = table.AsQueryable().Provider.CreateQuery(expr);
 71                var cmd = table.Context.GetCommand(query);
 72                string sql = cmd.CommandText.Replace('\n', ' ').Replace('\r', ' ');
 73                var whereIndex = sql.ToUpper().IndexOf("WHERE ");
 74                setStr = sql.Substring(whereIndex + 6);
 75                alias = System.Text.RegularExpressions.Regex.Replace(sql.Substring(0, whereIndex).ToUpper(), ".* AS ", "");
 76                alias = System.Text.RegularExpressions.Regex.Replace(alias, @" |\[|\]", "");
 77
 78                var pattern = string.Format(@"\)\s*AND\s*\(\s*\[{0}\]\s*.\s*\[{1}\]\s*=\s*\[{0}\]\s*.\s*\[{1}\]\s*\)\s*AND\s*\(", alias, firstMember.Name);
 79                setStr = System.Text.RegularExpressions.Regex.Replace(setStr, pattern, " , ", RegexOptions.IgnoreCase);
 80                setStr = System.Text.RegularExpressions.Regex.Replace(setStr, string.Format(@"\[{0}\].", alias), "", RegexOptions.IgnoreCase).Trim();
 81                if (setStr.StartsWith("("))
 82                {
 83                    setStr = setStr.Substring(1, setStr.Length - 2);
 84                }
 85                int pi = 0;
 86                foreach (DbParameter p in cmd.Parameters)
 87                {
 88                    var newName = "@setParam_" + pi++;
 89                    setStr = setStr.Replace(p.ParameterName, newName);
 90                    p.ParameterName = newName;
 91                    parameters.Add(p);
 92                }
 93                cmd.Parameters.Clear();
 94            }
 95
 96            setStr = setStr.Trim(new char[] { ' ', ',' });
 97
 98            var tableMapping = table.Context.Mapping.GetTable(param.Type);
 99
100            var whereCmd = table.Context.GetCommand(table.Where(predicate));
101            var whereIndex2 = whereCmd.CommandText.ToUpper().IndexOf("WHERE");
102            var whereStr = whereIndex2 > -1 ? whereCmd.CommandText.Replace('\r', ' ').Replace('\n', ' ').Substring(whereIndex2) : "";
103            var updateStr = string.Format("UPDATE {0} SET {1} FROM {0} AS {2} {3}", tableMapping.TableName, setStr, alias, whereStr);
104
105            whereCmd.CommandText = updateStr;
106            whereCmd.Parameters.AddRange(parameters.ToArray());
107
108            try
109            {
110                if (whereCmd.Connection.State != System.Data.ConnectionState.Open)
111                {
112                    whereCmd.Connection.Open();
113                }
114                return whereCmd.ExecuteNonQuery();
115            }
116            finally
117            {
118                whereCmd.Connection.Close();
119                whereCmd.Dispose();
120            }
121        }
122
123        #endregion
124
125        单个更新#region 单个更新
126        public static int Update<TEntity>(this DataContext dc, TEntity entity) where TEntity : class
127        {
128            var mapping =dc.Mapping.GetTable(entity.GetType());
129            var rowType = mapping.RowType;
130            string condition = "";
131            string setStr = "";
132            List<SqlParameter> args = new List<SqlParameter>();
133            foreach (var member in rowType.DataMembers)
134            {
135                if (!member.IsAssociation)
136                {
137                    string pName = string.Format("@p{0}", args.Count);
138                    if (member.IsPrimaryKey)
139                    {
140                        condition += string.Format("AND {0} = {1}", member.MappedName, pName);
141                    }
142                    else
143                    {
144                        setStr += string.Format(", {0} = {1}", member.MappedName, pName);
145                    }
146                    var value = member.MemberAccessor.GetBoxedValue(entity);
147                    value = value == null ? DBNull.Value : value;
148                    SqlParameter param = new SqlParameter(pName, value);
149                    args.Add(param);
150                }
151            }
152
153            IQueryable q = from o in dc.GetTable<TEntity>() select o;
154            var cmd = dc.GetCommand(q);
155            string tableName = mapping.TableName;
156            tableName = "[" + tableName.Replace(".", "].[") + "]";
157            string sql = string.Format("UPDATE {0} SET {1} WHERE {2}", tableName, setStr.Substring(2), condition.Substring(4));
158            cmd.CommandText = sql;
159            cmd.Parameters.AddRange(args.ToArray());
160            if (cmd.Connection.State != System.Data.ConnectionState.Open)
161            {
162                cmd.Connection.Open();
163            }
164            try
165            {
166                return cmd.ExecuteNonQuery();
167            }
168            finally
169            {
170                cmd.Connection.Close();
171            }
172        }
173
174        #endregion
175
176        #endregion
177    }
178}
179

代码下载：
/Files/kuiyouli/LinqTest.rar

转载于:https://www.cnblogs.com/kuiyouli/archive/2009/07/30/1535461.html