LINQ-to-SQL那点事~利用反射在LINQ-to-SQL环境中实现Ado.net的CU
回到目录 对于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>
前方永远都是通往成功的路，只要你相信，它就会更快的实现...
回到目录
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1371
52
Reflection mechanism implementation of interfaces and abstract classes in Java
May 02, 2024 pm 05:18 PM
The reflection mechanism allows programs to obtain and modify class information at runtime. It can be used to implement reflection of interfaces and abstract classes: Interface reflection: obtain the interface reflection object through Class.forName() and access its metadata (name, method and field) . Reflection of abstract classes: Similar to interfaces, you can obtain the reflection object of an abstract class and access its metadata and non-abstract methods. Practical case: The reflection mechanism can be used to implement dynamic proxies, intercepting calls to interface methods at runtime by dynamically creating proxy classes.
How to implement dual WeChat login on Huawei mobile phones?
Mar 24, 2024 am 11:27 AM
How to implement dual WeChat login on Huawei mobile phones? With the rise of social media, WeChat has become one of the indispensable communication tools in people's daily lives. However, many people may encounter a problem: logging into multiple WeChat accounts at the same time on the same mobile phone. For Huawei mobile phone users, it is not difficult to achieve dual WeChat login. This article will introduce how to achieve dual WeChat login on Huawei mobile phones. First of all, the EMUI system that comes with Huawei mobile phones provides a very convenient function - dual application opening. Through the application dual opening function, users can simultaneously
How to use reflection to access private fields and methods in golang
May 03, 2024 pm 12:15 PM
You can use reflection to access private fields and methods in Go language: To access private fields: obtain the reflection value of the value through reflect.ValueOf(), then use FieldByName() to obtain the reflection value of the field, and call the String() method to print the value of the field . Call a private method: also obtain the reflection value of the value through reflect.ValueOf(), then use MethodByName() to obtain the reflection value of the method, and finally call the Call() method to execute the method. Practical case: Modify private field values and call private methods through reflection to achieve object control and unit test coverage.
PHP Programming Guide: Methods to Implement Fibonacci Sequence
Mar 20, 2024 pm 04:54 PM
The programming language PHP is a powerful tool for web development, capable of supporting a variety of different programming logics and algorithms. Among them, implementing the Fibonacci sequence is a common and classic programming problem. In this article, we will introduce how to use the PHP programming language to implement the Fibonacci sequence, and attach specific code examples. The Fibonacci sequence is a mathematical sequence defined as follows: the first and second elements of the sequence are 1, and starting from the third element, the value of each element is equal to the sum of the previous two elements. The first few elements of the sequence
How to implement the WeChat clone function on Huawei mobile phones
Mar 24, 2024 pm 06:03 PM
How to implement the WeChat clone function on Huawei mobile phones With the popularity of social software and people's increasing emphasis on privacy and security, the WeChat clone function has gradually become the focus of people's attention. The WeChat clone function can help users log in to multiple WeChat accounts on the same mobile phone at the same time, making it easier to manage and use. It is not difficult to implement the WeChat clone function on Huawei mobile phones. You only need to follow the following steps. Step 1: Make sure that the mobile phone system version and WeChat version meet the requirements. First, make sure that your Huawei mobile phone system version has been updated to the latest version, as well as the WeChat App.
Master how Golang enables game development possibilities
Mar 16, 2024 pm 12:57 PM
In today's software development field, Golang (Go language), as an efficient, concise and highly concurrency programming language, is increasingly favored by developers. Its rich standard library and efficient concurrency features make it a high-profile choice in the field of game development. This article will explore how to use Golang for game development and demonstrate its powerful possibilities through specific code examples. 1. Golang’s advantages in game development. As a statically typed language, Golang is used in building large-scale game systems.
PHP Game Requirements Implementation Guide
Mar 11, 2024 am 08:45 AM
PHP Game Requirements Implementation Guide With the popularity and development of the Internet, the web game market is becoming more and more popular. Many developers hope to use the PHP language to develop their own web games, and implementing game requirements is a key step. This article will introduce how to use PHP language to implement common game requirements and provide specific code examples. 1. Create game characters In web games, game characters are a very important element. We need to define the attributes of the game character, such as name, level, experience value, etc., and provide methods to operate these
Introduction to Golang reflection and analysis of application scenarios
Apr 03, 2024 pm 01:45 PM
The reflection feature in the Go language allows a program to inspect and modify the structure of a type at runtime. By using Type, Value and reflect.Kind, we can obtain the type information, field values and methods of the object, and we can also create and modify objects. Specific operation methods include: checking type (TypeOf()), obtaining field value (ValueOf(), FieldByName()), modifying field value (Set()), and creating object (New()).
