using HH.WMS.Entitys;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using System.Linq;
|
using System.Reflection;
|
using System.Text;
|
using System.Threading.Tasks;
|
using Dapper;
|
using HH.WMS.Common;
|
using HH.WMS.Entitys.Common;
|
using HH.WMS.Common.External;
|
using Newtonsoft.Json;
|
|
namespace HH.WMS.DAL
|
{
|
public class ErpDapperDAL<T> : ErpDapperBaseDAL where T : new()
|
{
|
#region 表名
|
/// <summary>
|
/// 表名
|
/// </summary>
|
private string TableName
|
{
|
get
|
{
|
var attribute = typeof(T).GetCustomAttributes(typeof(TableAttribute), false).FirstOrDefault();
|
string tableName = string.Empty;
|
if (attribute != null)
|
{
|
tableName = ((TableAttribute)attribute).Table;
|
}
|
return tableName;
|
}
|
}
|
#endregion
|
|
#region 新增sql语句
|
/// <summary>
|
/// 新增sql语句
|
/// </summary>
|
/// <returns></returns>
|
public string AddSql()
|
{
|
PropertyInfo[] fields = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
|
StringBuilder sqlBuilder = new StringBuilder("INSERT INTO " + TableName);
|
string fieldsSql = string.Empty;
|
string _fieldsSql = string.Empty;
|
foreach (var f in fields)
|
{
|
var columnData = f.GetCustomAttribute(typeof(ColumnAttribute));
|
if (columnData == null) continue;
|
fieldsSql += string.Format("{0},", f.Name);
|
_fieldsSql += string.Format("@{0},", f.Name);
|
}
|
sqlBuilder.AppendLine("(");
|
sqlBuilder.AppendLine(fieldsSql.Trim(','));
|
sqlBuilder.AppendLine(")VALUES(");
|
sqlBuilder.AppendLine(_fieldsSql.Trim(','));
|
sqlBuilder.AppendLine(")");
|
return sqlBuilder.ToString();
|
}
|
#endregion
|
|
#region 修改sql语句，存在条件给实体赋值条件
|
/// <summary>
|
/// 修改sql语句，存在条件给实体赋值条件
|
/// </summary>
|
/// <param name="entity"></param>
|
/// <param name="where"></param>
|
/// <returns></returns>
|
public string UpdateSql(T entity, object where)
|
{
|
PropertyInfo[] fields = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
|
StringBuilder sqlBuilder = new StringBuilder("UPDATE " + TableName + " SET ");
|
string updateSql = string.Empty;
|
foreach (var f in fields)
|
{
|
var columnData = f.GetCustomAttribute(typeof(ColumnAttribute));
|
if (columnData == null) continue;
|
updateSql += string.Format("{0}=@{1},", f.Name, f.Name);
|
}
|
sqlBuilder.AppendLine(updateSql.Trim(',')).AppendLine("WHERE 1=1 ");
|
string whereSql = string.Empty;
|
if (where != null)
|
{
|
PropertyInfo[] whereFields = where.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
|
foreach (var f in whereFields)
|
{
|
whereSql += string.Format(" AND {0}=@{1} ", f.Name, f.Name);
|
var _f = fields.FirstOrDefault(p => p.Name.Equals(f.Name));
|
if (_f != null)
|
_f.SetValue(entity, f.GetValue(where, null));
|
}
|
}
|
sqlBuilder.AppendLine(whereSql.Trim(','));
|
return sqlBuilder.ToString();
|
}
|
#endregion
|
|
#region 修改sql语句，动态修改
|
/// <summary>
|
/// 修改sql语句，动态修改
|
/// </summary>
|
/// <param name="entity"></param>
|
/// <param name="where"></param>
|
/// <param name="mapEntity"></param>
|
/// <returns></returns>
|
public string UpdateSql(object entity, object where, T mapEntity)
|
{
|
PropertyInfo[] fields = entity.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
|
PropertyInfo[] entityFields = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
|
StringBuilder sqlBuilder = new StringBuilder("UPDATE " + TableName + " SET ");
|
string updateSql = string.Empty;
|
foreach (var f in fields)
|
{
|
if (entity.GetType() == typeof(T))
|
{
|
var columnData = f.GetCustomAttribute(typeof(ColumnAttribute));
|
if (columnData == null) continue;
|
}
|
updateSql += string.Format("{0}=@{1},", f.Name, f.Name);
|
//将更新字段给mapEntity 赋值
|
var _f = entityFields.FirstOrDefault(p => p.Name.Equals(f.Name));
|
if (_f != null)
|
_f.SetValue(mapEntity, f.GetValue(entity, null));
|
}
|
sqlBuilder.AppendLine(updateSql.Trim(',')).AppendLine(" WHERE 1=1 ");
|
string whereSql = string.Empty;
|
if (where != null)
|
{
|
PropertyInfo[] whereFields = where.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
|
foreach (var f in whereFields)
|
{
|
whereSql += string.Format(" AND {0}=@{1} ", f.Name, f.Name);
|
//将条件字段给mapEntity 赋值
|
var _f = entityFields.FirstOrDefault(p => p.Name.Equals(f.Name));
|
if (_f != null)
|
_f.SetValue(mapEntity, f.GetValue(where, null));
|
}
|
}
|
sqlBuilder.AppendLine(whereSql.Trim(','));
|
return sqlBuilder.ToString();
|
}
|
#endregion
|
|
#region 删除 sql语句
|
/// <summary>
|
/// 删除 sql语句
|
/// </summary>
|
/// <param name="param"></param>
|
/// <returns></returns>
|
public string DeleteSql(object param)
|
{
|
StringBuilder sqlBuilder = new StringBuilder("DELETE FROM " + TableName + " WHERE 1=1 ");
|
PropertyInfo[] fields = param.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
|
string whereSql = string.Empty;
|
foreach (var f in fields)
|
{
|
whereSql += string.Format(" AND {0}=@{1} ", f.Name, f.Name);
|
}
|
sqlBuilder.AppendLine(whereSql.Trim(','));
|
return sqlBuilder.ToString();
|
}
|
#endregion
|
|
//#region 查询sql语句
|
///// <summary>
|
///// 查询sql语句
|
///// </summary>
|
///// <param name="where"></param>
|
///// <returns></returns>
|
//public string QuerySql(object where = null, bool filterEmpty = true)
|
//{
|
// var sqlBuilder = new StringBuilder("SELECT * FROM " + TableName + " WHERE 1=1 ");
|
// if (where != null)
|
// {
|
// PropertyInfo[] whereFields = where.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
|
// foreach (var f in whereFields)
|
// {
|
// Type t = f.PropertyType;
|
// if (t == typeof(List<string>))
|
// {
|
// List<string> list = (List<string>)f.GetValue(where, null);
|
// if (list == null || !list.Any()) continue;
|
// sqlBuilder.AppendLine(string.Format(" AND {0} IN @{1} ", f.Name, f.Name));
|
// //list.ForEach(p =>
|
// //{
|
// // sqlBuilder.AppendLine(string.Format(" AND {0} IN @{1} ", f.Name, f.Name));
|
// //});
|
// }
|
// else
|
// {
|
// //}
|
// //if (t == typeof(string) ||
|
// // t == typeof(Object) ||
|
// // t == typeof(bool))
|
// //{
|
// string currentValue = Util.ToString(f.GetValue(where, null));
|
// if (string.IsNullOrEmpty(currentValue) && filterEmpty) continue;
|
// if (currentValue.Trim('%') != currentValue)
|
// {
|
// currentValue = Util.ToStringInput(currentValue);
|
|
// sqlBuilder.AppendLine(string.Format(" AND {0} LIKE '{1}' ", f.Name, currentValue));
|
// }
|
// else
|
// {
|
// sqlBuilder.AppendLine(string.Format(" AND {0}=@{1} ", f.Name, f.Name));
|
// }
|
// }
|
|
// }
|
// }
|
// return sqlBuilder.ToString();
|
//}
|
//#endregion
|
|
#region 查询sql语句
|
/// <summary>
|
/// 查询sql语句
|
/// </summary>
|
/// <param name="where"></param>
|
/// <returns></returns>
|
public string QuerySql(object where = null, bool filterEmpty = true, int top = 0)
|
{
|
var sqlBuilder = new StringBuilder();
|
if (top > 0)
|
{
|
sqlBuilder.Append("SELECT TOP " + top + " * FROM " + TableName + " WHERE 1=1 ");
|
}
|
else
|
{
|
sqlBuilder.Append("SELECT * FROM " + TableName + " WHERE 1=1 ");
|
}
|
if (where != null)
|
{
|
PropertyInfo[] whereFields = where.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
|
foreach (var f in whereFields)
|
{
|
Type t = f.PropertyType;
|
if (t == typeof(List<string>))
|
{
|
List<string> list = (List<string>)f.GetValue(where, null);
|
if (list == null || !list.Any()) continue;
|
sqlBuilder.AppendLine(string.Format(" AND {0} IN @{1} ", f.Name, f.Name));
|
}
|
else
|
{
|
string currentValue = Util.ToString(f.GetValue(where, null));
|
if (string.IsNullOrEmpty(currentValue) && filterEmpty) continue;
|
if (currentValue.Trim('%') != currentValue)
|
{
|
currentValue = Util.ToStringInput(currentValue);
|
|
sqlBuilder.AppendLine(string.Format(" AND {0} LIKE '{1}' ", f.Name, currentValue));
|
}
|
else
|
{
|
sqlBuilder.AppendLine(string.Format(" AND {0}=@{1} ", f.Name, f.Name));
|
}
|
}
|
|
}
|
}
|
return sqlBuilder.ToString();
|
}
|
|
public string QuerySql(List<HH.WMS.Entitys.Common.SearchWhere> where, bool isParamQuery)
|
{
|
string sql = "SELECT * FROM " + TableName;
|
StringBuilder sb = new StringBuilder();
|
foreach (HH.WMS.Entitys.Common.SearchWhere m in where)
|
{
|
if (m.value == null || string.IsNullOrEmpty(m.value.ToString()))
|
continue;
|
Type t = m.value.GetType();
|
switch (m.sign)
|
{
|
case Entitys.Common.WhereSign.Eq:
|
if (sb.Length > 0)
|
sb.Append(" and ");
|
if (t == typeof(string))
|
sb.Append(m.key + "=" + (isParamQuery ? "@" + m.key : "'" + m.value + "'"));
|
else if (t == typeof(int) || t == typeof(decimal))
|
sb.Append(m.key + "=" + (isParamQuery ? "@" + m.key : m.value));
|
break;
|
case Entitys.Common.WhereSign.TEq:
|
if (sb.Length > 0)
|
sb.Append(" and ");
|
sb.Append("CONVERT(date," + m.key + ")= " + (isParamQuery ? "@" + m.key : "CONVERT(date,'" + m.value + "')"));
|
break;
|
case Entitys.Common.WhereSign.TRange:
|
string[] dates = (string[])m.value;
|
if (!string.IsNullOrEmpty(dates[0]))
|
{
|
if (sb.Length > 0)
|
sb.Append(" and ");
|
sb.Append(" CONVERT(date," + m.key + ") >= " + (isParamQuery ? "@START" : "CONVERT(date,'" + dates[0] + "')"));
|
}
|
if (!string.IsNullOrEmpty(dates[1]))
|
{
|
if (sb.Length > 0)
|
sb.Append(" and ");
|
sb.Append(" CONVERT(date," + m.key + ") <= " + (isParamQuery ? "@END" : "CONVERT(date,'" + dates[1] + "')"));
|
}
|
break;
|
case Entitys.Common.WhereSign.Like:
|
if (sb.Length > 0)
|
sb.Append(" and ");
|
sb.Append(m.key + " like " + (isParamQuery ? "@" + m.key : "'%" + m.value + "%'"));
|
break;
|
case Entitys.Common.WhereSign.Gt:
|
if (sb.Length > 0)
|
sb.Append(" and ");
|
sb.Append(m.key + " > " + (isParamQuery ? "@" + m.key : m.value));
|
break;
|
case Entitys.Common.WhereSign.Lt:
|
if (sb.Length > 0)
|
sb.Append(" and ");
|
sb.Append(m.key + " < " + (isParamQuery ? "@" + m.key : m.value));
|
break;
|
case Entitys.Common.WhereSign.Include:
|
List<string> list = (List<string>)m.value;
|
if (list == null || !list.Any())
|
continue;
|
if (sb.Length > 0)
|
sb.Append(" and ");
|
if (isParamQuery)
|
{
|
sb.Append(string.Format(" {0} IN @{1} ", m.key, m.key));
|
}
|
else
|
{
|
sb.Append(string.Format(" {0} IN ('{1}') ", m.key, string.Join("','", list)));
|
}
|
break;
|
//List<string> list = (List<string>)t.GetValue(where, null);
|
//sqlBuilder.AppendLine(string.Format(" AND {0} IN @{1} ", f.Name, f.Name));
|
//list.ForEach(p =>
|
//{
|
// sqlBuilder.AppendLine(string.Format(" AND {0} IN @{1} ", f.Name, f.Name));
|
//});
|
}
|
}
|
if (sb.Length > 0)
|
return sql += " where " + sb.ToString();
|
return sql;
|
}
|
#endregion
|
|
#region 获取top条
|
/// <summary>
|
/// 获取top条
|
/// </summary>
|
/// <param name="top"></param>
|
/// <param name="where"></param>
|
/// <param name="orderBy"></param>
|
/// <returns></returns>
|
public List<T> GetTopList(int top, object where = null, string orderBy = "")
|
{
|
return ExecuteQuery<T>(QuerySql(where, true, top) + orderBy, where);
|
}
|
#endregion
|
|
#region 获取数量sql
|
/// <summary>
|
/// 获取数量sql
|
/// </summary>
|
/// <param name="where"></param>
|
/// <returns></returns>
|
public string CountSql(object where = null)
|
{
|
var sqlBuilder = new StringBuilder("SELECT COUNT(1) FROM " + TableName + " WHERE 1=1 ");
|
if (where != null)
|
{
|
PropertyInfo[] whereFields = where.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
|
foreach (var f in whereFields)
|
{
|
sqlBuilder.AppendLine(string.Format("AND {0}=@{1}", f.Name, f.Name));
|
}
|
}
|
return sqlBuilder.ToString();
|
}
|
#endregion
|
|
#region 新增
|
/// <summary>
|
/// 新增
|
/// </summary>
|
/// <param name="entity"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult Add(T entity, IDbTransaction trans = null)
|
{
|
return ExecuteTranSql(AddSql(), entity, trans);
|
}
|
|
public OperateResult Add(T entity, LogPara logPara, IDbTransaction trans = null)
|
{
|
var sqlStr = AddSql();
|
Log.Detail(logPara, "AddSql(): " + sqlStr);
|
return ExecuteTranSql(sqlStr, entity, trans);
|
}
|
#endregion
|
|
#region 批量新增
|
/// <summary>
|
/// 批量新增
|
/// </summary>
|
/// <param name="list"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult AddRange(List<T> list, IDbTransaction trans = null)
|
{
|
return ExecuteTranSql(AddSql(), list, trans);
|
}
|
#endregion
|
|
#region 修改
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="entity"></param>
|
/// <param name="where"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult UpdateAll(T entity, object where, IDbTransaction trans = null)
|
{
|
return ExecuteTranSql(UpdateSql(entity, where), entity, trans);
|
}
|
#endregion
|
|
#region 获取数量
|
/// <summary>
|
/// 获取数量
|
/// </summary>
|
/// <param name="where"></param>
|
/// <returns></returns>
|
public long Count(object where = null)
|
{
|
return ExecuteCountSql(CountSql(where), where);
|
}
|
#endregion
|
|
#region 单表分页
|
/// <summary>
|
/// 单表分页
|
/// </summary>
|
/// <param name="where"></param>
|
/// <param name="pageIndex"></param>
|
/// <param name="pageSize"></param>
|
/// <param name="orderBy"></param>
|
/// <returns></returns>
|
public OperateResult GetPagingResult(int pageIndex, int pageSize, object where = null, string orderBy = "")
|
{
|
string tableName = "(" + QuerySql(where) + ") t";
|
return ExecutePagingResult(tableName, pageIndex, pageSize, orderBy, where);
|
}
|
public OperateResult GetPagingResult(List<SearchWhere> whereColumn, int pageIndex, int pageSize, bool isParamQuery, object where = null, string orderBy = "")
|
{
|
string tableName = "(" + QuerySql(whereColumn, isParamQuery) + ") t ";
|
return ExecutePagingResult(tableName, pageIndex, pageSize, orderBy, where);
|
}
|
#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 GetPagingData(int pageIndex, int pageSize, out long total, object where = null, string orderBy = "")
|
{
|
string tableName = "(" + QuerySql(where) + ") t";
|
return ExecutePagingData(tableName, pageIndex, pageSize, out total, orderBy, where);
|
}
|
#endregion
|
|
public DataTable GetDataTable(object where = null, string orderBy = "")
|
{
|
string tableName = "(" + QuerySql(where) + " " + orderBy + ") t";
|
return ExecuteSqlDataTable(tableName, where);
|
}
|
|
#region 删除
|
/// <summary>
|
/// 删除
|
/// </summary>
|
/// <param name="param"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult Delete(object param, IDbTransaction trans = null)
|
{
|
return ExecuteTranSql(DeleteSql(param), param, trans);
|
}
|
#endregion
|
|
#region 动态修改单表
|
/// <summary>
|
/// 动态修改
|
/// </summary>
|
/// <param name="updateFields"></param>
|
/// <param name="where"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult Update(object updateFields, object where, IDbTransaction trans = null)
|
{
|
T t = new T();
|
string updateSql = UpdateSql(updateFields, where, t);
|
return ExecuteTranSql(updateSql, t, trans);
|
}
|
#endregion
|
|
#region 批量修改
|
/// <summary>
|
/// 批量修改
|
/// </summary>
|
/// <param name="list"></param>
|
/// <param name="where"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult UpdateRange(IList<T> list, object where, IDbTransaction trans = null)
|
{
|
string updateSql = string.Empty;
|
foreach (var entity in list)
|
{
|
updateSql = UpdateSql(entity, where);
|
}
|
return ExecuteTranSql(updateSql, list, trans);
|
}
|
#endregion
|
|
#region 查询-返回List
|
/// <summary>
|
/// 查询-返回List
|
/// </summary>
|
/// <param name="where"></param>
|
/// <returns></returns>
|
public List<T> GetList(object where = null, string orderBy = "")
|
{
|
return ExecuteQuery<T>(QuerySql(where) + orderBy, where);
|
}
|
#endregion
|
|
#region 查询-返回DataTable
|
/// <summary>
|
/// 查询-返回DataTable
|
/// </summary>
|
/// <param name="where"></param>
|
/// <returns></returns>
|
public DataTable GetDataTable(object where = null)
|
{
|
return ExecuteDataTable(QuerySql(where), where);
|
}
|
#endregion
|
|
#region 查询-返回entity
|
/// <summary>
|
/// 查询-返回entity
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="where"></param>
|
/// <returns></returns>
|
public T GetSingleEntity(object where = null, string orderBy = "")
|
{
|
string querySql = QuerySql(where, false) + orderBy;
|
return ExecuteSingleEntity<T>(querySql, where);
|
}
|
#endregion
|
|
/// <summary>
|
/// 支持模糊查询
|
/// </summary>
|
/// <param name="whereColumn"></param>
|
/// <param name="where"></param>
|
/// <param name="isParamQuery">是否通过参数化查询</param>
|
/// <returns></returns>
|
/// <History>[Hanhe(dbs)] created by 2018-12-19</History>
|
public List<T> GetList(List<SearchWhere> whereColumn, object where, bool isParamQuery)
|
{
|
if (!isParamQuery)
|
return ExecuteQuery<T>(QuerySql(whereColumn, isParamQuery));
|
else
|
return ExecuteQuery<T>(QuerySql(whereColumn, isParamQuery), where);
|
}
|
|
/// <summary>
|
/// 支持模糊分页查询
|
/// </summary>
|
/// <param name="pageIndex"></param>
|
/// <param name="pageSize"></param>
|
/// <param name="total"></param>
|
/// <param name="whereColumn"></param>
|
/// <param name="isParamQuery">是否通过参数化查询</param>
|
/// <param name="where"></param>
|
/// <param name="orderBy"></param>
|
/// <returns></returns>
|
/// <History>[Hanhe(dbs)] created by 2018-12-19</History>
|
public DataTable GetPagingData(int pageIndex, int pageSize, out long total, List<SearchWhere> whereColumn, bool isParamQuery, object where = null, string orderBy = "")
|
{
|
string tableName = "(" + QuerySql(whereColumn, isParamQuery) + ") t";
|
|
Log.Info("DapperDAL", tableName);
|
return ExecutePagingData(tableName, pageIndex, pageSize, out total, orderBy, where);
|
}
|
|
public DataTable GetData(List<SearchWhere> whereColumn, bool isParamQuery, object where = null, string orderBy = "")
|
{
|
string tableName = QuerySql(whereColumn, isParamQuery) + " " + orderBy;
|
return ExecuteDataTable(tableName, where);
|
}
|
}
|
}
|
