对于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没来的及实现）
/// <summary>
/// SQL操作类型
/// </summary>
protected enum SQLType
{
Insert,
Update,
Delete,
}
/// <summary>
/// 构建Update语句串
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
private Tuple<string, object[]> CreateUpdateSQL<TEntity>(TEntity entity) where TEntity : class
{
if (entity == null)
throw new ArgumentException("The database entity can not be null.");
List<string> pkList = GetPrimaryKey<TEntity>().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());
}
/// <summary>
/// 构建Delete语句串
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
private Tuple<string, object[]> CreateDeleteSQL<TEntity>(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<TEntity>().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());
}
/// <summary>
/// 构建Insert语句串
/// 主键为自增时，如果主键值为0，我们将主键插入到SQL串中
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
private Tuple<string, object[]> CreateInsertSQL<TEntity>(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<TEntity>().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());
}
/// <summary>
/// 执行ＳＱＬ，根据ＳＱＬ操作的类型
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="list"></param>
/// <param name="sqlType"></param>
/// <returns></returns>
protected string DoSQL<TEntity>(IEnumerable<TEntity> 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();
}
前方永远都是通往成功的路，只要你相信，它就会更快的实现...
作者：仓储大叔，张占岭，
荣誉：微软MVP
