Linq To Sql 之批量更新
发表时间：8月. 31st, 2009 作者： 李子哥哥 in Linq
最近才来研究Linq To Sql，有点落后了，写了个 Linq To Sql 扩展实现批量删除，给大家分享下。。。
没有经过太多测试，可能有很多错误，要是哪位高手修改了也给我一份，分享一下，Mail：kuiyouli@126.com
Code1
using System;2
using System.Collections.Generic;3
using System.Linq;4
using System.Text;5
using System.Data.Common;6
using System.Linq.Expressions;7
using System.Reflection;8
using System.Data.SqlClient;9
using System.Data.Linq;10
using System.Text.RegularExpressions;11
12
namespace OC.LinqToSqlEx13
{14
15
public static class Extend16
{17
Update#region Update18
19
批量更新#region 批量更新20
public static int Update<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, TEntity>> evaluator) where TEntity : class21
{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 : class26
{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
else55
{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
try109
{110
if (whereCmd.Connection.State != System.Data.ConnectionState.Open)111
{112
whereCmd.Connection.Open();113
}114
return whereCmd.ExecuteNonQuery();115
}116
finally117
{118
whereCmd.Connection.Close();119
whereCmd.Dispose();120
}121
}122
123
#endregion124
125
单个更新#region 单个更新126
public static int Update<TEntity>(this DataContext dc, TEntity entity) where TEntity : class127
{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
else143
{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
try165
{166
return cmd.ExecuteNonQuery();167
}168
finally169
{170
cmd.Connection.Close();171
}172
}173
174
#endregion175
176
#endregion177
}178
}179
代码下载：
http://www.oo-css.cn/download/linq/LinqUpdaetExtend.rar
27 条评论
denDED
11月. 17th, 2009
чтобы добавлять свои статьи, обязательно ли регистрироватся?
nintendost
11月. 17th, 2009
Article very interesting, I will necessarily add it in the selected works and I will visit this site
nintendostiks
11月. 20th, 2009
Article very interesting, I will necessarily add it in the selected works and I will visit this site
gigantplsa
11月. 21st, 2009
Article very interesting, I will necessarily add it in the selected works and I will visit this site
levitra_online_buy
11月. 23rd, 2009
Hi. This is a super post!
cialis_online_buy
11月. 23rd, 2009
Hi. This is a super post!
李子哥哥
11月. 25th, 2009
谢谢关注
李子哥哥
11月. 25th, 2009
悲剧啊，这句看不懂
Paydaymister
12月. 2nd, 2009
author’s note seemed to me very helpful and changed my outlook on many things.
Viktorqqwa
12月. 3rd, 2009
Article very interesting, I will necessarily add it in the selected works and I will visit this site
Antiagingmen
12月. 3rd, 2009
Thanks to your article I really liked. Be sure to add your blog to their favorites
李子哥哥
12月. 18th, 2009
Thanks for your interesting in our website.
李子哥哥
12月. 18th, 2009
Thanks for your interesting in our website.
Paydaymen
12月. 28th, 2009
Thank you for a good story, I really enjoyed your blog. Be sure to give a link to your friends!
Dublemen
1月. 10th, 2010
Hello, I want to congratulate your site with 2010. I am sure that, in this new year, your article will please his readers.
Paydayloan
1月. 25th, 2010
Good Luck
Каталог статей
1月. 28th, 2010
What amusing topic
узбекское телевидение онлайн
5月. 12th, 2010
ок то что искал
футболки на заказ петербург
5月. 18th, 2010
ок автор прав не спорить
MasterPillsy
6月. 1st, 2010
Ambler compliment to asafoetida.Gametogenesis codeword or askance virtuous and cheerfully dainty coot. cialis dreampharmaceuticals from online.
MastUrnike
6月. 2nd, 2010
I am pleased with your blog, I think we have a lot in common. I’ll go to you on more often.
Jobrabota
6月. 4th, 2010
I am pleased with your blog, I think we have a lot in common. I’ll go to you on more often.
Autooloan
6月. 7th, 2010
I am pleased with your blog, I think we have a lot in common. I’ll go to you on more often.
Forexcoolclab
6月. 11th, 2010
I am pleased with your blog, I think we have a lot in common. I’ll go to you on more often Forex cool.
Cialisbalaba
6月. 17th, 2010
I am pleased with your blog, I think we have a lot in common. I’ll go to you on more often Cialis cool.
требования к установке газовых котлов
6月. 17th, 2010
ок забегу на медне
Cialismanemun
7月. 29th, 2010
You have a very good blog. Your note, I liked .. I hope my site and will make you happy.
发表评论