回到目录 对于linq to sql提供的CURD 操作 ，给我们的感觉就是简单，容易使用，更加面向对象，不用拼SQL语句了，这些好处都表示在处理单条实体或者集合长度小的情况下，如果有一个1000条的集合实体，希望进行update 操作 ，如果你还用linq to sql提代的updat
回到目录
对于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没来的及实现）
<span >///</span> <span ><summary></span>
<span >///</span><span > SQL<strong>操作</strong>类型
</span><span >///</span> <span ></summary></span>
<span >protected</span> <span >enum</span><span > SQLType
{
Insert,
Update,
Delete,
}
</span><span >///</span> <span ><summary></span>
<span >///</span><span > 构建Update语句串
</span><span >///</span> <span ></summary></span>
<span >///</span> <span ><typeparam name="TEntity"></typeparam></span>
<span >///</span> <span ><param name="entity"></param></span>
<span >///</span> <span ><returns></returns></span>
<span >private</span> Tuple<<span >string</span>, <span >object</span>[]> CreateUpdateSQL<TEntity>(TEntity entity) <span >where</span> TEntity : <span >class</span><span >
{
</span><span >if</span> (entity == <span >null</span><span >)
</span><span >throw</span> <span >new</span> ArgumentException(<span >"</span><span >The database entity can not be null.</span><span >"</span><span >);
List</span><<span >string</span>> pkList = GetPrimaryKey<TEntity>().Select(i =><span > i.Name).ToList();
Type entityType </span>=<span > entity.GetType();
</span><span >var</span> table = entityType.GetProperties().Where(i =>
!<span >pkList.Contains(i.Name)
</span>&& i.GetValue(entity, <span >null</span>) != <span >null</span>
&& i.PropertyType != <span >typeof</span><span >(EntityState)
</span>&& !(i.GetCustomAttributes(<span >false</span>).Length > <span >0</span>
&& i.GetCustomAttributes(<span >false</span>).Where(j => j.GetType() == <span >typeof</span>(NavigationAttribute)) != <span >null</span><span >)
</span>&& (i.PropertyType.IsValueType || i.PropertyType == <span >typeof</span>(<span >string</span>)) <span >//</span><span >过滤导航属性</span>
<span > ).ToArray();
</span><span >//</span><span >过滤主键，航行属性，状态属性等</span>
<span >if</span> (pkList == <span >null</span> || pkList.Count == <span >0</span><span >)
</span><span >throw</span> <span >new</span> ArgumentException(<span >"</span><span >The Table entity have not a primary key.</span><span >"</span><span >);
List</span><<span >object</span>> arguments = <span >new</span> List<<span >object</span>><span >();
StringBuilder builder </span>= <span >new</span><span > StringBuilder();
</span><span >foreach</span> (<span >var</span> change <span >in</span><span > table)
{
</span><span >if</span><span > (pkList.Contains(change.Name))
</span><span >continue</span><span >;
</span><span >if</span> (arguments.Count != <span >0</span><span >)
builder.Append(</span><span >"</span><span >, </span><span >"</span><span >);
builder.Append(change.Name </span>+ <span >"</span><span > = {</span><span >"</span> + arguments.Count + <span >"</span><span >}</span><span >"</span><span >);
</span><span >if</span> (change.PropertyType == <span >typeof</span>(<span >string</span>) || change.PropertyType == <span >typeof</span><span >(DateTime))
arguments.Add(</span><span >"</span><span >'</span><span >"</span> + change.GetValue(entity, <span >null</span>).ToString().Replace(<span >"</span><span >'</span><span >"</span>, <span >"</span><span >char(39)</span><span >"</span>) + <span >"</span><span >'</span><span >"</span><span >);
</span><span >else</span><span >
arguments.Add(change.GetValue(entity, </span><span >null</span><span >));
}
</span><span >if</span> (builder.Length == <span >0</span><span >)
</span><span >throw</span> <span >new</span> Exception(<span >"</span><span >没有任何属性进行更新</span><span >"</span><span >);
builder.Insert(</span><span >0</span>, <span >"</span><span > UPDATE </span><span >"</span> + <span >string</span>.Format(<span >"</span><span >[{0}]</span><span >"</span>, entityType.Name) + <span >"</span><span > SET </span><span >"</span><span >);
builder.Append(</span><span >"</span><span > WHERE </span><span >"</span><span >);
</span><span >bool</span> firstPrimaryKey = <span >true</span><span >;
</span><span >foreach</span> (<span >var</span> primaryField <span >in</span><span > pkList)
{
</span><span >if</span><span > (firstPrimaryKey)
firstPrimaryKey </span>= <span >false</span><span >;
</span><span >else</span><span >
builder.Append(</span><span >"</span><span > AND </span><span >"</span><span >);
</span><span >object</span> val = entityType.GetProperty(primaryField).GetValue(entity, <span >null</span><span >);
builder.Append(GetEqualStatment(primaryField, arguments.Count));
arguments.Add(val);
}
</span><span >return</span> <span >new</span> Tuple<<span >string</span>, <span >object</span>[]><span >(builder.ToString(), arguments.ToArray());
}
</span><span >///</span> <span ><summary></span>
<span >///</span><span > 构建Delete语句串
</span><span >///</span> <span ></summary></span>
<span >///</span> <span ><typeparam name="TEntity"></typeparam></span>
<span >///</span> <span ><param name="entity"></param></span>
<span >///</span> <span ><returns></returns></span>
<span >private</span> Tuple<<span >string</span>, <span >object</span>[]> CreateDeleteSQL<TEntity>(TEntity entity) <span >where</span> TEntity : <span >class</span><span >
{
</span><span >if</span> (entity == <span >null</span><span >)
</span><span >throw</span> <span >new</span> ArgumentException(<span >"</span><span >The database entity can not be null.</span><span >"</span><span >);
Type entityType </span>=<span > entity.GetType();
List</span><<span >string</span>> pkList = GetPrimaryKey<TEntity>().Select(i =><span > i.Name).ToList();
</span><span >if</span> (pkList == <span >null</span> || pkList.Count == <span >0</span><span >)
</span><span >throw</span> <span >new</span> ArgumentException(<span >"</span><span >The Table entity have not a primary key.</span><span >"</span><span >);
List</span><<span >object</span>> arguments = <span >new</span> List<<span >object</span>><span >();
StringBuilder builder </span>= <span >new</span><span > StringBuilder();
builder.Append(</span><span >"</span><span > Delete from </span><span >"</span> + <span >string</span>.Format(<span >"</span><span >[{0}]</span><span >"</span><span >, entityType.Name));
builder.Append(</span><span >"</span><span > WHERE </span><span >"</span><span >);
</span><span >bool</span> firstPrimaryKey = <span >true</span><span >;
</span><span >foreach</span> (<span >var</span> primaryField <span >in</span><span > pkList)
{
</span><span >if</span><span > (firstPrimaryKey)
firstPrimaryKey </span>= <span >false</span><span >;
</span><span >else</span><span >
builder.Append(</span><span >"</span><span > AND </span><span >"</span><span >);
</span><span >object</span> val = entityType.GetProperty(primaryField).GetValue(entity, <span >null</span><span >);
builder.Append(GetEqualStatment(primaryField, arguments.Count));
arguments.Add(val);
}
</span><span >return</span> <span >new</span> Tuple<<span >string</span>, <span >object</span>[]><span >(builder.ToString(), arguments.ToArray());
}
</span><span >///</span> <span ><summary></span>
<span >///</span><span > 构建Insert语句串
</span><span >///</span><span > 主键为自增时，如果主键值为0，我们将主键插入到SQL串中
</span><span >///</span> <span ></summary></span>
<span >///</span> <span ><typeparam name="TEntity"></typeparam></span>
<span >///</span> <span ><param name="entity"></param></span>
<span >///</span> <span ><returns></returns></span>
<span >private</span> Tuple<<span >string</span>, <span >object</span>[]> CreateInsertSQL<TEntity>(TEntity entity) <span >where</span> TEntity : <span >class</span><span >
{
</span><span >if</span> (entity == <span >null</span><span >)
</span><span >throw</span> <span >new</span> ArgumentException(<span >"</span><span >The database entity can not be null.</span><span >"</span><span >);
Type entityType </span>=<span > entity.GetType();
</span><span >var</span> table = entityType.GetProperties().Where(i => i.PropertyType != <span >typeof</span><span >(EntityKey)
</span>&& i.PropertyType != <span >typeof</span><span >(EntityState)
</span>&& i.Name != <span >"</span><span >IsValid</span><span >"</span>
&& i.GetValue(entity, <span >null</span>) != <span >null</span>
&& !(i.GetCustomAttributes(<span >false</span>).Length > <span >0</span>
&& i.GetCustomAttributes(<span >false</span>).Where(j => j.GetType() == <span >typeof</span>(NavigationAttribute)) != <span >null</span><span >)
</span>&& (i.PropertyType.IsValueType || i.PropertyType == <span >typeof</span>(<span >string</span>))).ToArray();<span >//</span><span >过滤主键，航行属性，状态属性等</span>
<span >
List</span><<span >string</span>> pkList = GetPrimaryKey<TEntity>().Select(i =><span > i.Name).ToList();
List</span><<span >object</span>> arguments = <span >new</span> List<<span >object</span>><span >();
StringBuilder fieldbuilder </span>= <span >new</span><span > StringBuilder();
StringBuilder valuebuilder </span>= <span >new</span><span > StringBuilder();
fieldbuilder.Append(</span><span >"</span><span > INSERT INTO </span><span >"</span> + <span >string</span>.Format(<span >"</span><span >[{0}]</span><span >"</span>, entityType.Name) + <span >"</span><span > (</span><span >"</span><span >);
</span><span >foreach</span> (<span >var</span> member <span >in</span><span > table)
{
</span><span >if</span> (pkList.Contains(member.Name) && Convert.ToString(member.GetValue(entity, <span >null</span>)) == <span >"</span><span >0</span><span >"</span><span >)
</span><span >continue</span><span >;
</span><span >object</span> value = member.GetValue(entity, <span >null</span><span >);
</span><span >if</span> (value != <span >null</span><span >)
{
</span><span >if</span> (arguments.Count != <span >0</span><span >)
{
fieldbuilder.Append(</span><span >"</span><span >, </span><span >"</span><span >);
valuebuilder.Append(</span><span >"</span><span >, </span><span >"</span><span >);
}
fieldbuilder.Append(member.Name);
</span><span >if</span> (member.PropertyType == <span >typeof</span>(<span >string</span>) || member.PropertyType == <span >typeof</span><span >(DateTime))
valuebuilder.Append(</span><span >"</span><span >'{</span><span >"</span> + arguments.Count + <span >"</span><span >}'</span><span >"</span><span >);
</span><span >else</span><span >
valuebuilder.Append(</span><span >"</span><span >{</span><span >"</span> + arguments.Count + <span >"</span><span >}</span><span >"</span><span >);
</span><span >if</span> (value.GetType() == <span >typeof</span>(<span >string</span><span >))
value </span>= value.ToString().Replace(<span >"</span><span >'</span><span >"</span>, <span >"</span><span >char(39)</span><span >"</span><span >);
arguments.Add(value);
}
}
fieldbuilder.Append(</span><span >"</span><span >) Values (</span><span >"</span><span >);
fieldbuilder.Append(valuebuilder.ToString());
fieldbuilder.Append(</span><span >"</span><span >);</span><span >"</span><span >);
</span><span >return</span> <span >new</span> Tuple<<span >string</span>, <span >object</span>[]><span >(fieldbuilder.ToString(), arguments.ToArray());
}
</span><span >///</span> <span ><summary></span>
<span >///</span><span > 执行ＳＱＬ，根据ＳＱＬ<strong>操作</strong>的类型
</span><span >///</span> <span ></summary></span>
<span >///</span> <span ><typeparam name="TEntity"></typeparam></span>
<span >///</span> <span ><param name="list"></param></span>
<span >///</span> <span ><param name="sqlType"></param></span>
<span >///</span> <span ><returns></returns></span>
<span >protected</span> <span >string</span> DoSQL<TEntity>(IEnumerable<TEntity> list, SQLType sqlType) <span >where</span> TEntity : <span >class</span><span >
{
StringBuilder sqlstr </span>= <span >new</span><span > StringBuilder();
</span><span >switch</span><span > (sqlType)
{
</span><span >case</span><span > SQLType.Insert:
list.ToList().ForEach(i </span>=><span >
{
Tuple</span><<span >string</span>, <span >object</span>[]> sql =<span > CreateInsertSQL(i);
sqlstr.AppendFormat(sql.Item1, sql.Item2);
});
</span><span >break</span><span >;
</span><span >case</span><span > SQLType.Update:
list.ToList().ForEach(i </span>=><span >
{
Tuple</span><<span >string</span>, <span >object</span>[]> sql =<span > CreateUpdateSQL(i);
sqlstr.AppendFormat(sql.Item1, sql.Item2);
});
</span><span >break</span><span >;
</span><span >case</span><span > SQLType.Delete:
list.ToList().ForEach(i </span>=><span >
{
Tuple</span><<span >string</span>, <span >object</span>[]> sql =<span > CreateDeleteSQL(i);
sqlstr.AppendFormat(sql.Item1, sql.Item2);
});
</span><span >break</span><span >;
</span><span >default</span><span >:
</span><span >throw</span> <span >new</span> ArgumentException(<span >"</span><span >请输入正确的参数</span><span >"</span><span >);
}
</span><span >return</span><span > sqlstr.ToString();
}</span>
前方永远都是通往成功的路，只要你相信，它就会更快的实现...
回到目录
每个人都需要一台速度更快、更稳定的 PC。随着时间的推移，垃圾文件、旧注册表数据和不必要的后台进程会占用资源并降低性能。幸运的是，许多工具可以让 Windows 保持平稳运行。
广告Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
601
收藏
