using System;
|
using System.Collections.Generic;
|
using System.Data.Common;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
using Dapper;
|
using HH.WMS.Entitys;
|
using System.Data;
|
|
namespace HH.WMS.DAL
|
{
|
|
public class DapperBaseDAL : BaseDAL
|
{
|
|
#region 执行查询-返回动态列List
|
/// <summary>
|
/// 查询-返回动态列List
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="where"></param>
|
/// <returns></returns>
|
public List<dynamic> ExecuteDynamicList(string sql, object param = null)
|
{
|
using (DbConnection conn = this.DataAccess.CreateConnection())
|
{
|
return conn.Query(sql, param).ToList();
|
}
|
}
|
#endregion
|
|
#region 执行查询-返回datatable
|
/// <summary>
|
/// 执行查询-返回datatable
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="param"></param>
|
/// <returns></returns>
|
public DataTable ExecuteDataTable(string sql, object param = null)
|
{
|
using (DbConnection conn = this.DataAccess.CreateConnection())
|
{
|
DataTable dt = new DataTable();
|
dt.Load(conn.ExecuteReader(sql, param));
|
return dt;
|
}
|
}
|
#endregion
|
|
#region 执行查询-返回单个实体
|
/// <summary>
|
/// 执行查询-返回单个实体
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="param"></param>
|
/// <returns></returns>
|
public T ExecuteSingleEntity<T>(string sql, object param = null)
|
{
|
using (DbConnection conn = this.DataAccess.CreateConnection())
|
{
|
return conn.QueryFirstOrDefault<T>(sql, param);
|
}
|
}
|
#endregion
|
|
|
|
#region 执行查询-返回动态列单个实体
|
/// <summary>
|
/// 执行查询-返回动态列单个实体
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="where"></param>
|
/// <returns></returns>
|
public dynamic ExecuteSingleDynamic(string sql, object param = null)
|
{
|
using (DbConnection conn = this.DataAccess.CreateConnection())
|
{
|
return conn.QueryFirstOrDefault(sql, param);
|
}
|
}
|
#endregion
|
|
#region 执行查询-返回List
|
/// <summary>
|
/// 执行查询-返回List
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="where"></param>
|
/// <returns></returns>
|
public List<T> ExecuteQuery<T>(string sql, object param = null)
|
{
|
using (DbConnection conn = this.DataAccess.CreateConnection())
|
{
|
return conn.Query<T>(sql, param).ToList();
|
}
|
}
|
#endregion
|
|
#region 分页-datable
|
/// <summary>
|
/// 分页-datable
|
/// </summary>
|
/// <param name="tableName">多表格式(select * from tn_wm_out_mst) t where 1=1 </param>
|
/// <param name="pageIndex"></param>
|
/// <param name="pageSize"></param>
|
/// <param name="total"></param>
|
/// <param name="orderBy"></param>
|
/// <param name="param">注入参数</param>
|
/// <returns></returns>
|
public OperateResult ExecutePagingResult(string tableName, int pageIndex, int pageSize, string orderBy = "", object param = null)
|
{
|
try
|
{
|
long total;
|
DataTable dt = ExecutePagingData(tableName, pageIndex, pageSize, out total, orderBy, param);
|
return OperateResult.Succeed(null, new
|
{
|
rows = dt,
|
total = total
|
});
|
}
|
catch (Exception ex)
|
{
|
return OperateResult.Error(ex.Message);
|
}
|
}
|
#endregion
|
|
#region 分页返回数据
|
/// <summary>
|
/// 分页返回数据
|
/// </summary>
|
/// <param name="tableName"></param>
|
/// <param name="pageIndex"></param>
|
/// <param name="pageSize"></param>
|
/// <param name="total"></param>
|
/// <param name="orderBy"></param>
|
/// <param name="param"></param>
|
/// <returns></returns>
|
public DataTable ExecutePagingData(string tableName, int pageIndex, int pageSize, out long total, string orderBy = "", object param = null)
|
{
|
string countSql = "SELECT COUNT(1) FROM " + tableName;
|
string pagingSql = string.Empty;
|
switch (NowDbType)
|
{
|
case DBTYPE.MSSQL:
|
if (string.IsNullOrEmpty(orderBy))
|
orderBy = "ORDER BY CN_T_MODIFY DESC";
|
pagingSql = @"SELECT * FROM (SELECT ROW_NUMBER() OVER({0}) AS ROWID,* FROM {1} ) AS SP
|
WHERE ROWID BETWEEN STR(({2}-1)*{3}+1) AND STR({4}*{5})";
|
pagingSql = string.Format(pagingSql, orderBy, tableName, pageIndex, pageSize, pageIndex, pageSize);
|
break;
|
case DBTYPE.MYSQL:
|
pagingSql = @"SELECT T.* FROM (SELECT * FROM {0} {3}) T LIMIT {1},{2} ";
|
pagingSql = string.Format(pagingSql, tableName, pageIndex, pageSize, orderBy);
|
break;
|
}
|
total = ExecuteCountSql(countSql, param);
|
HH.WMS.Common.Log.Info("DapperBaseDAL", countSql);
|
return ExecuteDataTable(pagingSql, param);
|
}
|
#endregion
|
|
public DataTable ExecuteSqlDataTable(string tableName, object param = null)
|
{
|
string sql = @"SELECT * FROM " + tableName;
|
return ExecuteDataTable(sql, param);
|
}
|
|
#region 执行sql，返回数量
|
/// <summary>
|
/// 执行sql，返回数量
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="param"></param>
|
/// <returns></returns>
|
public long ExecuteCountSql(string sql, object param = null)
|
{
|
try
|
{
|
using (DbConnection conn = this.DataAccess.CreateConnection())
|
{
|
object obj = conn.ExecuteScalar(sql, param);
|
return Convert.ToUInt32(obj);
|
}
|
}
|
catch
|
{
|
return 0;
|
}
|
}
|
#endregion
|
|
#region 执行sql 事务，单条
|
/// <summary>
|
/// 执行sql 事务，单条
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="entity"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult ExecuteTranSql(string sql, object param, IDbTransaction trans)
|
{
|
if (trans == null)
|
{
|
return UseTransaction(tran =>
|
{
|
return tran.Connection.Execute(sql, param, tran);
|
});
|
}
|
else
|
{
|
try
|
{
|
int affectedRows = trans.Connection.Execute(sql, param, trans);
|
return OperateResult.Succeed(affectedRows);
|
}
|
catch (Exception exception)
|
{
|
throw exception;
|
}
|
}
|
}
|
#endregion
|
|
#region 执行sql 事务，多条
|
/// <summary>
|
/// 执行sql 事务，多条
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="list"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult ExecuteTranSql<T>(string sql, IList<T> list, IDbTransaction trans = null)
|
{
|
if (trans == null)
|
{
|
return UseTransaction(tran =>
|
{
|
foreach (var entity in list)
|
{
|
tran.Connection.Execute(sql, entity, tran);
|
}
|
});
|
}
|
else
|
{
|
try
|
{
|
foreach (var entity in list)
|
{
|
trans.Connection.Execute(sql, entity, trans);
|
}
|
return OperateResult.Succeed();
|
}
|
catch (Exception exception)
|
{
|
throw exception;
|
}
|
}
|
}
|
#endregion
|
|
#region 使用事务
|
/// <summary>
|
/// 使用事务
|
/// </summary>
|
/// <param name="action"></param>
|
/// <returns></returns>
|
public OperateResult UseTransaction(Action<IDbTransaction> action)
|
{
|
using (DbConnection conn = this.DataAccess.CreateConnection())
|
{
|
conn.Open();
|
using (var tran = conn.BeginTransaction(IsolationLevel.ReadCommitted))
|
{
|
try
|
{
|
action(tran);
|
tran.Commit();
|
return OperateResult.Succeed();
|
}
|
catch (Exception exception)
|
{
|
tran.Rollback();
|
return OperateResult.Error(exception.Message);
|
}
|
finally
|
{
|
tran.Dispose();
|
}
|
}
|
}
|
}
|
|
/// <summary>
|
/// 带返回值事务（用于单条事务返回影响行数）
|
/// </summary>
|
/// <param name="action"></param>
|
/// <returns></returns>
|
public OperateResult UseTransaction(Func<IDbTransaction, int> action)
|
{
|
using (DbConnection conn = this.DataAccess.CreateConnection())
|
{
|
conn.Open();
|
using (var tran = conn.BeginTransaction(IsolationLevel.ReadCommitted))
|
{
|
try
|
{
|
int affectedRows = action(tran);
|
tran.Commit();
|
return OperateResult.Succeed(affectedRows);
|
}
|
catch (Exception exception)
|
{
|
tran.Rollback();
|
return OperateResult.Error(exception.Message);
|
}
|
finally
|
{
|
tran.Dispose();
|
}
|
}
|
}
|
}
|
#endregion
|
|
#region 过滤查询条件
|
/// <summary>
|
/// 过滤查询条件
|
/// </summary>
|
/// <param name="field"></param>
|
/// <param name="value"></param>
|
/// <param name="opt"></param>
|
/// <returns></returns>
|
public string GetFilterField(string field, object value, string opt = "=")
|
{
|
string _value = WMS.Common.Util.ToStringInput(value);
|
if (string.IsNullOrEmpty(_value))
|
return string.Empty;
|
string result = string.Empty;
|
field = " AND " + field + " ";
|
switch (opt)
|
{
|
case "=":
|
result = field + opt + "'" + _value + "' ";
|
break;
|
case "like":
|
result = field + opt + " '%" + _value + "%' ";
|
break;
|
case "in":
|
result = field + opt + " ('" + _value.Replace(",", "','") + "') ";
|
break;
|
case "dateArray":
|
string[] date = new string[2];
|
date = value as string[];
|
if (date.Length == 2)
|
{
|
string startDate = date[0];
|
if (!string.IsNullOrEmpty(startDate))
|
{
|
result += (field + ">= '" + startDate + "' ");
|
}
|
string endDate = date[1];
|
if (!string.IsNullOrEmpty(endDate))
|
{
|
result += (field + "< '" + Convert.ToDateTime(endDate).AddDays(1).ToString() + "' ");
|
}
|
}
|
break;
|
default: return string.Empty;
|
}
|
return result;
|
}
|
#endregion
|
}
|
}
|
