回到目录 对于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没来的及 实现 ）
///
/// SQL 操作 类型
///
protected enum SQLType
{
Insert,
Update,
Delete,
}
///
/// 构建Update语句串
///
///
///
///
private Tuple string , object []> CreateUpdateSQL (TEntity entity) where TEntity : class
{
if (entity == null )
throw new ArgumentException( " The database entity can not be null. " );
List string > pkList = GetPrimaryKey ().Select(i => i.Name).ToList();
Type entityType = entity.GetType();
var table = entityType.GetProperties().Where(i =>
! pkList.Contains(i.Name)
&& i.GetValue(entity, null ) != null
&& i.PropertyType != typeof (EntityState)
&& !(i.GetCustomAttributes( false ).Length > 0
&& i.GetCustomAttributes( false ).Where(j => j.GetType() == typeof (NavigationAttribute)) != null )
&& (i.PropertyType.IsValueType || i.PropertyType == typeof ( string )) // 过滤导航属性
).ToArray();
// 过滤主键，航行属性，状态属性等
if (pkList == null || pkList.Count == 0 )
throw new ArgumentException( " The Table entity have not a primary key. " );
List object > arguments = new List object > ();
StringBuilder builder = new StringBuilder();
foreach ( var change in table)
{
if (pkList.Contains(change.Name))
continue ;
if (arguments.Count != 0 )
builder.Append( " , " );
builder.Append(change.Name + " = { " + arguments.Count + " } " );
if (change.PropertyType == typeof ( string ) || change.PropertyType == typeof (DateTime))
arguments.Add( " ' " + change.GetValue(entity, null ).ToString().Replace( " ' " , " char(39) " ) + " ' " );
else
arguments.Add(change.GetValue(entity, null ));
}
if (builder.Length == 0 )
throw new Exception( " 没有任何属性进行更新 " );
builder.Insert( 0 , " UPDATE " + string .Format( " [{0}] " , entityType.Name) + " SET " );
builder.Append( " WHERE " );
bool firstPrimaryKey = true ;
foreach ( var primaryField in pkList)
{
if (firstPrimaryKey)
firstPrimaryKey = false ;
else
builder.Append( " AND " );
object val = entityType.GetProperty(primaryField).GetValue(entity, null );
builder.Append(GetEqualStatment(primaryField, arguments.Count));
arguments.Add(val);
}
return new Tuple string , object []> (builder.ToString(), arguments.ToArray());
}
///
/// 构建Delete语句串
///
///
///
///
private Tuple string , object []> CreateDeleteSQL (TEntity entity) where TEntity : class
{
if (entity == null )
throw new ArgumentException( " The database entity can not be null. " );
Type entityType = entity.GetType();
List string > pkList = GetPrimaryKey ().Select(i => i.Name).ToList();
if (pkList == null || pkList.Count == 0 )
throw new ArgumentException( " The Table entity have not a primary key. " );
List object > arguments = new List object > ();
StringBuilder builder = new StringBuilder();
builder.Append( " Delete from " + string .Format( " [{0}] " , entityType.Name));
builder.Append( " WHERE " );
bool firstPrimaryKey = true ;
foreach ( var primaryField in pkList)
{
if (firstPrimaryKey)
firstPrimaryKey = false ;
else
builder.Append( " AND " );
object val = entityType.GetProperty(primaryField).GetValue(entity, null );
builder.Append(GetEqualStatment(primaryField, arguments.Count));
arguments.Add(val);
}
return new Tuple string , object []> (builder.ToString(), arguments.ToArray());
}
///
/// 构建Insert语句串
/// 主键为自增时，如果主键值为0，我们将主键插入到SQL串中
///
///
///
///
private Tuple string , object []> CreateInsertSQL (TEntity entity) where TEntity : class
{
if (entity == null )
throw new ArgumentException( " The database entity can not be null. " );
Type entityType = entity.GetType();
var table = entityType.GetProperties().Where(i => i.PropertyType != typeof (EntityKey)
&& i.PropertyType != typeof (EntityState)
&& i.Name != " IsValid "
&& i.GetValue(entity, null ) != null
&& !(i.GetCustomAttributes( false ).Length > 0
&& i.GetCustomAttributes( false ).Where(j => j.GetType() == typeof (NavigationAttribute)) != null )
&& (i.PropertyType.IsValueType || i.PropertyType == typeof ( string ))).ToArray(); // 过滤主键，航行属性，状态属性等
List string > pkList = GetPrimaryKey ().Select(i => i.Name).ToList();
List object > arguments = new List object > ();
StringBuilder fieldbuilder = new StringBuilder();
StringBuilder valuebuilder = new StringBuilder();
fieldbuilder.Append( " INSERT INTO " + string .Format( " [{0}] " , entityType.Name) + " ( " );
foreach ( var member in table)
{
if (pkList.Contains(member.Name) && Convert.ToString(member.GetValue(entity, null )) == " 0 " )
continue ;
object value = member.GetValue(entity, null );
if (value != null )
{
if (arguments.Count != 0 )
{
fieldbuilder.Append( " , " );
valuebuilder.Append( " , " );
}
fieldbuilder.Append(member.Name);
if (member.PropertyType == typeof ( string ) || member.PropertyType == typeof (DateTime))
valuebuilder.Append( " '{ " + arguments.Count + " }' " );
else
valuebuilder.Append( " { " + arguments.Count + " } " );
if (value.GetType() == typeof ( string ))
value = value.ToString().Replace( " ' " , " char(39) " );
arguments.Add(value);
}
}
fieldbuilder.Append( " ) Values ( " );
fieldbuilder.Append(valuebuilder.ToString());
fieldbuilder.Append( " ); " );
return new Tuple string , object []> (fieldbuilder.ToString(), arguments.ToArray());
}
///
/// 执行ＳＱＬ，根据ＳＱＬ 操作 的类型
///
///
///
///
///
protected string DoSQL (IEnumerable list, SQLType sqlType) where TEntity : class
{
StringBuilder sqlstr = new StringBuilder();
switch (sqlType)
{
case SQLType.Insert:
list.ToList().ForEach(i =>
{
Tuple string , object []> sql = CreateInsertSQL(i);
sqlstr.AppendFormat(sql.Item1, sql.Item2);
});
break ;
case SQLType.Update:
list.ToList().ForEach(i =>
{
Tuple string , object []> sql = CreateUpdateSQL(i);
sqlstr.AppendFormat(sql.Item1, sql.Item2);
});
break ;
case SQLType.Delete:
list.ToList().ForEach(i =>
{
Tuple string , object []> sql = CreateDeleteSQL(i);
sqlstr.AppendFormat(sql.Item1, sql.Item2);
});
break ;
default :
throw new ArgumentException( " 请输入正确的参数 " );
}
return sqlstr.ToString();
}
前方永远都是通往成功的路，只要你相信，它就会更快的 实现 ...
回到目录
查看更多关于LINQ-to-SQL那点事~利用反射在LINQ-to-SQL环境中实现Ado.net的CU的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did158547