转载注明出处
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net.Cache;
using System.Text;
using Dapper;
using DapperExtensions;
using TestData.Entity;
using TestData.Business;
namespace TestData.Business
{
/// <summary>
/// 业务逻辑的基类 包含了一些简单的操作
/// </summary>
/// <typeparam name="T"></typeparam>
public abstract class BaseManager<T> where T : class, IDataEntity
{
/// <summary>
/// 查询所有
/// </summary>
/// <returns></returns>
public IEnumerable<T> GetAll()
{
using (var conn = ConnectionFactory.Connection)
{
return conn.GetList<T>();
}
}
/// <summary>
/// 根绝sql查询
/// </summary>
/// <param name="sql">sql</param>
/// <param name="parameters">参数列表</param>
/// <returns></returns>
public IEnumerable<T> SelectCommond(string sql, object parameters = null)
{
using (var conn = ConnectionFactory.Connection)
{
return conn.Query<T>(sql, parameters);
}
}
/// <summary>
/// 根据表明查询
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public IEnumerable<T> GetAll(string name)
{
using (var conn = ConnectionFactory.Connection)
{
return conn.Query<T>(string.Format("select * from {0}", name));
}
}
/// <summary>
/// 根据编号查询
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public T GetById(int? id)
{
if (id == null)
return default(T);
using (var conn = ConnectionFactory.Connection)
{
return conn.Get<T>(id.Value);
}
}
/// <summary>
/// 修改实体
/// </summary>
/// <param name="t">实体对象</param>
/// <returns></returns>
public bool Update(T t)
{
using (var conn = ConnectionFactory.Connection)
{
t.EditorDate = DateTime.Now;
return conn.Update(t);
}
}
/// <summary>
/// 得到数量
/// </summary>
/// <returns></returns>
public int GetCount()
{
using (var conn = ConnectionFactory.Connection)
{
return conn.Count<T>(null);
}
}
/// <summary>
/// 分页
/// </summary>
/// <param name="predicate"></param>
/// <param name="pageindex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public Tuple<int, IEnumerable<T>> GetPaged(object predicate, int pageindex, int pageSize)
{
using (var conn = ConnectionFactory.Connection)
{
var sort = new List<ISort>
{
Predicates.Sort<T>(p=>p.EditorDate)
};
var total = conn.Count<T>(predicate);
return new Tuple<int, IEnumerable<T>>(total, conn.GetPage<T>(predicate, sort, pageindex, pageSize).ToList());
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="t">实体对象</param>
/// <returns></returns>
public bool Insert(T t)
{
t.EditorDate = DateTime.Now;
return this.Add(t, false) == t.Id;
}
/// <summary>
/// 添加实体集合
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public bool Insert(List<T> list)
{
using (var conn = ConnectionFactory.Connection)
{
list.ForEach(p => p.EditorDate = DateTime.Now);
return conn.Insert(list);
}
}
/// <summary>
/// 添加实体
/// </summary>
/// <param name="t">实体对象</param>
/// <param name="isAutoGenId"></param>
/// <returns></returns>
public int Add(T t, bool isAutoGenId = true)
{
using (var conn = ConnectionFactory.Connection)
{
//var maxindex = conn.Query<int?>(string.Format("select max(indexs) from {0}", typeof(T).Name)).FirstOrDefault() ?? 0;
//t.Indexs = maxindex + 1;
return conn.Insert(t, isGenId: isAutoGenId);
}
}
/// <summary>
/// 根据编号删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool Delete(int? id)
{
var obj = this.GetById(id);
if (obj == null) return false;
return this.Update(obj);
}
/// <summary>
/// 根据编号修改
/// </summary>
/// <param name="id"></param>
/// <param name="mark"></param>
/// <returns></returns>
public bool UpdataStatus(int? id)
{
var obj = this.GetById(id);
if (obj == null) return false;
return this.Update(obj);
}
/// <summary>
/// 根据外键得到数据
/// </summary>
/// <param name="foreignKeyName">外键名称</param>
/// <param name="foreignKeyValue">外键的值</param>
/// <returns></returns>
public IEnumerable<T> GetByForeignKey(string foreignKeyName, Guid foreignKeyValue)
{
using (var conn = ConnectionFactory.Connection)
{
return conn.Query<T>(string.Format("select * from {0} where {1}=@value", typeof(T).Name, foreignKeyName), new { value = foreignKeyValue });
}
}
/// <summary>
/// 根据列查询
/// </summary>
/// <param name="fieldName">列名称</param>
/// <param name="fieldValue">列的值</param>
/// <returns></returns>
public IEnumerable<T> GetByField(string fieldName, dynamic fieldValue)
{
using (var conn = ConnectionFactory.Connection)
{
return conn.Query<T>(string.Format("select * from {0} where {1}=@value", typeof(T).Name, fieldName), new { value = fieldValue });
}
}
/// <summary>
/// lxh 根据某列查询的方法--带排序
/// </summary>
/// <param name="fieldName">查询列名</param>
/// <param name="fieldValue">条件内容</param>
/// <param name="sortFieldName">排序列名</param>
/// <returns></returns>
public IEnumerable<T> GetByField(string fieldName, dynamic fieldValue, string sortFieldName)
{
using (var conn = ConnectionFactory.Connection)
{
return conn.Query<T>(string.Format("select * from {0} where {1}=@value order by {2}", typeof(T).Name, fieldName, sortFieldName), new { value = fieldValue });
}
}
/// <summary>
/// lxh 获取排序号的方法
/// </summary>
/// <returns></returns>
public int GetNextSequence(T t)
{
using (var conn = ConnectionFactory.Connection)
{
return conn.Query<int>(string.Format("select isnull(max(Sequence),0)+1 from {0}", typeof(T).Name)).FirstOrDefault();
}
}
/// <summary>
/// 存储过程
/// </summary>
/// <param name="procName"></param>
/// <param name="obj"></param>
/// <returns></returns>
public List<dynamic> SelectProc(string procName, object obj = null)
{
using (var conn = ConnectionFactory.Connection)
{
return conn.Query(procName, obj, commandType: CommandType.StoredProcedure).ToList();
}
}
}
}
using CustomerInterface;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq.Expressions;
using System.Linq;
using Demo.Expressions;
using DapperExtensions;
using DapperExtensions.Mapper;
using DapperExtensions.Sql;
using Demo.Common;
namespace Demo.Demo
{
public class DapperRepository<T> : IDisposable,IRepository<T> where T : class
{
private IDbConnection _innerConn = null;
private IDbTransaction _innerTran = null;
private IDbConnection _refConn = null;
private IDbTransaction _refTran = null;
/// <summary>
/// 返回仓储类当前连接
/// </summary>
public IDbConnection Connection
{
get
{
if (_refConn != null)
{
return _refConn;
}
else
{
return _innerConn;
}
}
}
/// <summary>
/// 返回仓储类当前事务
/// </summary>
public IDbTransaction Transaction
{
get
{
if (_refTran != null)
{
return _refTran;
}
else
{
return _innerTran;
}
}
}
public DapperRepository()
{
_innerConn = DbConnectionFactory.CreateDbConnection();
_innerConn.Open();
_innerConn.Execute("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
}
public DapperRepository(IDbConnection conn,IDbTransaction trans=null)
{
if (conn == null)
{
throw new Exception("conn can not be null!");
}
if (trans != null)
{
if (trans.Connection != conn)
{
throw new Exception("trans'connection must be same as conn!");
}
}
_refConn = conn;
_refTran = trans;
}
public void BeginTrans()
{
_innerTran = this.Connection.BeginTransaction(IsolationLevel.ReadUncommitted);
}
public void Rollback()
{
if (Transaction != null)
{
this.Transaction.Rollback();
}
}
public void Commit()
{
if (this.Transaction != null)
{
this.Transaction.Commit();
}
}
/// <summary>
/// 添加数据
/// </summary>
/// <param name="entity">添加数据对象</param>
/// <returns>返回插入数据的主键</returns>
public dynamic Add(T entity)
{
return this.Connection.Insert<T>(entity,this.Transaction);
}
/// <summary>
/// 添加多组数据
/// </summary>
/// <param name="entitys">IEnumerable<T></param>
/// <returns></returns>
public List<dynamic> AddBatch(IEnumerable<T> entitys)
{
List<dynamic> retVal = new List<dynamic>();
foreach (T entity in entitys)
{
retVal.Add( Add(entity));
}
return retVal;
}
/// <summary>
/// 更新数据
/// </summary>
/// <param name="entity"></param>
/// <returns>bool</returns>
public bool Update(T entity)
{
return this.Connection.Update(entity,this.Transaction);
}
/// <summary>
/// 删除数据 根据对象删除
/// </summary>
/// <param name="entity"></param>
/// <returns>bool</returns>
public bool Delete(T entity)
{
return this.Connection.Delete(entity, this.Transaction);
}
/// <summary>
/// 删除数据 根据主键Id删除
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public bool Delete(object predicate=null)
{
return this.Connection.Delete(predicate, this.Transaction);
}
/// <summary>
/// 获取数据
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public T Get(object Id)
{
return this.Connection.Get<T>(Id,this.Transaction);
}
/// <summary>
/// 返回所有数据
/// </summary>
/// <returns></returns>
public IEnumerable<T> GetAll()
{
return this.Connection.GetList<T>(this.Transaction);
}
public IEnumerable<T> GetList(string sql, object parameters = null)
{
return this.Connection.Query<T>(sql, parameters,this.Transaction);
}
public int Execute(string sql, object parameters = null)
{
return this.Connection.Execute(sql, parameters, this.Transaction);
}
public IEnumerable<T> GetList(IPredicateGroup predGroup, List<ISort> sort)
{
IEnumerable<T> list = this.Connection.GetList<T>(predGroup, sort, this.Transaction);
return list;
}
public Tuple<int, IEnumerable<T>> GetPage(IPredicateGroup predicate, int pageindex, int pageSize,List<ISort> sort)
{
var multi = this.Connection.GetPage<T>(predicate, sort, pageindex, pageSize,this.Transaction);
var count = multi.Count();
var results = multi.ToList();
return new Tuple<int, IEnumerable<T>>(count, results);
}
public PagedDataTable GetPagedTable(IPredicateGroup predicate, int pageindex, int pageSize, IList<ISort> sort)
{
var totalCount=this.Connection.Count<T>(predicate,this.Transaction);
List<T> multi = this.Connection.GetPage<T>(predicate, sort, pageindex, pageSize, this.Transaction).ToList();
PagedDataTable retVal = new PagedDataTable() {
Data=IITDeductionDataType.Convert<T>(multi),
TotalCount = totalCount,
PageIndex=pageindex,
PageSize=pageSize
};
return retVal;
}
public long Count(IPredicateGroup predicate)
{
return this.Connection.Count<T>(predicate, this.Transaction);
}
public object ExecuteScalar(string query, object parameters = null)
{
return this.Connection.ExecuteScalar(query, parameters,this.Transaction);
}
/// <summary>
/// 多条件组合查询
/// </summary>
/// <param name="predGroup"></param>
/// <returns>IEnumerable<T></returns>
public IEnumerable<T> QueryByPredGroup(IPredicateGroup predGroup, List<ISort> sort)
{
IEnumerable<T> list = this.Connection.GetList<T>(predGroup, sort);
return list;
}
/// <summary>
/// 查询返回List<object>
/// </summary>
/// <typeparam name="TAny">自定义传输返回的Obect</typeparam>
/// <param name="query">querySql</param>
/// <param name="parameters">querySql参数</param>
/// <returns></returns>
public IEnumerable<TAny> Query<TAny>(string query, object parameters = null) where TAny : class
{
return Connection.Query<TAny>(query, parameters, Transaction);
}
/// <summary>
/// 通过Linq方式查询
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public T FirstOrDefault(Expression<Func<T, bool>> expression)
{
IPredicate ipredicate = expression.ToPredicateGroup();
var List = this.Connection.GetList<T>(ipredicate,null,this.Transaction).FirstOrDefault();
return List;
}
/// <summary>
/// 通过Linq获取LIST数据
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public IEnumerable<T> GetList(Expression<Func<T, bool>> expression)
{
IPredicate ipredicate = expression.ToPredicateGroup();
IEnumerable<T> list = this.Connection.GetList<T>(expression, null, this.Transaction);
return list;
}
public string AddPageQuery(string sql)
{
string querySql ="select * from("+sql
+ @")AS RowConstrainedResult
WHERE RowNum >= (@PageIndex * @PageSize + 1)
AND RowNum <= (@PageIndex + 1) * @PageSize
ORDER BY RowNum";
return querySql;
}
public void Dispose()
{
if (_innerTran != null)
{
_innerTran.Dispose();
_innerTran = null;
}
if (_innerConn != null)
{
_innerConn.Close();
_innerConn.Dispose();
_innerConn = null;
}
}
}
}
public interface IRepository<T> where T : class
{
IDbConnection Connection{get;}
IDbTransaction Transaction{get;}
dynamic Add(T entity);
List<dynamic> AddBatch(IEnumerable<T> entitys);
bool Update(T entity);
bool Delete(T entity);
bool Delete(object Id);
T Get(object Id);
IEnumerable<T> GetAll();
IEnumerable<T> GetList(string sql, object parameters = null);
int Execute(string sql, object parameters = null);
long Count(IPredicateGroup predicate);
object ExecuteScalar(string query, object parameters = null);
T FirstOrDefault(Expression<Func<T, bool>> expression);
IEnumerable<T> GetList(Expression<Func<T, bool>> expression);
IEnumerable<T> GetList(IPredicateGroup predGroup, List<ISort> sort);
IEnumerable<TAny> Query<TAny>(string query, object parameters = null) where TAny : class;
}
predGroup使用方法
public class DbConnectionFactory
{
private static readonly string connectionString;
private static readonly string databaseType;
static DbConnectionFactory()
{
connectionString = ConfigurationManager.AppSettings["Connection"];
databaseType = ConfigurationManager.AppSettings["Provider"];
}
public static IDbConnection CreateDbConnection()
{
IDbConnection connection = null;
switch (databaseType)
{
case "system.data.sqlclient":
connection = new System.Data.SqlClient.SqlConnection(connectionString);
break;
case "mysql":
//connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
break;
case "oracle":
//connection = new Oracle.DataAccess.Client.OracleConnection(connectionString);
//connection = new System.Data.OracleClient.OracleConnection(connectionString);
break;
case "db2":
connection = new System.Data.OleDb.OleDbConnection(connectionString);
break;
default:
connection = new System.Data.SqlClient.SqlConnection(connectionString);
break;
}
return connection;
}
}
public PagedDataTable GetDataList()
{
var pgMain = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
//选择筛选
var pgCheck = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
{
if (getDeclareInfoListDto.hireDateStart != null)
{
//pgCheck.Predicates.Add(Predicates.Field<employee>(p => p.hiredate, Operator.Ge, DateTime.Parse(getDeclareInfoListDto.hireDateStart)));
pgCheck.Predicates.Add(Predicates.Field<employee>(p => p.hiredate, Operator.Ge, getDeclareInfoListDto.hireDateStart));
}
if (getDeclareInfoListDto.hireDateEnd != null)
{
pgCheck.Predicates.Add(Predicates.Field<v_employee_page>(p => p.hiredate, Operator.Le, getDeclareInfoListDto.hireDateEnd));
}
if (getDeclareInfoListDto.quitDateStart != null)
{
pgCheck.Predicates.Add(Predicates.Field<v_employee_page>(p => p.quitdate, Operator.Ge, getDeclareInfoListDto.quitDateStart));
}
if (getDeclareInfoListDto.quitDataEnd != null)
{
pgCheck.Predicates.Add(Predicates.Field<v_employee_page>(p => p.quitdate, Operator.Le, getDeclareInfoListDto.quitDataEnd));
}
if (!string.IsNullOrWhiteSpace(getDeclareInfoListDto.department))
{
pgCheck.Predicates.Add(Predicates.Field<v_employee_page>(p => p.department, Operator.Eq, getDeclareInfoListDto.department));
}
if (!string.IsNullOrWhiteSpace(getDeclareInfoListDto.position))
{
pgCheck.Predicates.Add(Predicates.Field<v_employee_page>(p => p.position, Operator.Eq, getDeclareInfoListDto.position));
}
if (userLevel != 0)
{
pgCheck.Predicates.Add(Predicates.Field<v_employee_page>(p => p.assignto, Operator.Eq, getDeclareInfoListDto.userCode));
}
}
pgMain.Predicates.Add(pgCheck);
var pgStatus = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
pgStatus.Predicates.Add(Predicates.Field<v_employee_page>(p => p.status, Operator.Lt, 2));
pgMain.Predicates.Add(pgStatus);
//模糊筛选
var input = getDeclareInfoListDto.filterInput;
if (!string.IsNullOrWhiteSpace(input))
{
var pgInput = new PredicateGroup { Operator = GroupOperator.Or, Predicates = new List<IPredicate>() };
{
var str = string.Format("%{0}%", input);
pgInput.Predicates.Add(Predicates.Field<v_employee_page>(p => p.usercode, Operator.Like, str));
pgInput.Predicates.Add(Predicates.Field<v_employee_page>(p => p.empname, Operator.Like, str));
pgInput.Predicates.Add(Predicates.Field<v_employee_page>(p => p.mobilephone, Operator.Like, str));
pgInput.Predicates.Add(Predicates.Field<v_employee_page>(p => p.certnumber, Operator.Like, str));
pgInput.Predicates.Add(Predicates.Field<v_employee_page>(p => p.empcode, Operator.Like, str));
}
pgMain.Predicates.Add(pgInput);
}
IList<ISort> sort = new List<ISort>();
bool existSort = false;
if (!string.IsNullOrWhiteSpace(getDeclareInfoListDto.usercodeSort))
{
existSort = true;
bool reorder = false;
if (getDeclareInfoListDto.usercodeSort == "DESC")
{
reorder = true;
}
sort.Add(new Sort { PropertyName = "empcode", Ascending = reorder });
}
if (!string.IsNullOrWhiteSpace(getDeclareInfoListDto.departmentSort))
{
existSort = true;
bool reorder = false;
if (getDeclareInfoListDto.departmentSort == "DESC")
{
reorder = true;
}
sort.Add(new Sort { PropertyName = "department", Ascending = reorder });
}
if (!string.IsNullOrWhiteSpace(getDeclareInfoListDto.hireDateSort))
{
existSort = true;
bool reorder = false;
if (getDeclareInfoListDto.hireDateSort == "DESC")
{
reorder = true;
}
sort.Add(new Sort { PropertyName = "hiredate", Ascending = reorder });
}
if (!existSort)
{
sort.Add(new Sort { PropertyName = "lastmodifytime", Ascending = false });
}
IPredicateGroup predGroup = Predicates.Group(GroupOperator.And, pgMain);
retVal = _employeeDao.GetPagedTable(predGroup, getDeclareInfoListDto.page, getDeclareInfoListDto.limit, sort);
return retVal;
