/// <summary>
/// 获取多实体集合
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
protected SqlMapper.GridReader GetMultiple(string sql, dynamic param = null, IDbTransaction transaction = null,
int?commandTimeout = null, CommandType?commandType = null)
{
DbConnObj ConnObj = GetConnObj(transaction);
return(ConnObj.DbConnection.QueryMultiple(sql, param as object, ConnObj.DbTransaction, commandTimeout, commandType));
}
protected DbConnObj GetConnObj(IDbTransaction transaction = null)
{
DbConnObj dbConnObj = new DbConnObj();
dbConnObj.DbConnection = this.DBSession.Connection;
if (null != transaction)
{
dbConnObj.DbConnection = transaction.Connection;
dbConnObj.DbTransaction = transaction;
}
else if (null != Transaction.Current)
{
dbConnObj.DbConnection = Transaction.Current.DbTransactionWrapper.DbTransaction.Connection;
dbConnObj.DbTransaction = Transaction.Current.DbTransactionWrapper.DbTransaction;
if (this.DBSession.Connection.GetType() != dbConnObj.DbConnection.GetType())
{
dbConnObj.DbConnection = this.DBSession.Connection;
dbConnObj.DbTransaction = null;
}
else
{
if (this.DBSession.Connection.Database != dbConnObj.DbConnection.Database)
{
dbConnObj.DbConnection = this.DBSession.Connection;
dbConnObj.DbTransaction = null;
}
}
}
return(dbConnObj);
}
/// <summary>
/// 查询列表数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="predicate"></param>
/// <param name="sort"></param>
/// <param name="buffered"></param>
/// <returns></returns>
public IEnumerable <TReturn> GetList <TReturn>(object predicate = null, IList <ISort> sort = null,
bool buffered = false) where TReturn : class
{
DbConnObj ConnObj = GetConnObj();
return(ConnObj.DbConnection.GetList <T, TReturn>(predicate, sort, null, null, buffered, dbType: this.DBSession.dbType));
}
/// <summary>
/// 插入单条记录
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public dynamic Insert(T entity, IDbTransaction transaction = null)
{
DbConnObj ConnObj = GetConnObj(transaction);
dynamic result = ConnObj.DbConnection.Insert <T>(entity, ConnObj.DbTransaction, dbType: this.DBSession.dbType);
return(result);
}
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="allRowsCount"></param>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="allRowsCountSql"></param>
/// <param name="allRowsCountParam"></param>
/// <param name="buffered"></param>
/// <returns></returns>
protected IEnumerable <TReturn> GetPage <TReturn>(int pageIndex, int pageSize, out long allRowsCount, string sql, dynamic param = null, string allRowsCountSql = null, dynamic allRowsCountParam = null, int?commandTimeout = null, CommandType?commandType = null) where TReturn : class
{
DbConnObj ConnObj = GetConnObj();
IEnumerable <TReturn> entityList = ConnObj.DbConnection.GetPage <TReturn>(pageIndex, pageSize, out allRowsCount, sql, param as object, allRowsCountSql, ConnObj.DbTransaction, commandTimeout, true, base.DBSession.dbType);
return(entityList);
}
/// <summary>
/// 获取全部数据集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public IEnumerable <T> GetAll(string tableName = null)
{
DbConnObj ConnObj = GetConnObj();
if (ConnObj.DbConnection != null)
{
if (string.IsNullOrWhiteSpace(tableName))
{
return(ConnObj.DbConnection.GetList <T>(transaction: ConnObj.DbTransaction, dbType: ConnObj.dbType));
}
else
{
return(ConnObj.DbConnection.GetList <T>(transaction: ConnObj.DbTransaction, dbType: ConnObj.dbType, tableName: tableName));
}
}
else
{
DataBaseType dbType;
using (IDbConnection conn = DBUtils.CreateDBConnection(ConnKey, out dbType)) {
if (string.IsNullOrWhiteSpace(tableName))
{
return(conn.GetList <T>(transaction: ConnObj.DbTransaction, dbType: dbType));
}
else
{
return(conn.GetList <T>(transaction: ConnObj.DbTransaction, dbType: dbType, tableName: tableName));
}
}
}
}
/// <summary>
/// 更新单条记录
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public bool Update(T entity, IDbTransaction transaction = null)
{
DbConnObj ConnObj = GetConnObj(transaction);
bool isOk = ConnObj.DbConnection.Update <T>(entity, ConnObj.DbTransaction, dbType: this.DBSession.dbType);
return(isOk);
}
/// <summary>
/// 根据表达式筛选
/// </summary>
/// <typeparam name="TFirst"></typeparam>
/// <typeparam name="TSecond"></typeparam>
/// <typeparam name="TReturn"></typeparam>
/// <param name="sql"></param>
/// <param name="map"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="buffered"></param>
/// <param name="splitOn"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
protected IEnumerable <TReturn> Get <TFirst, TSecond, TThird, TReturn>(string sql, Func <TFirst, TSecond, TThird, TReturn> map,
dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id",
int?commandTimeout = null, CommandType?commandType = null) where TReturn : class
{
DbConnObj ConnObj = GetConnObj(transaction);
return(ConnObj.DbConnection.Query(sql, map, param as object, ConnObj.DbTransaction, buffered, splitOn, commandTimeout, commandType));
}
/// <summary>
/// 分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="allRowsCount"></param>
/// <param name="predicate"></param>
/// <param name="sort"></param>
/// <param name="buffered"></param>
/// <returns></returns>
public IEnumerable <TReturn> GetPageList <TReturn>(int pageIndex, int pageSize, out long allRowsCount,
object predicate = null, IList <ISort> sort = null, bool buffered = true) where TReturn : class
{
DbConnObj ConnObj = GetConnObj();
IEnumerable <TReturn> entityList = ConnObj.DbConnection.GetPage <T, TReturn>(predicate, sort, pageIndex, pageSize, null, null, buffered, dbType: this.DBSession.dbType);
allRowsCount = ConnObj.DbConnection.Count <T>(predicate, dbType: this.DBSession.dbType);
return(entityList);
}
/// <summary>
/// 删除单条记录
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="primaryId"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public int Delete(dynamic primaryId, IDbTransaction transaction = null)
{
DbConnObj ConnObj = GetConnObj(transaction);
var entity = GetById(primaryId);
var obj = entity as T;
int isOk = ConnObj.DbConnection.Delete <T>(obj, dbType: this.DBSession.dbType);
return(isOk);
}
/// <summary>
/// 根据多个Id获取多个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ids"></param>
/// <returns></returns>
public IEnumerable <TReturn> GetByIds <TReturn>(IList <dynamic> ids) where TReturn : class
{
var tblName = string.Format("dbo.{0}", typeof(T).Name);
var idsin = string.Join(",", ids.ToArray <dynamic>());
var sql = "SELECT * FROM @table WHERE Id in (@ids)";
DbConnObj ConnObj = GetConnObj();
IEnumerable <TReturn> dataList = ConnObj.DbConnection.Query <TReturn>(sql, new { table = tblName, ids = idsin });
return(dataList);
}
public LambdaUpdateHelper <T> LambdaUpdate(IDbTransaction transaction = null, int?commandTimeout = null)
{
DbConnObj ConnObj = GetConnObj(transaction);
if (ConnObj.DbConnection != null)
{
return(ConnObj.DbConnection.LambdaUpdate <T>(transaction, commandTimeout));
}
else
{
return(DapperExtension.LambdaUpdate <T>(ConnKey, commandTimeout));
}
}
public LambdaQueryHelper <TEntity> LambdaQuery <TEntity>(IDbTransaction transaction = null, int?commandTimeout = null) where TEntity : class
{
DbConnObj ConnObj = GetConnObj(transaction);
if (ConnObj.DbConnection != null)
{
return(ConnObj.DbConnection.LambdaQuery <TEntity>(transaction, commandTimeout));
}
else
{
return(DapperExtension.LambdaQuery <TEntity>(ConnKey, commandTimeout));
}
}
/// <summary>
/// 根据Id获取实体
/// </summary>
/// <typeparam name="TReturn">返回的类型</typeparam>
/// <param name="primaryId">主键</param>
/// <returns></returns>
public TReturn GetById <TReturn>(dynamic primaryId) where TReturn : class
{
DbConnObj ConnObj = GetConnObj();
if (ConnObj.DbConnection != null)
{
return(ConnObj.DbConnection.Get <T, TReturn>(primaryId as object, ConnObj.DbTransaction, dbType: ConnObj.dbType));
}
else
{
DataBaseType dbType;
using (IDbConnection conn = DBUtils.CreateDBConnection(ConnKey, out dbType)) {
return(conn.Get <T, TReturn>(primaryId as object, ConnObj.DbTransaction, dbType: dbType));
}
}
}
/// <summary>
/// 获取全部数据集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public IEnumerable <TReturn> GetAll <TReturn>() where TReturn : class
{
DbConnObj ConnObj = GetConnObj();
if (ConnObj.DbConnection != null)
{
return(ConnObj.DbConnection.GetList <T, TReturn>(transaction: ConnObj.DbTransaction, dbType: ConnObj.dbType));
}
else
{
DataBaseType dbType;
using (IDbConnection conn = DBUtils.CreateDBConnection(ConnKey, out dbType)) {
return(conn.GetList <T, TReturn>(transaction: ConnObj.DbTransaction, dbType: dbType));
}
}
}
/// <summary>
/// 统计记录总数
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="predicate"></param>
/// <returns></returns>
public int Count(object predicate)
{
DbConnObj ConnObj = GetConnObj();
if (ConnObj.DbConnection != null)
{
return(ConnObj.DbConnection.Count <T>(predicate, transaction: ConnObj.DbTransaction, dbType: ConnObj.dbType));
}
else
{
DataBaseType dbType;
using (IDbConnection conn = DBUtils.CreateDBConnection(ConnKey, out dbType)) {
return(conn.Count <T>(predicate, transaction: ConnObj.DbTransaction, dbType: dbType));
}
}
}
/// <summary>
/// 执行sql操作
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
protected int Execute(string sql, dynamic param = null, IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null)
{
DbConnObj ConnObj = GetConnObj(transaction);
if (ConnObj.DbConnection != null)
{
return(ConnObj.DbConnection.Execute(sql, param as object, ConnObj.DbTransaction, commandTimeout, commandType));
}
else
{
DataBaseType dbType;
using (IDbConnection conn = DBUtils.CreateDBConnection(ConnKey, out dbType)) {
return(conn.Execute(sql, param as object, ConnObj.DbTransaction, commandTimeout, commandType));
}
}
}
/// <summary>
/// 查询列表数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="predicate"></param>
/// <param name="sort"></param>
/// <returns></returns>
public IEnumerable <TReturn> GetList <TReturn>(object predicate = null, IList <ISort> sort = null) where TReturn : class
{
DbConnObj ConnObj = GetConnObj();
if (ConnObj.DbConnection != null)
{
return(ConnObj.DbConnection.GetList <T, TReturn>(predicate, sort, ConnObj.DbTransaction, null, dbType: ConnObj.dbType));
}
else
{
DataBaseType dbType;
using (IDbConnection conn = DBUtils.CreateDBConnection(ConnKey, out dbType)) {
return(conn.GetList <T, TReturn>(predicate, sort, ConnObj.DbTransaction, null, dbType: dbType));
}
}
}
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="allRowsCount"></param>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="allRowsCountSql"></param>
/// <param name="allRowsCountParam"></param>
/// <returns></returns>
protected IEnumerable <T> GetPage(int pageIndex, int pageSize, out long allRowsCount, string sql, dynamic param = null, string allRowsCountSql = null, dynamic allRowsCountParam = null, int?commandTimeout = null, CommandType?commandType = null)
{
DbConnObj ConnObj = GetConnObj();
if (ConnObj.DbConnection != null)
{
return(ConnObj.DbConnection.GetPage <T>(pageIndex, pageSize, out allRowsCount, sql, param as object, allRowsCountSql, ConnObj.DbTransaction, commandTimeout, ConnObj.dbType));
}
else
{
DataBaseType dbType;
using (IDbConnection conn = DBUtils.CreateDBConnection(ConnKey, out dbType)) {
return(conn.GetPage <T>(pageIndex, pageSize, out allRowsCount, sql, param as object, allRowsCountSql, ConnObj.DbTransaction, commandTimeout, dbType));
}
}
}
/// <summary>
/// 更新单条记录
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public bool Update(T entity, IDbTransaction transaction = null)
{
DbConnObj ConnObj = GetConnObj(transaction);
if (ConnObj.DbConnection != null)
{
return(ConnObj.DbConnection.Update <T>(entity, ConnObj.DbTransaction, dbType: ConnObj.dbType));
}
else
{
DataBaseType dbType;
using (IDbConnection conn = DBUtils.CreateDBConnection(ConnKey, out dbType)) {
return(conn.Update <T>(entity, ConnObj.DbTransaction, dbType: dbType));
}
}
}
/// <summary>
/// 删除单条记录
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="predicate"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public int DeleteList(object predicate = null, IDbTransaction transaction = null)
{
DbConnObj ConnObj = GetConnObj(transaction);
if (ConnObj.DbConnection != null)
{
return(ConnObj.DbConnection.Delete <T>(predicate, ConnObj.DbTransaction, dbType: ConnObj.dbType));
}
else
{
DataBaseType dbType;
using (IDbConnection conn = DBUtils.CreateDBConnection(ConnKey, out dbType)) {
return(conn.Delete <T>(predicate, ConnObj.DbTransaction, dbType: dbType));
}
}
}
/// <summary>
/// 根据条件筛选出数据集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
protected IEnumerable <TReturn> Get <TReturn>(string sql, dynamic param = null, IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null) where TReturn : class
{
DbConnObj ConnObj = GetConnObj(transaction);
if (ConnObj.DbConnection != null)
{
return(ConnObj.DbConnection.Query <TReturn>(sql, param as object, ConnObj.DbTransaction, true, commandTimeout, commandType));
}
else
{
DataBaseType dbType;
using (IDbConnection conn = DBUtils.CreateDBConnection(ConnKey, out dbType)) {
return(conn.Query <TReturn>(sql, param as object, ConnObj.DbTransaction, true, commandTimeout, commandType));
}
}
}
/// <summary>
/// 根据表达式筛选
/// </summary>
/// <typeparam name="TFirst"></typeparam>
/// <typeparam name="TSecond"></typeparam>
/// <typeparam name="TReturn"></typeparam>
/// <param name="sql"></param>
/// <param name="map"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="splitOn"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
protected IEnumerable <T> Get <TFirst, TSecond, TThird>(string sql, Func <TFirst, TSecond, TThird, T> map,
dynamic param = null, IDbTransaction transaction = null, string splitOn = "Id",
int?commandTimeout = null, CommandType?commandType = null)
{
DbConnObj ConnObj = GetConnObj(transaction);
if (ConnObj.DbConnection != null)
{
return(ConnObj.DbConnection.Query(sql, map, param as object, ConnObj.DbTransaction, true, splitOn, commandTimeout, commandType));
}
else
{
DataBaseType dbType;
using (IDbConnection conn = DBUtils.CreateDBConnection(ConnKey, out dbType)) {
return(conn.Query(sql, map, param as object, ConnObj.DbTransaction, true, splitOn, commandTimeout, commandType));
}
}
}
/// <summary>
/// 删除单条记录
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="primaryId"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public int Delete(dynamic primaryId, IDbTransaction transaction = null)
{
DbConnObj ConnObj = GetConnObj(transaction);
if (ConnObj.DbConnection != null)
{
object entity = this.GetById(primaryId);
var obj = entity as T;
return(ConnObj.DbConnection.Delete <T>(obj, ConnObj.DbTransaction, dbType: ConnObj.dbType));
}
else
{
DataBaseType dbType;
using (IDbConnection conn = DBUtils.CreateDBConnection(ConnKey, out dbType)) {
object entity = conn.Get <T>(primaryId as object, ConnObj.DbTransaction, dbType: dbType);
var obj = entity as T;
return(conn.Delete <T>(obj, ConnObj.DbTransaction, dbType: dbType));
}
}
}
protected DbConnObj GetConnObj(IDbTransaction transaction = null)
{
DbConnObj dbConnObj = new DbConnObj();
if (null != transaction)
{
dbConnObj.DbConnection = transaction.Connection;
dbConnObj.DbTransaction = transaction;
}
else if (null != this.DBSession)
{
if (DBSession.Connection.ConnectionString == "")
{
DBSession = Helper.GetPerHttpRequestDBSession(ConnKey);
}
dbConnObj.DbConnection = this.DBSession.Connection;
dbConnObj.dbType = this.DBSession.dbType;
}
return(dbConnObj);
}
/// <summary>
/// 根据多个Id获取多个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ids"></param>
/// <returns></returns>
public IEnumerable <T> GetByIds(IList <dynamic> ids)
{
var tblName = string.Format("dbo.{0}", typeof(T).Name);
var idsin = string.Join(",", ids.ToArray <dynamic>());
var sql = "SELECT * FROM @table WHERE Id in (@ids)";
DbConnObj ConnObj = GetConnObj();
IEnumerable <T> dataList = new List <T>();
if (ConnObj.DbConnection != null)
{
dataList = ConnObj.DbConnection.Query <T>(sql, new { table = tblName, ids = idsin }, ConnObj.DbTransaction);
}
else
{
DataBaseType dbType;
using (IDbConnection conn = DBUtils.CreateDBConnection(ConnKey, out dbType)) {
dataList = conn.Query <T>(sql, new { table = tblName, ids = idsin });
}
}
return(dataList);
}
/// <summary>
/// 分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="allRowsCount"></param>
/// <param name="predicate"></param>
/// <param name="sort"></param>
/// <returns></returns>
public IEnumerable <TReturn> GetPageList <TReturn>(int pageIndex, int pageSize, out long allRowsCount,
object predicate = null, IList <ISort> sort = null) where TReturn : class
{
DbConnObj ConnObj = GetConnObj();
allRowsCount = 0;
IEnumerable <TReturn> entityList = new List <TReturn>();
if (ConnObj.DbConnection != null)
{
entityList = ConnObj.DbConnection.GetPage <T, TReturn>(predicate, sort, pageIndex, pageSize, ConnObj.DbTransaction, null, dbType: ConnObj.dbType);
allRowsCount = ConnObj.DbConnection.Count <T>(predicate, transaction: ConnObj.DbTransaction, dbType: ConnObj.dbType);
}
else
{
DataBaseType dbType;
using (IDbConnection conn = DBUtils.CreateDBConnection(ConnKey, out dbType)) {
entityList = conn.GetPage <T, TReturn>(predicate, sort, pageIndex, pageSize, ConnObj.DbTransaction, null, dbType: dbType);
allRowsCount = conn.Count <T>(predicate, transaction: ConnObj.DbTransaction, dbType: dbType);
}
}
return(entityList);
}
/// <summary>
/// 根据条件筛选出数据集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="buffered"></param>
/// <returns></returns>
protected IEnumerable <TReturn> Get <TReturn>(string sql, dynamic param = null, IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null) where TReturn : class
{
DbConnObj ConnObj = GetConnObj(transaction);
return(ConnObj.DbConnection.Query <TReturn>(sql, param as object, transaction != null ? transaction : ConnObj.DbTransaction, true, commandTimeout, commandType));
}
/// <summary>
/// 执行sql操作
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
protected int Execute(string sql, dynamic param = null, IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null)
{
DbConnObj ConnObj = GetConnObj(transaction);
return(ConnObj.DbConnection.Execute(sql, param as object, ConnObj.DbTransaction, commandTimeout, commandType));
}
/// <summary>
/// 根据Id获取实体
/// </summary>
/// <typeparam name="TReturn">返回的类型</typeparam>
/// <param name="primaryId">主键</param>
/// <returns></returns>
public TReturn GetById <TReturn>(dynamic primaryId) where TReturn : class
{
DbConnObj ConnObj = GetConnObj();
return(ConnObj.DbConnection.Get <T, TReturn>(primaryId as object, dbType: this.DBSession.dbType));
}
