回到目录 对于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></summary></span>
<span>///</span><span> SQL<strong>操作</strong>类型
</span><span>///</span> <span></span>
<span>protected</span> <span>enum</span><span> SQLType
{
Insert,
Update,
Delete,
}
</span><span>///</span> <span><summary></summary></span>
<span>///</span><span> 构建Update语句串
</span><span>///</span> <span></span>
<span>///</span> <span><typeparam name="TEntity"></typeparam></span>
<span>///</span> <span><param name="entity"></span>
<span>///</span> <span><returns></returns></span>
<span>private</span> Tuplestring, <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>string> 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>object> arguments = <span>new</span> Listobject><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> Tuplestring, <span>object</span>[]><span>(builder.ToString(), arguments.ToArray());
}
</span><span>///</span> <span><summary></summary></span>
<span>///</span><span> 构建Delete语句串
</span><span>///</span> <span></span>
<span>///</span> <span><typeparam name="TEntity"></typeparam></span>
<span>///</span> <span><param name="entity"></span>
<span>///</span> <span><returns></returns></span>
<span>private</span> Tuplestring, <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>string> 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>object> arguments = <span>new</span> Listobject><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> Tuplestring, <span>object</span>[]><span>(builder.ToString(), arguments.ToArray());
}
</span><span>///</span> <span><summary></summary></span>
<span>///</span><span> 构建Insert语句串
</span><span>///</span><span> 主键为自增时，如果主键值为0，我们将主键插入到SQL串中
</span><span>///</span> <span></span>
<span>///</span> <span><typeparam name="TEntity"></typeparam></span>
<span>///</span> <span><param name="entity"></span>
<span>///</span> <span><returns></returns></span>
<span>private</span> Tuplestring, <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>string> pkList = GetPrimaryKey<tentity>().Select(i =><span> i.Name).ToList();
List</span>object> arguments = <span>new</span> Listobject><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> Tuplestring, <span>object</span>[]><span>(fieldbuilder.ToString(), arguments.ToArray());
}
</span><span>///</span> <span><summary></summary></span>
<span>///</span><span> 执行ＳＱＬ，根据ＳＱＬ<strong>操作</strong>的类型
</span><span>///</span> <span></span>
<span>///</span> <span><typeparam name="TEntity"></typeparam></span>
<span>///</span> <span><param name="list"></span>
<span>///</span> <span><param name="sqlType"></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>string, <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>string, <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>string, <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></tentity></tentity></tentity></tentity></tentity></tentity></tentity></tentity>
前方永远都是通往成功的路，只要你相信，它就会更快的实现...
回到目录
陳述
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn
MySQL的許可與其他數據庫系統相比如何？Apr 25, 2025 am 12:26 AMMySQL使用的是GPL許可證。 1）GPL許可證允許自由使用、修改和分發MySQL，但修改後的分發需遵循GPL。 2）商業許可證可避免公開修改，適合需要保密的商業應用。
您什麼時候選擇InnoDB而不是Myisam，反之亦然？Apr 25, 2025 am 12:22 AM選擇InnoDB而不是MyISAM的情況包括：1)需要事務支持，2)高並發環境，3)需要高數據一致性；反之，選擇MyISAM的情況包括：1)主要是讀操作，2)不需要事務支持。 InnoDB適合需要高數據一致性和事務處理的應用，如電商平台，而MyISAM適合讀密集型且無需事務的應用，如博客系統。
在MySQL中解釋外鍵的目的。Apr 25, 2025 am 12:17 AM在MySQL中，外鍵的作用是建立表與表之間的關係，確保數據的一致性和完整性。外鍵通過引用完整性檢查和級聯操作維護數據的有效性，使用時需注意性能優化和避免常見錯誤。
MySQL中有哪些不同類型的索引？Apr 25, 2025 am 12:12 AMMySQL中有四種主要的索引類型：B-Tree索引、哈希索引、全文索引和空間索引。 1.B-Tree索引適用於範圍查詢、排序和分組，適合在employees表的name列上創建。 2.哈希索引適用於等值查詢，適合在MEMORY存儲引擎的hash_table表的id列上創建。 3.全文索引用於文本搜索，適合在articles表的content列上創建。 4.空間索引用於地理空間查詢，適合在locations表的geom列上創建。
您如何在MySQL中創建索引？Apr 25, 2025 am 12:06 AMtoCreateAnIndexinMysql，usethecReateIndexStatement.1）forasingLecolumn，使用“ createIndexIdx_lastNameEnemployees（lastName）; 2）foracompositeIndex，使用“ createIndexIndexIndexIndexIndexDx_nameOmplayees（lastName，firstName，firstName）;” 3）forauniqe instex，creationexexexexex，
MySQL與Sqlite有何不同？Apr 24, 2025 am 12:12 AMMySQL和SQLite的主要區別在於設計理念和使用場景：1.MySQL適用於大型應用和企業級解決方案，支持高性能和高並發；2.SQLite適合移動應用和桌面軟件，輕量級且易於嵌入。
MySQL中的索引是什麼？它們如何提高性能？Apr 24, 2025 am 12:09 AMMySQL中的索引是數據庫表中一列或多列的有序結構，用於加速數據檢索。 1）索引通過減少掃描數據量提升查詢速度。 2）B-Tree索引利用平衡樹結構，適合範圍查詢和排序。 3）創建索引使用CREATEINDEX語句，如CREATEINDEXidx_customer_idONorders(customer_id)。 4）複合索引可優化多列查詢，如CREATEINDEXidx_customer_orderONorders(customer_id,order_date)。 5）使用EXPLAIN分析查詢計劃，避
說明如何使用MySQL中的交易來確保數據一致性。Apr 24, 2025 am 12:09 AM在MySQL中使用事務可以確保數據一致性。 1)通過STARTTRANSACTION開始事務，執行SQL操作後用COMMIT提交或ROLLBACK回滾。 2)使用SAVEPOINT可以設置保存點，允許部分回滾。 3)性能優化建議包括縮短事務時間、避免大規模查詢和合理使用隔離級別。
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
Video Face Swap
使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！
熱門文章
刺客信條陰影：貝殼謎語解決方案
4 週前ByDDD
Windows 11 KB5054979中的新功能以及如何解決更新問題
3 週前ByDDD
在哪裡可以找到原子中的起重機控制鑰匙卡
4 週前ByDDD
<🎜>：死鐵路 - 如何完成所有挑戰
1 個月前ByDDD
Atomfall指南：項目位置，任務指南和技巧
1 個月前ByDDD
熱工具
ZendStudio 13.5.1 Mac
強大的PHP整合開發環境
SublimeText3 英文版
推薦：為Win版本，支援程式碼提示！
記事本++7.3.1
好用且免費的程式碼編輯器
SAP NetWeaver Server Adapter for Eclipse
將Eclipse與SAP NetWeaver應用伺服器整合。
WebStorm Mac版
好用的JavaScript開發工具
