Rumah > Artikel > pangkalan data > 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> 执行SQL,根据SQL<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>
前方永远都是通往成功的路,只要你相信,它就会更快的实现...
回到目录