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>

 

前方永远都是通往成功的路，只要你相信，它就会更快的实现...

回到目录