- using Domain;
- using System;
- using System.Collections;
- using System.Collections.Generic;
- using System.Data;
- using System.Data.Common;
- using System.Data.Entity;
- using System.Data.Entity.Core.Objects;
- using System.Data.Entity.Infrastructure;
- using System.Data.SqlClient;
- using System.Linq;
- using System.Linq.Expressions;
- using System.Text;
- using System.Threading.Tasks;
- namespace Service
- {
- public abstract classRepositoryBase : IRepository whereT :class
- {
- #region执行sql语句返回datatable/object/// <summary>
- /// 执行查询并返回首行首列的值
- /// </summary>
- /// <param name="sql"></param>
- /// <param name="parameters"></param>
- /// <returns></returns>
- public objectSqlQueryForScalar(string sql, DbParameter[] parameters)
- {
- stringconnStr =this.Context.Database.Connection.ConnectionString;
- using(SqlConnection conn =new System.Data.SqlClient.SqlConnection(connStr))
- {
- if(conn.State != ConnectionState.Open)
- {
- conn.Open();
- }
- SqlCommand cmd =new SqlCommand();
- cmd.Connection = conn;
- cmd.CommandText = sql;
- cmd.CommandType = CommandType.Text;
- if(parameters !=null&& parameters.Length >0)
- {
- foreach(varitemin parameters)
- {
- cmd.Parameters.Add(item);
- }
- }
- objectobj = cmd.ExecuteScalar();
- conn.Close();//连接需要关闭
- conn.Dispose();
- return obj;
- }
- }
- /// <summary>
- /// 执行查询并返回DataTable
- /// </summary>
- /// <param name="sql"></param>
- /// <param name="parameters"></param>
- /// <returns></returns>
- publicDataTable SqlQueryForDataTatable(string sql, DbParameter[] parameters)
- {
- stringconnStr =this.Context.Database.Connection.ConnectionString;
- using(SqlConnection conn =new System.Data.SqlClient.SqlConnection(connStr))
- {
- if(conn.State != ConnectionState.Open)
- {
- conn.Open();
- }
- SqlCommand cmd =new SqlCommand();
- cmd.Connection = conn;
- cmd.CommandText = sql;
- cmd.CommandType = CommandType.Text;
- if(parameters !=null&& parameters.Length >0)
- {
- foreach(varitemin parameters)
- {
- cmd.Parameters.Add(item);
- }
- }
- SqlDataAdapter adapter =new SqlDataAdapter(cmd);
- DataTable table =new DataTable();
- adapter.Fill(table);
- return table;
- }
- }
- #endregion
- #region获取单条记录/// <summary>
- /// 通过lambda表达式获取一条记录p=>p.id==id
- /// </summary>
- public virtualT Get(Expressionbool>> predicate)
- {
- try
- {
- return dbSet.AsNoTracking().SingleOrDefault(predicate);
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- #endregion
- #region单模型 CRUD 操作/// <summary>
- /// 增加一条记录
- /// </summary>
- /// <param name="entity">实体模型</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtual boolSave(T entity,boolIsCommit =true)
- {
- try
- {
- introw =0;
- varentry =this.Context.Entry(entity);
- entry.State = System.Data.Entity.EntityState.Added;
- if (IsCommit)
- {
- row = Context.SaveChanges();
- entry.State = System.Data.Entity.EntityState.Detached;
- }
- returnrow >0;
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 增加一条记录(异步方式)
- /// </summary>
- /// <param name="entity">实体模型</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtualTask<bool> SaveAsync(T entity,boolIsCommit =true)
- {
- try
- {
- introw =0;
- varentry =this.Context.Entry(entity);
- entry.State = System.Data.Entity.EntityState.Added;
- if (IsCommit)
- {
- row = Context.SaveChanges();
- entry.State = System.Data.Entity.EntityState.Detached;
- }
- returnTask.FromResult<bool>(row >0);
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 更新一条记录
- /// </summary>
- /// <param name="entity">实体模型</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtual boolUpdate(T entity,boolIsCommit =true)
- {
- try
- {
- introws =0;
- varentry =this.Context.Entry(entity);
- entry.State = System.Data.Entity.EntityState.Modified;
- if (IsCommit)
- {
- rows =this.Context.SaveChanges();
- entry.State = System.Data.Entity.EntityState.Detached;
- }
- returnrows >0;
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 更新一条记录(异步方式)
- /// </summary>
- /// <param name="entity">实体模型</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtualTask<bool> UpdateAsync(T entity,boolIsCommit =true)
- {
- try
- {
- introws =0;
- varentry =this.Context.Entry(entity);
- entry.State = System.Data.Entity.EntityState.Modified;
- if (IsCommit)
- {
- rows =this.Context.SaveChanges();
- entry.State = System.Data.Entity.EntityState.Detached;
- }
- returnTask.FromResult<bool>(rows >0);
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 增加或更新一条记录
- /// </summary>
- /// <param name="entity">实体模型</param>
- /// <param name="IsSave">是否增加</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtual boolSaveOrUpdate(T entity,boolIsSave,boolIsCommit =true)
- {
- try
- {
- returnIsSave Save(entity, IsCommit) : Update(entity, IsCommit);
- }
- catch(Exception e) {throw e; }
- }
- /// <summary>
- /// 增加或更新一条记录(异步方式)
- /// </summary>
- /// <param name="entity">实体模型</param>
- /// <param name="IsSave">是否增加</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtualTask<bool> SaveOrUpdateAsync(T entity,boolIsSave,boolIsCommit =true)
- {
- try
- {
- returnIsSave SaveAsync(entity, IsCommit) : UpdateAsync(entity, IsCommit);
- }
- catch(Exception e) {throw e; }
- }
- /// <summary>
- /// 更新字段
- /// </summary>
- /// <param name="table">表名</param>
- /// <param name="dic">被解析的字段</param>
- /// <param name="where">条件</param>
- /// <returns></returns>
- //public virtual bool Modify(string table, Dictionary<string, object> dic, string where)
- //{
- // try
- // {
- // string sql = "update "+table+" ";
- // return this.Context.Database.ExecuteSqlCommand(sql, para);
- // }
- // catch (Exception e)
- // {
- // throw e;
- // }
- //}
- #endregion
- #region多模型操作/// <summary>
- /// 增加多模型数据,指定独立模型集合
- /// </summary>
- public virtual intSaveList(List t) whereT1 :class
- {
- try
- {
- if(t ==null|| t.Count ==0)return 0;
- this.Context.Set().Local.Clear();
- foreach(varitemin t)
- {
- this.Context.Set().Add(item);
- }
- return this.Context.SaveChanges();
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 增加多模型数据,与当前模型一致
- /// </summary>
- public virtual intSaveList(List t)
- {
- try
- {
- this.dbSet.Local.Clear();
- foreach(varitemin t)
- {
- this.dbSet.Add(item);
- }
- return this.Context.SaveChanges();
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 增加多条记录,同一模型
- /// </summary>
- /// <param name="T1">实体模型集合</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtual boolSaveList(List T1, boolIsCommit =true)
- {
- try
- {
- if(T1 ==null|| T1.Count ==0)return false;
- this.Context.Set().Local.Clear();
- foreach(varitemin T1)
- {
- this.Context.Set().Add(item);
- }
- if (IsCommit)
- {
- return this.Context.SaveChanges() >0;
- }
- return false;
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 增加多条记录,同一模型(异步方式)
- /// </summary>
- /// <param name="T1">实体模型集合</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtualTask<bool> SaveListAsync(List T1, boolIsCommit =true)
- {
- try
- {
- if(T1 ==null|| T1.Count ==0)returnTask.FromResult<bool>(false);
- this.Context.Set().Local.Clear();
- foreach(varitemin T1)
- {
- this.Context.Set().Add(item);
- }
- if (IsCommit)
- {
- returnTask.FromResult<bool>(this.Context.SaveChanges() >0);
- }
- returnTask.FromResult<bool>(false);
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 增加多条记录,独立模型
- /// </summary>
- /// <param name="T1">实体模型集合</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtual boolSaveList(List T, boolIsCommit =true)whereT1 :class
- {
- try
- {
- if(T ==null|| T.Count ==0)return false;
- this.Context.Set().Local.Clear();
- foreach(varitemin T)
- {
- this.Context.Set().Add(item);
- }
- if (IsCommit)
- {
- return this.Context.SaveChanges() >0;
- }
- return false;
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 增加多条记录,独立模型(异步方式)
- /// </summary>
- /// <param name="T1">实体模型集合</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtualTask<bool> SaveListAsync(List T, boolIsCommit =true)whereT1 :class
- {
- try
- {
- if(T ==null|| T.Count ==0)returnTask.FromResult<bool>(false);
- this.Context.Set().Local.Clear();
- foreach(varitemin T)
- {
- this.Context.Set().Add(item);
- }
- if (IsCommit)
- {
- returnTask.FromResult<bool>(this.Context.SaveChanges() >0);
- }
- returnTask.FromResult<bool>(false);
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 更新多模型,指定独立模型集合
- /// </summary>
- public virtual intUpdateList(List t) whereT1 :class
- {
- if(t.Count <=0)return 0;
- try
- {
- foreach(varitemin t)
- {
- this.Context.Entry(item).State = System.Data.Entity.EntityState.Modified;
- }
- return this.Context.SaveChanges();
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 更新多模型,与当前模型一致
- /// </summary>
- public virtual intUpdateList(List t)
- {
- if(t.Count <=0)return 0;
- try
- {
- foreach(varitemin t)
- {
- this.Context.Entry(item).State = System.Data.Entity.EntityState.Modified;
- }
- return this.Context.SaveChanges();
- }
- catch(Exception e) {throw e; }
- }
- /// <summary>
- /// 更新多条记录,同一模型
- /// </summary>
- /// <param name="t">实体模型集合</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtual boolUpdateList(List t, boolIsCommit =true)
- {
- if(t.Count <=0)return false;
- try
- {
- foreach(varitemin t)
- {
- this.Context.Entry(item).State = System.Data.Entity.EntityState.Modified;
- }
- if (IsCommit)
- {
- return this.Context.SaveChanges() >0;
- }
- return false;
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 更新多条记录,同一模型(异步方式)
- /// </summary>
- /// <param name="t">实体模型集合</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtualTask<bool> UpdateListAsync(List t, boolIsCommit =true)
- {
- if(t.Count <=0)returnTask.FromResult<bool>(false);
- try
- {
- foreach(varitemin t)
- {
- this.Context.Entry(item).State = System.Data.Entity.EntityState.Modified;
- }
- if (IsCommit)
- {
- returnTask.FromResult<bool>(this.Context.SaveChanges() >0);
- }
- returnTask.FromResult<bool>(false);
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 更新多条记录,独立模型
- /// </summary>
- /// <param name="t">实体模型集合</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtual boolUpdateList(List t, boolIsCommit =true)whereT1 :class
- {
- if(t.Count <=0)return false;
- try
- {
- foreach(varitemin t)
- {
- this.Context.Entry(item).State = System.Data.Entity.EntityState.Modified;
- }
- if (IsCommit)
- {
- return this.Context.SaveChanges() >0;
- }
- return false;
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 更新多条记录,独立模型(异步方式)
- /// </summary>
- /// <param name="t">实体模型集合</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtualTask<bool> UpdateListAsync(List t, boolIsCommit =true)whereT1 :class
- {
- if(t.Count <=0)returnTask.FromResult<bool>(false);
- try
- {
- foreach(varitemin t)
- {
- this.Context.Entry(item).State = System.Data.Entity.EntityState.Modified;
- }
- if (IsCommit)
- {
- returnTask.FromResult<bool>(this.Context.SaveChanges() >0);
- }
- returnTask.FromResult<bool>(false);
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 批量删除数据,当前模型
- /// </summary>
- public virtual intDeleteList(List t)
- {
- if(t ==null|| t.Count ==0)return 0;
- foreach(varitemin t)
- {
- this.dbSet.Remove(item);
- }
- return this.Context.SaveChanges();
- }
- /// <summary>
- /// 批量删除数据,自定义模型
- /// </summary>
- public virtual intDeleteList(List t) whereT1 :class
- {
- try
- {
- if(t ==null|| t.Count ==0)return 0;
- foreach(varitemin t)
- {
- this.Context.Set().Remove(item);
- }
- return this.Context.SaveChanges();
- }
- catch(Exception e) {throw e; }
- }
- /// <summary>
- /// 删除多条记录,同一模型
- /// </summary>
- /// <param name="T1">实体模型集合</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtual boolDeleteList(List T1, boolIsCommit =true)
- {
- if(T1 ==null|| T1.Count ==0)return false;
- foreach(varitemin T1)
- {
- this.dbSet.Remove(item);
- }
- if (IsCommit)
- {
- return this.Context.SaveChanges() >0;
- }
- return false;
- }
- /// <summary>
- /// 删除多条记录,同一模型(异步方式)
- /// </summary>
- /// <param name="T1">实体模型集合</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtualTask<bool> DeleteListAsync(List T1, boolIsCommit =true)
- {
- if(T1 ==null|| T1.Count ==0)returnTask.FromResult<bool>(false);
- foreach(varitemin T1)
- {
- this.dbSet.Remove(item);
- }
- if (IsCommit)
- {
- returnTask.FromResult<bool>(this.Context.SaveChanges() >0);
- }
- returnTask.FromResult<bool>(false);
- }
- /// <summary>
- /// 删除多条记录,独立模型
- /// </summary>
- /// <param name="T1">实体模型集合</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtual boolDeleteList(List t, boolIsCommit =true)whereT1 :class
- {
- if(t ==null|| t.Count ==0)return false;
- foreach(varitemin t)
- {
- this.Context.Set().Remove(item);
- }
- if (IsCommit)
- {
- return this.Context.SaveChanges() >0;
- }
- return false;
- }
- /// <summary>
- /// 删除多条记录,独立模型(异步方式)
- /// </summary>
- /// <param name="T1">实体模型集合</param>
- /// <param name="IsCommit">是否提交(默认提交)</param>
- /// <returns></returns>
- public virtualTask<bool> DeleteListAsync(List T, boolIsCommit =true)whereT1 :class
- {
- if(T ==null|| T.Count ==0)returnTask.FromResult<bool>(false);
- foreach(varitemin T)
- {
- this.Context.Set().Remove(item);
- }
- if (IsCommit)
- {
- returnTask.FromResult<bool>(this.Context.SaveChanges() >0);
- }
- returnTask.FromResult<bool>(false);
- }
- /// <summary>
- /// 通过Lamda表达式,删除一条或多条记录
- /// </summary>
- /// <param name="predicate"></param>
- /// <param name="IsCommit"></param>
- /// <returns></returns>
- public virtual boolDelete(Expressionbool>> predicate,boolIsCommit =true)
- {
- vardata =this.dbSet.Where(predicate);
- foreach(varitemin data)
- {
- this.dbSet.Remove(item);
- }
- if (IsCommit)
- {
- return this.Context.SaveChanges() >0;
- }
- return false;
- }
- /// <summary>
- /// 通过Lamda表达式,删除一条或多条记录(异步方式)
- /// </summary>
- /// <param name="predicate"></param>
- /// <param name="IsCommit"></param>
- /// <returns></returns>
- public virtualTask<bool> DeleteAsync(Expressionbool>> predicate,boolIsCommit =true)
- {
- vardata =this.dbSet.Where(predicate);
- foreach(varitemin data)
- {
- this.dbSet.Remove(item);
- }
- if (IsCommit)
- {
- returnTask.FromResult<bool>(this.Context.SaveChanges() >0);
- }
- returnTask.FromResult<bool>(false);
- }
- #endregion
- #region固定公用帮助,含事务privateDbContext context =new MyConfig().db;
- /// <summary>
- /// 数据上下文--->根据Domain实体模型名称进行更改
- /// </summary>
- public DbContext Context
- {
- get
- {
- context.Configuration.ValidateOnSaveEnabled =false;
- return context;
- }
- }
- /// <summary>
- /// 数据上下文--->拓展属性
- /// </summary>
- public MyConfig Config
- {
- get
- {
- return new MyConfig();
- }
- }
- /// <summary>
- /// 公用泛型处理属性
- /// 注:所有泛型操作的基础
- /// </summary>
- publicDbSet dbSet
- {
- get{return this.Context.Set(); }
- }
- /// <summary>
- /// 事务
- /// </summary>
- privateDbContextTransaction _transaction =null;
- /// <summary>
- /// 开始事务
- /// </summary>
- public DbContextTransaction Transaction
- {
- get
- {
- if(this._transaction ==null)
- {
- this._transaction =this.Context.Database.BeginTransaction();
- }
- return this._transaction;
- }
- set{this._transaction = value; }
- }
- /// <summary>
- /// 事务状态
- /// </summary>
- public boolCommitted {get;set; }
- /// <summary>
- /// 异步锁定
- /// </summary>
- private readonly objectsync =new object();
- /// <summary>
- /// 提交事务
- /// </summary>
- public void Commit()
- {
- if(!Committed)
- {
- lock (sync)
- {
- if(this._transaction !=null)
- _transaction.Commit();
- }
- Committed =true;
- }
- }
- /// <summary>
- /// 回滚事务
- /// </summary>
- public void Rollback()
- {
- Committed =false;
- if(this._transaction !=null)
- this._transaction.Rollback();
- }
- #endregion
- #region增删改操作/// <summary>
- /// 添加一条模型记录,自动提交更改
- /// </summary>
- public virtual bool Save(T entity)
- {
- try
- {
- introw =0;
- varentry =this.Context.Entry(entity);
- entry.State = System.Data.Entity.EntityState.Added;
- row = Context.SaveChanges();
- entry.State = System.Data.Entity.EntityState.Detached;
- returnrow >0;
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 更新一条模型记录,自动提交更改
- /// </summary>
- public virtual bool Update(T entity)
- {
- try
- {
- introws =0;
- varentry =this.Context.Entry(entity);
- entry.State = System.Data.Entity.EntityState.Modified;
- rows =this.Context.SaveChanges();
- entry.State = System.Data.Entity.EntityState.Detached;
- returnrows >0;
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 更新模型记录,如不存在进行添加操作
- /// </summary>
- public virtual boolSaveOrUpdate(T entity,bool isEdit)
- {
- try
- {
- returnisEdit Update(entity) : Save(entity);
- }
- catch(Exception e) {throw e; }
- }
- /// <summary>
- /// 删除一条或多条模型记录,含事务
- /// </summary>
- public virtual intDelete(Expressionbool>> predicate =null)
- {
- try
- {
- introws =0;
- IQueryable entry = (predicate == null)this.dbSet.AsQueryable() :this.dbSet.Where(predicate);
- List list = entry.ToList();
- if(list.Count >0)
- {
- for(inti =0; i < list.Count; i++)
- {
- this.dbSet.Remove(list[i]);
- }
- rows =this.Context.SaveChanges();
- }
- return rows;
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 使用原始SQL语句,含事务处理
- /// </summary>
- public virtual intDeleteBySql(stringsql,params DbParameter[] para)
- {
- try
- {
- return this.Context.Database.ExecuteSqlCommand(sql, para);
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 执行SQL删除(异步方式)
- /// </summary>
- /// <param name="sql">SQL语句</param>
- /// <param name="para">Parameters参数</param>
- public virtualTask<int> DeleteBySqlAsync(stringsql,params DbParameter[] para)
- {
- returnTask.FromResult<int>(DeleteBySql(sql, para));
- }
- #endregion
- #region存储过程操作/// <summary>
- /// 执行返回影响行数的存储过程
- /// </summary>
- /// <param name="procname">过程名称</param>
- /// <param name="parameter">参数对象</param>
- /// <returns></returns>
- public virtual objectExecuteProc(stringprocname,params DbParameter[] parameter)
- {
- try
- {
- return ExecuteSqlCommand(procname, parameter);
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 执行返回结果集的存储过程
- /// </summary>
- /// <param name="procname">过程名称</param>
- /// <param name="parameter">参数对象</param>
- /// <returns></returns>
- public virtual objectExecuteQueryProc(stringprocname,params DbParameter[] parameter)
- {
- try
- {
- return Context.Database.SqlFunctionForDynamic(procname, parameter);
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- #endregion
- #region存在验证操作/// <summary>
- /// 验证当前条件是否存在相同项
- /// </summary>
- public virtual boolIsExist(Expressionbool>> predicate)
- {
- varentry =this.dbSet.Where(predicate);
- return (entry.Any());
- }
- /// <summary>
- /// 根据SQL验证实体对象是否存在
- /// </summary>
- public virtual boolIsExist(stringsql,params DbParameter[] para)
- {
- IEnumerable result =this.Context.Database.SqlQuery(typeof(int), sql, para);
- if(result.GetEnumerator().Current ==null|| result.GetEnumerator().Current.ToString() =="0")
- return false;
- return true;
- }
- /// <summary>
- /// 验证当前条件是否存在相同项(异步方式)
- /// </summary>
- public virtualTask<bool> IsExistAsync(Expressionbool>> predicate)
- {
- returnTask.FromResult<bool>(IsExist(predicate));
- }
- /// <summary>
- /// 根据SQL验证实体对象是否存在(异步方式)
- /// </summary>
- public virtualTask<bool> IsExistAsync(stringsql,params DbParameter[] para)
- {
- returnTask.FromResult<bool>(IsExist(sql, para));
- }
- #endregion
- #region获取多条数据操作/// <summary>
- /// 返回IQueryable集合,延时加载数据
- /// </summary>
- public virtualIQueryable LoadTop(Expressionbool>> predicate,int TopNum)
- {
- try
- {
- if(predicate !=null)
- {
- return this.dbSet.Where(predicate).Take(TopNum).AsNoTracking();
- }
- return this.dbSet.AsQueryable().AsNoTracking();
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 返回IQueryable集合,延时加载数据
- /// </summary>
- public virtualIQueryable LoadAll(Expressionbool>> predicate)
- {
- try
- {
- if(predicate !=null)
- {
- return this.dbSet.Where(predicate).AsNoTracking();
- }
- return this.dbSet.AsQueryable().AsNoTracking();
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 返回IQueryable集合,延时加载数据(异步方式)
- /// </summary>
- /// <param name="predicate"></param>
- /// <returns></returns>
- public virtualTask> LoadAllAsync(Expressionbool>> predicate)
- {
- try
- {
- returnTask.FromResult>(LoadAll(predicate));
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 返回DbQuery集合,延时加载数据
- /// </summary>
- public virtualDbQuery LoadQueryAll(Expressionbool>> predicate)
- {
- try
- {
- if(predicate !=null)
- {
- return this.dbSet.Where(predicate)asDbQuery;
- }
- return this.dbSet;
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 获取DbQuery的列表(异步方式)
- /// </summary>
- /// <param name="predicate"></param>
- /// <returns></returns>
- public virtualTask> LoadQueryAllAsync(Expressionbool>> predicate)
- {
- try
- {
- returnTask.FromResult>(LoadQueryAll(predicate));
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 返回List集合,不采用延时加载
- /// </summary>
- public virtualList LoadListAll(Expressionbool>> predicate)
- {
- try
- {
- if(predicate !=null)
- {
- return this.dbSet.Where(predicate).AsNoTracking().ToList();
- }
- return this.dbSet.AsQueryable().AsNoTracking().ToList();
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 返回List<T>集合,不采用延时加载(异步方式)
- /// </summary>
- /// <param name="predicate"></param>
- /// <returns></returns>
- public virtualTask> LoadListAllAsync(Expressionbool>> predicate)
- {
- try
- {
- returnTask.FromResult>(LoadListAll(predicate));
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 返回IEnumerable集合,采用原始T-Sql方式
- /// </summary>
- public virtualIEnumerable LoadEnumerableAll(stringsql,params DbParameter[] para)
- {
- try
- {
- return this.Context.Database.SqlQuery(sql, para);
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 获取IEnumerable列表(异步方式)
- /// </summary>
- /// <param name="sql">SQL语句</param>
- /// <param name="para">Parameters参数</param>
- /// <returns></returns>
- public virtualTask> LoadEnumerableAllAsync(stringsql,params DbParameter[] para)
- {
- try
- {
- returnTask.FromResult>(LoadEnumerableAll(sql, para));
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 返回IEnumerable动态集合,采用原始T-Sql方式
- /// </summary>
- public virtualSystem.Collections.IEnumerable LoadEnumerable(stringsql,params DbParameter[] para)
- {
- try
- {
- return this.Context.Database.SqlQueryForDynamic(sql, para);
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 获取数据动态集合(异步方式)
- /// </summary>
- /// <param name="sql">SQL语句</param>
- /// <param name="para">Parameters参数</param>
- /// <returns></returns>
- public virtualTask LoadEnumerableAsync(stringsql,params DbParameter[] para)
- {
- try
- {
- returnTask.FromResult(LoadEnumerable(sql, para));
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 返回IList集合,采用原始T-Sql方式
- /// </summary>
- public virtualList SelectBySql(stringsql,params DbParameter[] para)
- {
- try
- {
- return this.Context.Database.SqlQuery(typeof(T), sql, para).Cast().ToList();
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 采用SQL进行数据的查询,返回IList集合(异步方式)
- /// </summary>
- /// <param name="sql">SQL语句</param>
- /// <param name="para">Parameters参数</param>
- /// <returns></returns>
- public virtualTask> SelectBySqlAsync(stringsql,params DbParameter[] para)
- {
- try
- {
- returnTask.FromResult>(SelectBySql(sql, para));
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 采用SQL进行数据的查询,指定泛型,返回IList集合
- /// </summary>
- /// <typeparam name="T1"></typeparam>
- /// <param name="sql"></param>
- /// <param name="para"></param>
- /// <returns></returns>
- public virtualTask> SelectBySqlAsync(stringsql,params DbParameter[] para)
- {
- try
- {
- returnTask.FromResult>(SelectBySql(sql, para));
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 指定泛型,返回IList集合,采用原始T-Sql方式
- /// </summary>
- public virtualList SelectBySql(stringsql,params DbParameter[] para)
- {
- try
- {
- return this.Context.Database.SqlQuery(sql, para).ToList();
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 可指定返回结果、排序、查询条件的通用查询方法,返回实体对象
- /// </summary>
- /// <typeparam name="TEntity">实体对象</typeparam>
- /// <typeparam name="TOrderBy">排序字段类型</typeparam>
- /// <typeparam name="TResult">数据结果,与TEntity一致</typeparam>
- /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>
- /// <param name="orderby">排序字段</param>
- /// <param name="selector">返回结果(必须是模型中存在的字段)</param>
- /// <param name="IsAsc">排序方向,true为正序false为倒序</param>
- /// <returns>实体集合</returns>
- public virtualList QueryEntity
- (Expressionbool>>where,
- Expression> orderby,
- Expression> selector,
- bool IsAsc)
- whereTEntity :class
- whereTResult :class
- {
- IQueryable query = this.Context.Set();
- if(where!=null)
- {
- query = query.Where(where);
- }
- if(orderby!=null)
- {
- query = IsAsc ? query.OrderBy(orderby) : query.OrderByDescending(orderby);
- }
- if(selector ==null)
- {
- returnquery.Cast().AsNoTracking().ToList();
- }
- return query.Select(selector).AsNoTracking().ToList();
- }
- /// <summary>
- /// 可指定返回结果、排序、查询条件的通用查询方法,返回实体对象集合(异步方式)
- /// </summary>
- /// <typeparam name="TEntity">实体对象</typeparam>
- /// <typeparam name="TOrderBy">排序字段类型</typeparam>
- /// <typeparam name="TResult">数据结果,与TEntity一致</typeparam>
- /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>
- /// <param name="orderby">排序字段</param>
- /// <param name="selector">返回结果(必须是模型中存在的字段)</param>
- /// <param name="IsAsc">排序方向,true为正序false为倒序</param>
- /// <returns>实体集合</returns>
- public virtualTask> QueryEntityAsync(Expressionbool>>where, Expression> orderby, Expression> selector, bool IsAsc)
- whereTEntity :class
- whereTResult :class
- {
- returnTask.FromResult>(QueryEntity(where,orderby, selector, IsAsc));
- }
- /// <summary>
- /// 可指定返回结果、排序、查询条件的通用查询方法,返回Object对象
- /// </summary>
- /// <typeparam name="TEntity">实体对象</typeparam>
- /// <typeparam name="TOrderBy">排序字段类型</typeparam>
- /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>
- /// <param name="orderby">排序字段</param>
- /// <param name="selector">返回结果(必须是模型中存在的字段)</param>
- /// <param name="IsAsc">排序方向,true为正序false为倒序</param>
- /// <returns>自定义实体集合</returns>
- public virtualList<object> QueryObject
- (Expressionbool>>where,
- Expression> orderby,
- Func,
- List<object>> selector,
- bool IsAsc)
- whereTEntity :class
- {
- IQueryable query = this.Context.Set();
- if(where!=null)
- {
- query = query.Where(where);
- }
- if(orderby!=null)
- {
- query = IsAsc ? query.OrderBy(orderby) : query.OrderByDescending(orderby);
- }
- if(selector ==null)
- {
- returnquery.AsNoTracking().ToList<object>();
- }
- return selector(query);
- }
- /// <summary>
- /// 可指定返回结果、排序、查询条件的通用查询方法,返回Object对象集合(异步方式)
- /// </summary>
- /// <typeparam name="TEntity">实体对象</typeparam>
- /// <typeparam name="TOrderBy">排序字段类型</typeparam>
- /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>
- /// <param name="orderby">排序字段</param>
- /// <param name="selector">返回结果(必须是模型中存在的字段)</param>
- /// <param name="IsAsc">排序方向,true为正序false为倒序</param>
- /// <returns>自定义实体集合</returns>
- public virtualTaskobject>> QueryObjectAsync(Expressionbool>>where, Expression> orderby, Func, List<object>> selector,bool IsAsc)
- whereTEntity :class
- {
- returnTask.FromResultobject>>(QueryObject(where,orderby, selector, IsAsc));
- }
- /// <summary>
- /// 可指定返回结果、排序、查询条件的通用查询方法,返回动态类对象
- /// </summary>
- /// <typeparam name="TEntity">实体对象</typeparam>
- /// <typeparam name="TOrderBy">排序字段类型</typeparam>
- /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>
- /// <param name="orderby">排序字段</param>
- /// <param name="selector">返回结果(必须是模型中存在的字段)</param>
- /// <param name="IsAsc">排序方向,true为正序false为倒序</param>
- /// <returns>动态类</returns>
- public virtual dynamicQueryDynamic
- (Expressionbool>>where,
- Expression> orderby,
- Func,
- List<object>> selector,
- bool IsAsc)
- whereTEntity :class
- {
- List<object> list = QueryObject
- (where,orderby, selector, IsAsc);
- return Common.JsonConverter.JsonClass(list);
- }
- /// <summary>
- /// 可指定返回结果、排序、查询条件的通用查询方法,返回动态类对象集合(异步方式)
- /// </summary>
- /// <typeparam name="TEntity">实体对象</typeparam>
- /// <typeparam name="TOrderBy">排序字段类型</typeparam>
- /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>
- /// <param name="orderby">排序字段</param>
- /// <param name="selector">返回结果(必须是模型中存在的字段)</param>
- /// <param name="IsAsc">排序方向,true为正序false为倒序</param>
- /// <returns>动态类</returns>
- public virtualTask<dynamic> QueryDynamicAsync(Expressionbool>>where, Expression> orderby, Func, List<object>> selector,bool IsAsc)
- whereTEntity :class
- {
- returnTask.FromResult<dynamic>(QueryDynamic(where,orderby, selector, IsAsc));
- }
- #endregion
- #region分页操作/// <summary>
- /// 待自定义分页函数,使用必须重写,指定数据模型
- /// </summary>
- public virtualIList PageByListSql(stringsql, IList parameters, Common.PageCollection page)
- {
- return null;
- }
- /// <summary>
- /// 待自定义分页函数,使用必须重写,
- /// </summary>
- public virtualIList PageByListSql(stringsql, IList parameters, Common.PageCollection page)
- {
- return null;
- }
- /// <summary>
- /// 对IQueryable对象进行分页逻辑处理,过滤、查询项、排序对IQueryable操作
- /// </summary>
- /// <param name="t">Iqueryable</param>
- /// <param name="index">当前页</param>
- /// <param name="PageSize">每页显示多少条</param>
- /// <returns>当前IQueryable to List的对象</returns>
- public virtualCommon.PageInfo Query(IQueryable query, intindex,int PageSize)
- {
- if(index <1)
- {
- index =1;
- }
- if(PageSize <=0)
- {
- PageSize =20;
- }
- intcount = query.Count();
- intmaxpage = count / PageSize;
- if(count % PageSize >0)
- {
- maxpage++;
- }
- if(index > maxpage)
- {
- index = maxpage;
- }
- if(count >0)
- query = query.Skip((index -1) * PageSize).Take(PageSize);
- return newCommon.PageInfo(index, PageSize, count, query.ToList());
- }
- public virtualCommon.PageInfo<object> FindAll(intPageIndex,intPageSize, Expressionbool>> condition, String orderByExpression,bool IsDESC)
- whereTEntity :class
- {
- varproperty =typeof(TEntity).GetProperty(orderByExpression);
- varparameter = Expression.Parameter(typeof(TEntity),"p");
- varpropertyAccess = Expression.MakeMemberAccess(parameter, property);
- varorderByExp = Expression.Lambda(propertyAccess, parameter);
- IQueryable query = this.Context.Set();
- if(condition !=null)
- {
- query = query.Where(condition);
- }
- intcount = query.Count();
- stringmethodName = IsDESC"OrderByDescending":"OrderBy";
- MethodCallExpression resultExp = Expression.Call(typeof(Queryable), methodName,newType[] {typeof(TEntity), property.PropertyType }, query.Expression, Expression.Quote(orderByExp));
- query = query.Provider.CreateQuery(resultExp);
- return newCommon.PageInfo<object>(PageIndex, PageSize, count, query.ToList<object>());
- }
- /// <summary>
- /// 通用EF分页,默认显示20条记录
- /// </summary>
- /// <typeparam name="TEntity">实体模型</typeparam>
- /// <typeparam name="TOrderBy">排序类型</typeparam>
- /// <param name="index">当前页</param>
- /// <param name="pageSize">显示条数</param>
- /// <param name="where">过滤条件</param>
- /// <param name="orderby">排序字段</param>
- /// <param name="selector">结果集合</param>
- /// <param name="isAsc">排序方向true正序 false倒序</param>
- /// <returns>自定义实体集合</returns>
- public virtualCommon.PageInfo<object> Query
- (intindex,int pageSize,
- Expressionbool>>where,
- Expression> orderby,
- Func, List<object>> selector,
- bool isAsc)
- whereTEntity :class
- {
- if(index <1)
- {
- index =1;
- }
- if(pageSize <=0)
- {
- pageSize =20;
- }
- IQueryable query = this.Context.Set();
- if(where!=null)
- {
- query = query.Where(where);
- }
- intcount = query.Count();
- intmaxpage = count / pageSize;
- if(count % pageSize >0)
- {
- maxpage++;
- }
- if(index > maxpage)
- {
- index = maxpage;
- }
- if(orderby!=null)
- {
- query = isAsc ? query.OrderBy(orderby) : query.OrderByDescending(orderby);
- }
- if(count >0)
- query = query.Skip((index -1) * pageSize).Take(pageSize);
- //返回结果为null,返回所有字段
- if(selector ==null)
- return newCommon.PageInfo<object>(index, pageSize, count, query.ToList<object>());
- return newCommon.PageInfo<object>(index, pageSize, count, selector(query).ToList());
- }
- /// <summary>
- /// 普通SQL查询分页方法
- /// </summary>
- /// <param name="index">当前页</param>
- /// <param name="pageSize">显示行数</param>
- /// <param name="tableName">表名/视图</param>
- /// <param name="field">获取项</param>
- /// <param name="filter">过滤条件</param>
- /// <param name="orderby">排序字段+排序方向</param>
- /// <param name="group">分组字段</param>
- /// <returns>结果集</returns>
- public virtualCommon.PageInfo Query(intindex,intpageSize,stringtableName,stringfield,stringfilter,string orderby,stringgroup,params DbParameter[] para)
- {
- //执行分页算法
- if(index <=0)
- index =1;
- intstart = (index -1) * pageSize;
- if(start >0)
- start -=1;
- else
- start =0;
- intend = index * pageSize;
- #region查询逻辑stringlogicSql ="SELECT";
- //查询项
- if(!string.IsNullOrEmpty(field))
- {
- logicSql +=" "+ field;
- }
- else
- {
- logicSql +=" *";
- }
- logicSql +=" FROM ("+ tableName +" ) where";
- //过滤条件
- if(!string.IsNullOrEmpty(filter))
- {
- logicSql +=" "+ filter;
- }
- else
- {
- filter =" 1=1";
- logicSql +=" 1=1";
- }
- //分组
- if(!string.IsNullOrEmpty(group))
- {
- logicSql +=" group by "+ group;
- }
- #endregion
- //获取当前条件下数据总条数
- intcount =this.Context.Database.SqlQuery(typeof(int),"select count(*) from ("+ tableName +") where "+ filter, para).Cast<int>().FirstOrDefault();
- stringsql ="SELECT T.* FROM ( SELECT B.* FROM ( SELECT A.*,ROW_NUMBER() OVER(ORDER BY getdate()) as RN"+
- logicSql +") A ) B WHERE B.RN<="+ end +") T WHERE T.RN>"+ start;
- //排序
- if(!string.IsNullOrEmpty(orderby))
- {
- sql +=" order by "+orderby;
- }
- varlist = ExecuteSqlQuery(sql, para)as IEnumerable;
- if(list !=null)
- return newCommon.PageInfo(index, pageSize, count, list.Cast<object>().ToList());
- return newCommon.PageInfo(index, pageSize, count,new { });
- }
- /// <summary>
- /// 最简单的SQL分页
- /// </summary>
- /// <param name="index">页码</param>
- /// <param name="pageSize">显示行数</param>
- /// <param name="sql">纯SQL语句</param>
- /// <param name="orderby">排序字段与方向</param>
- /// <returns></returns>
- public virtualCommon.PageInfo Query(intindex,intpageSize,stringsql,string orderby,params DbParameter[] para)
- {
- return this.Query(index, pageSize, sql,null,null,orderby,null, para);
- }
- /// <summary>
- /// 多表联合分页算法
- /// </summary>
- public virtualCommon.PageInfo Query(IQueryable query,intindex,int PageSize)
- {
- varenumerable = (queryasSystem.Collections.IEnumerable).Cast<object>();
- if(index <1)
- {
- index =1;
- }
- if(PageSize <=0)
- {
- PageSize =20;
- }
- intcount = enumerable.Count();
- intmaxpage = count / PageSize;
- if(count % PageSize >0)
- {
- maxpage++;
- }
- if(index > maxpage)
- {
- index = maxpage;
- }
- if(count >0)
- enumerable = enumerable.Skip((index -1) * PageSize).Take(PageSize);
- return new Common.PageInfo(index, PageSize, count, enumerable.ToList());
- }
- #endregion
- #regionADO.NET增删改查方法/// <summary>
- /// 执行增删改方法,含事务处理
- /// </summary>
- public virtual objectExecuteSqlCommand(stringsql,params DbParameter[] para)
- {
- try
- {
- return this.Context.Database.ExecuteSqlCommand(sql, para);
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- /// <summary>
- /// 执行多条SQL,增删改方法,含事务处理
- /// </summary>
- public virtual objectExecuteSqlCommand(Dictionary<string,object> sqllist)
- {
- try
- {
- introws =0;
- IEnumeratorstring,object>> enumerator = sqllist.GetEnumerator();
- using (Transaction)
- {
- while (enumerator.MoveNext())
- {
- rows +=this.Context.Database.ExecuteSqlCommand(enumerator.Current.Key, enumerator.Current.Value);
- }
- Commit();
- }
- return rows;
- }
- catch (Exception e)
- {
- Rollback();
- throw e;
- }
- }
- /// <summary>
- /// 执行查询方法,返回动态类,接收使用var,遍历时使用dynamic类型
- /// </summary>
- public virtual objectExecuteSqlQuery(stringsql,params DbParameter[] para)
- {
- try
- {
- return this.Context.Database.SqlQueryForDynamic(sql, para);
- }
- catch (Exception e)
- {
- throw e;
- }
- }
- #endregion
- }
- }
来源: http://www.bubuko.com/infodetail-2131433.html