您的当前位置:首页正文

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没来的及实现)

 /// 
 /// SQL操作类型
 /// 
 protected enum SQLType
 {
 Insert,
 Update,
 Delete,
 }

 /// 
 /// 构建Update语句串
 /// 
 /// 
 /// 
 /// 
 private Tuple CreateUpdateSQL(TEntity entity) where TEntity : class
 {
 if (entity == null)
 throw new ArgumentException("The database entity can not be null.");
 List 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 arguments = new List();
 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(builder.ToString(), arguments.ToArray());

 }

 /// 
 /// 构建Delete语句串
 /// 
 /// 
 /// 
 /// 
 private Tuple CreateDeleteSQL(TEntity entity) where TEntity : class
 {
 if (entity == null)
 throw new ArgumentException("The database entity can not be null.");

 Type entityType = entity.GetType();
 List 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 arguments = new List();
 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(builder.ToString(), arguments.ToArray());
 }

 /// 
 /// 构建Insert语句串
 /// 主键为自增时,如果主键值为0,我们将主键插入到SQL串中
 /// 
 /// 
 /// 
 /// 
 private Tuple 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 pkList = GetPrimaryKey().Select(i => i.Name).ToList();
 List arguments = new List();
 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(fieldbuilder.ToString(), arguments.ToArray());
 }
 
 /// 
 /// 执行SQL,根据SQL操作的类型
 /// 
 /// 
 /// 
 /// 
 /// 
 protected string DoSQL(IEnumerable list, SQLType sqlType) where TEntity : class
 {
 StringBuilder sqlstr = new StringBuilder();
 switch (sqlType)
 {
 case SQLType.Insert:
 list.ToList().ForEach(i =>
 {
 Tuple sql = CreateInsertSQL(i);
 sqlstr.AppendFormat(sql.Item1, sql.Item2);
 });
 break;
 case SQLType.Update:
 list.ToList().ForEach(i =>
 {
 Tuple sql = CreateUpdateSQL(i);
 sqlstr.AppendFormat(sql.Item1, sql.Item2);
 });
 break;
 case SQLType.Delete:
 list.ToList().ForEach(i =>
 {
 Tuple sql = CreateDeleteSQL(i);
 sqlstr.AppendFormat(sql.Item1, sql.Item2);
 });
 break;
 default:
 throw new ArgumentException("请输入正确的参数");
 }
 return sqlstr.ToString();
 }

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

回到目录

本文如未解决您的问题请添加抖音号:51dongshi(抖音搜索懂视),直接咨询即可。

热门图文

Top