using System;
|
using System.Linq;
|
using System.Collections.Generic;
|
using System.Data.SqlClient;
|
using iWareCommon.Common.Entity;
|
using System.Data.Entity;
|
|
namespace iWareCommon.Common.Dao
|
{
|
/// <summary>
|
/// 公用方法的抽象类
|
/// 张展
|
/// </summary>
|
/// <typeparam name="T">自定义的实体类</typeparam>
|
/// <typeparam name="S">ORM中的实体类</typeparam>
|
public abstract class CommonDao<T, S> where T : class, ICommonEntity<S> where S : class
|
{
|
|
#region 需要在子类中实现的抽象方法
|
/// <summary>
|
/// 该方法获取在自定义实体和ORM自动生成实体之间字段的对应关系
|
/// </summary>
|
protected abstract string GetColumnName(string name);
|
|
/// <summary>
|
/// 获取T中属性名列表
|
/// </summary>
|
/// <returns>T中属性名列表</returns>
|
protected abstract List<string> GetColumnNames();
|
|
/// <summary>
|
/// 该方法将Orm的S对象转换为自定义类型T的对象
|
/// </summary>
|
/// <param name="s">Orm中的S实体的实例</param>
|
/// <returns>自定义的T类型实例</returns>
|
protected abstract T ToEntity(S s);
|
|
/// <summary>
|
/// 该方法将定义类型T的S对象转换为自Orm的对象
|
/// </summary>
|
/// <param name="t">自定义T类型的对象</param>
|
/// <returns>Orm中S的对象</returns>
|
protected abstract S ToOrm(T t);
|
|
/// <summary>
|
/// 该方法获取在自定义实体和ORM自动生成实体之间字段的对应关系
|
/// </summary>
|
protected abstract string GetTableName();
|
#endregion
|
|
#region 根据条件查询
|
|
/// <summary>
|
/// 根据条件查询自定义实体T的列表
|
/// </summary>
|
/// <param name="param">查询条件</param>
|
/// <param name="msg">错误信息</param>
|
/// <returns>自定义实体T的记录列表</returns>
|
public virtual List<T> QueryByParam(QueryParam param, DbContext dbModel)
|
{
|
var res = new List<T>();
|
var filter = param.Filter == null || param.Filter.Count <= 0 ? new Dictionary<string, object>() : param.Filter;
|
var search = param.Search == null || param.Search.Count <= 0 ? new Dictionary<string, object>() : param.Search;
|
|
var leqd = param.Leqd == null || param.Leqd.Count <= 0 ? new Dictionary<string, object>() : param.Leqd;
|
var geqd = param.Geqd == null || param.Geqd.Count <= 0 ? new Dictionary<string, object>() : param.Geqd;
|
var ltd = param.Ltd == null || param.Ltd.Count <= 0 ? new Dictionary<string, object>() : param.Ltd;
|
var gtd = param.Gtd == null || param.Gtd.Count <= 0 ? new Dictionary<string, object>() : param.Gtd;
|
var leq = param.Leq == null || param.Leq.Count <= 0 ? new Dictionary<string, object>() : param.Leq;
|
var geq = param.Geq == null || param.Geq.Count <= 0 ? new Dictionary<string, object>() : param.Geq;
|
var lt = param.Lt == null || param.Lt.Count <= 0 ? new Dictionary<string, object>() : param.Lt;
|
var gt = param.Gt == null || param.Gt.Count <= 0 ? new Dictionary<string, object>() : param.Gt;
|
var neq = param.Neq == null || param.Neq.Count <= 0 ? new Dictionary<string, object>() : param.Neq;
|
var order = param.Order == null || param.Order.Count <= 0 ? new Dictionary<string, object>() { { "Id", "DESC" } } : param.Order;
|
|
var sql = string.Format("SELECT * FROM {0} WHERE 1=1", GetTableName());
|
|
var sqlParams = new List<SqlParameter>();
|
|
foreach (var key in filter.Keys)
|
{
|
if (filter[key] != null && !string.IsNullOrEmpty(filter[key] + ""))
|
{
|
|
sqlParams.Add(new SqlParameter(("@F_" + key), filter[key]));
|
sql += string.Format(" AND {0} = {1}", GetColumnName(key), "@F_" + key);
|
}
|
}
|
|
foreach (var key in search.Keys)
|
{
|
if (search[key] != null && !string.IsNullOrEmpty(search[key] + ""))
|
{
|
sqlParams.Add(new SqlParameter("@S_" + key, "%" + search[key] + "%"));
|
sql += string.Format(" AND {0} LIKE {1}", GetColumnName(key), "@S_" + key);
|
}
|
}
|
|
foreach (var key in leqd.Keys)
|
{
|
if (leqd[key] != null && !string.IsNullOrEmpty(leqd[key] + ""))
|
{
|
sqlParams.Add(new SqlParameter("@LEQD_" + key, leqd[key]));
|
sql += string.Format(" AND {0} <= {1}", GetColumnName(key), "@LEQD_" + key);
|
}
|
}
|
|
|
foreach (var key in geqd.Keys)
|
{
|
if (geqd[key] != null && !string.IsNullOrEmpty(geqd[key] + ""))
|
{
|
sqlParams.Add(new SqlParameter("@GEQD_" + key, geqd[key]));
|
sql += string.Format(" AND {0} >= {1}", GetColumnName(key), "@GEQD_" + key);
|
}
|
}
|
|
foreach (var key in ltd.Keys)
|
{
|
if (ltd[key] != null && !string.IsNullOrEmpty(ltd[key] + ""))
|
{
|
sqlParams.Add(new SqlParameter("@LTD_" + key, ltd[key]));
|
sql += string.Format(" AND {0} < {1}", GetColumnName(key), "@LTD_" + key);
|
}
|
}
|
|
foreach (var key in gtd.Keys)
|
{
|
if (gtd[key] != null && !string.IsNullOrEmpty(gtd[key] + ""))
|
{
|
sqlParams.Add(new SqlParameter("@GTD_" + key, gtd[key]));
|
sql += string.Format(" AND {0} > {1}", GetColumnName(key), "@GTD_" + key);
|
}
|
}
|
|
foreach (var key in leq.Keys)
|
{
|
if (leq[key] != null && !string.IsNullOrEmpty(leq[key] + ""))
|
{
|
sqlParams.Add(new SqlParameter("@LEQ_" + key, leq[key]));
|
sql += string.Format(" AND {0} <= {1}", GetColumnName(key), "@LEQ_" + key);
|
}
|
}
|
|
foreach (var key in geq.Keys)
|
{
|
if (geq[key] != null && !string.IsNullOrEmpty(geq[key] + ""))
|
{
|
sqlParams.Add(new SqlParameter("@GEQ_" + key, geq[key]));
|
sql += string.Format(" AND {0} >= {1}", GetColumnName(key), "@GEQ_" + key);
|
}
|
}
|
|
foreach (var key in lt.Keys)
|
{
|
if (lt[key] != null && !string.IsNullOrEmpty(lt[key] + ""))
|
{
|
sqlParams.Add(new SqlParameter("@LT_" + key, lt[key]));
|
sql += string.Format(" AND {0} < {1}", GetColumnName(key), "@LT_" + key);
|
}
|
}
|
|
foreach (var key in gt.Keys)
|
{
|
if (gt[key] != null && !string.IsNullOrEmpty(gt[key] + ""))
|
{
|
sqlParams.Add(new SqlParameter("@GT_" + key, gt[key]));
|
sql += string.Format(" AND {0} > {1}", GetColumnName(key), "@GT_" + key);
|
}
|
}
|
|
foreach (var key in neq.Keys)
|
{
|
if (neq[key] != null && !string.IsNullOrEmpty(neq[key] + ""))
|
{
|
sqlParams.Add(new SqlParameter("@NEQ_" + key, neq[key]));
|
sql += string.Format(" AND {0} <> {1}", GetColumnName(key), "@NEQ_" + key);
|
}
|
}
|
|
sql += " ORDER BY ";
|
|
foreach (var key in order.Keys)
|
{
|
|
var value = string.IsNullOrEmpty(order[key].ToString()) ? "DESC" : order[key].ToString().ToUpper();
|
|
sqlParams.Add(new SqlParameter("@O_" + key, order[key]));
|
sql += string.Format("{0} {1},", GetColumnName(key), value);
|
|
}
|
|
if (sql.EndsWith(","))
|
{
|
sql = sql.Substring(0, sql.Length - 1);
|
}
|
var datas = dbModel.Database.SqlQuery<S>(sql, sqlParams.ToArray()).ToList();
|
datas.ForEach(x => res.Add(ToEntity(x)));
|
|
|
return res;
|
}
|
|
#endregion
|
|
#region 根据条件分页查询
|
/// <summary>
|
/// 根据条件分页查询自定义实体T的列表，同时返回记录的总条数及当前所在的页数
|
/// </summary>
|
/// <param name="param">查询条件</param>
|
/// <param name="msg">异常错误消息</param>
|
/// <param name="totalNum">记录的总条数</param>
|
/// <param name="currentPage">当前页面数</param>
|
/// <returns>自定义实体T的记录列表</returns>
|
public virtual List<T> QueryByParam(QueryParam param, DbContext dbModel, out int totalNum, out int currentPage)
|
{
|
|
totalNum = 0;
|
currentPage = 1;
|
|
var res = new List<T>();
|
|
var filter = param.Filter == null || param.Filter.Count <= 0 ? new Dictionary<string, object>() : param.Filter;
|
var search = param.Search == null || param.Search.Count <= 0 ? new Dictionary<string, object>() : param.Search;
|
|
var leqd = param.Leqd == null || param.Leqd.Count <= 0 ? new Dictionary<string, object>() : param.Leqd;
|
var geqd = param.Geqd == null || param.Geqd.Count <= 0 ? new Dictionary<string, object>() : param.Geqd;
|
var ltd = param.Ltd == null || param.Ltd.Count <= 0 ? new Dictionary<string, object>() : param.Ltd;
|
var gtd = param.Gtd == null || param.Gtd.Count <= 0 ? new Dictionary<string, object>() : param.Gtd;
|
var leq = param.Leq == null || param.Leq.Count <= 0 ? new Dictionary<string, object>() : param.Leq;
|
var geq = param.Geq == null || param.Geq.Count <= 0 ? new Dictionary<string, object>() : param.Geq;
|
var lt = param.Lt == null || param.Lt.Count <= 0 ? new Dictionary<string, object>() : param.Lt;
|
var gt = param.Gt == null || param.Gt.Count <= 0 ? new Dictionary<string, object>() : param.Gt;
|
var neq = param.Neq == null || param.Neq.Count <= 0 ? new Dictionary<string, object>() : param.Neq;
|
var pageIndex = param.PageIndex < 1 ? 1 : param.PageIndex;
|
var pageSize = param.PageSize < 1 ? 5 : param.PageSize;
|
var order = param.Order == null || param.Order.Count <= 0 ? new Dictionary<string, object>() { { "Id", "DESC" } } : param.Order;
|
|
//查询数量的sql语句
|
var sqlCount = string.Format("SELECT COUNT(*) FROM {0} WHERE 1=1", GetTableName());
|
|
//查询数量使用的sql参数列表
|
var sqlParamsCount = new List<SqlParameter>();
|
|
var orderStr = "";
|
|
|
foreach (var key in order.Keys)
|
{
|
var value = string.IsNullOrEmpty(order[key].ToString()) ? "DESC" : order[key].ToString().ToUpper();
|
sqlParamsCount.Add(new SqlParameter("@O_" + key, order[key]));
|
orderStr += string.Format("{0} {1},", GetColumnName(key), value);
|
}
|
|
if (orderStr.EndsWith(","))
|
{
|
orderStr = orderStr.Substring(0, orderStr.Length - 1);
|
}
|
|
//查询记录列表的sql语句
|
var sqlData = string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY {0} ) AS ROWNUM from {1} where 1=1", orderStr, GetTableName());
|
|
//查询记录列表使用的sql参数列表
|
var sqlParamsData = new List<SqlParameter>();
|
|
foreach (var key in filter.Keys)
|
{
|
if (filter[key] != null && !string.IsNullOrEmpty(filter[key] + ""))
|
{
|
sqlParamsCount.Add(new SqlParameter("@F_" + key, filter[key]));
|
sqlParamsData.Add(new SqlParameter("@F_" + key, filter[key]));
|
sqlCount += string.Format(" AND {0} = {1}", GetColumnName(key), "@F_" + key);
|
sqlData += string.Format(" AND {0} = {1}", GetColumnName(key), "@F_" + key);
|
}
|
}
|
|
foreach (var key in search.Keys)
|
{
|
if (search[key] != null && !string.IsNullOrEmpty(search[key] + ""))
|
{
|
sqlParamsCount.Add(new SqlParameter("@S_" + key, "%" + search[key] + "%"));
|
sqlParamsData.Add(new SqlParameter("@S_" + key, "%" + search[key] + "%"));
|
sqlCount += string.Format(" AND {0} LIKE {1}", GetColumnName(key), "@S_" + key);
|
sqlData += string.Format(" AND {0} LIKE {1}", GetColumnName(key), "@S_" + key);
|
}
|
}
|
|
foreach (var key in leqd.Keys)
|
{
|
if (leqd[key] != null && !string.IsNullOrEmpty(leqd[key] + ""))
|
{
|
sqlParamsCount.Add(new SqlParameter("@LEQD_" + key, leqd[key]));
|
sqlParamsData.Add(new SqlParameter("@LEQD_" + key, leqd[key]));
|
sqlCount += string.Format(" AND {0} <= {1}", GetColumnName(key), "@LEQD_" + key);
|
sqlData += string.Format(" AND {0} <= {1}", GetColumnName(key), "@LEQD_" + key);
|
}
|
}
|
|
foreach (var key in geqd.Keys)
|
{
|
if (geqd[key] != null && !string.IsNullOrEmpty(geqd[key] + ""))
|
{
|
sqlParamsCount.Add(new SqlParameter("@GEQD_" + key, geqd[key]));
|
sqlParamsData.Add(new SqlParameter("@GEQD_" + key, geqd[key]));
|
sqlCount += string.Format(" AND {0} >= {1}", GetColumnName(key), "@GEQD_" + key);
|
sqlData += string.Format(" AND {0} >= {1}", GetColumnName(key), "@GEQD_" + key);
|
}
|
}
|
|
foreach (var key in ltd.Keys)
|
{
|
if (ltd[key] != null && !string.IsNullOrEmpty(ltd[key] + ""))
|
{
|
sqlParamsCount.Add(new SqlParameter("@LTD_" + key, ltd[key]));
|
sqlParamsData.Add(new SqlParameter("@LTD_" + key, ltd[key]));
|
sqlCount += string.Format(" AND {0} < {1}", GetColumnName(key), "@LTD_" + key);
|
sqlData += string.Format(" AND {0} < {1}", GetColumnName(key), "@LTD_" + key);
|
}
|
}
|
|
foreach (var key in gtd.Keys)
|
{
|
if (gtd[key] != null && !string.IsNullOrEmpty(gtd[key] + ""))
|
{
|
sqlParamsCount.Add(new SqlParameter("@GTD_" + key, gtd[key]));
|
sqlParamsData.Add(new SqlParameter("@GTD_" + key, gtd[key]));
|
sqlCount += string.Format(" AND {0} > {1}", GetColumnName(key), "@GTD_" + key);
|
sqlData += string.Format(" AND {0} > {1}", GetColumnName(key), "@GTD_" + key);
|
}
|
}
|
|
foreach (var key in leq.Keys)
|
{
|
if (leq[key] != null && !string.IsNullOrEmpty(leq[key] + ""))
|
{
|
sqlParamsCount.Add(new SqlParameter("@LEQ_" + key, leq[key]));
|
sqlParamsData.Add(new SqlParameter("@LEQ_" + key, leq[key]));
|
sqlCount += string.Format(" AND {0} <= {1}", GetColumnName(key), "@LEQ_" + key);
|
sqlData += string.Format(" AND {0} <= {1}", GetColumnName(key), "@LEQ_" + key);
|
}
|
}
|
|
foreach (var key in geq.Keys)
|
{
|
if (geq[key] != null && !string.IsNullOrEmpty(geq[key] + ""))
|
{
|
sqlParamsCount.Add(new SqlParameter("@GEQ_" + key, geq[key]));
|
sqlParamsData.Add(new SqlParameter("@GEQ_" + key, geq[key]));
|
sqlCount += string.Format(" AND {0} >= {1}", GetColumnName(key), "@GEQ_" + key);
|
sqlData += string.Format(" AND {0} >= {1}", GetColumnName(key), "@GEQ_" + key);
|
}
|
}
|
|
foreach (var key in lt.Keys)
|
{
|
if (lt[key] != null && !string.IsNullOrEmpty(lt[key] + ""))
|
{
|
sqlParamsCount.Add(new SqlParameter("@LT_" + key, lt[key]));
|
sqlParamsData.Add(new SqlParameter("@LT_" + key, lt[key]));
|
sqlCount += string.Format(" AND {0} < {1}", GetColumnName(key), "@LT_" + key);
|
sqlData += string.Format(" AND {0} < {1}", GetColumnName(key), "@LT_" + key);
|
}
|
}
|
|
foreach (var key in gt.Keys)
|
{
|
if (gt[key] != null && !string.IsNullOrEmpty(gt[key] + ""))
|
{
|
sqlParamsCount.Add(new SqlParameter("@GT_" + key, gt[key]));
|
sqlParamsData.Add(new SqlParameter("@GT_" + key, gt[key]));
|
sqlCount += string.Format(" AND {0} > {1}", GetColumnName(key), "@GT_" + key);
|
sqlData += string.Format(" AND {0} > {1}", GetColumnName(key), "@GT_" + key);
|
}
|
}
|
|
foreach (var key in neq.Keys)
|
{
|
if (neq[key] != null && !string.IsNullOrEmpty(neq[key] + ""))
|
{
|
sqlParamsCount.Add(new SqlParameter("@NEQ_" + key, neq[key]));
|
sqlParamsData.Add(new SqlParameter("@NEQ_" + key, neq[key]));
|
sqlCount += string.Format(" AND {0} <> {1}", GetColumnName(key), "@NEQ_" + key);
|
sqlData += string.Format(" AND {0} <> {1}", GetColumnName(key), "@NEQ_" + key);
|
}
|
}
|
|
totalNum = dbModel.Database.SqlQuery<int>(sqlCount, sqlParamsCount.ToArray()).ToList()[0];
|
|
int totalPage = Convert.ToInt32(totalNum / pageSize);
|
|
if ((totalNum % pageSize) > 0)
|
{
|
totalPage += 1;
|
}
|
|
currentPage = pageIndex > totalPage ? totalPage : pageIndex;
|
currentPage = Math.Max(currentPage,1);
|
sqlParamsData.Add(new SqlParameter("@PI_Start", (currentPage - 1) * pageSize + 1));
|
sqlParamsData.Add(new SqlParameter("@PI_End", currentPage * pageSize));
|
sqlData += string.Format(") t Where t.ROWNUM between {0} and {1} ", "@PI_Start", "@PI_End");
|
|
var datas = dbModel.Database.SqlQuery<S>(sqlData, sqlParamsData.ToArray()).ToList();
|
|
|
|
datas.ForEach(x => res.Add(ToEntity(x)));
|
return res;
|
}
|
|
#endregion
|
|
#region 插入新的对象
|
|
/// <summary>
|
/// 将自定义的实体T的实例列表批量保存到数据库
|
/// </summary>
|
/// <param name="ts">自定义类型T的实例列表</param>
|
/// <param name="msg">异常错误消息</param>
|
/// <returns>保存的数量</returns>
|
public virtual int Save(List<T> ts, DbContext dbModel)
|
{
|
if (ts == null)
|
{
|
return 0;
|
}
|
ts.ForEach(x => dbModel.Set<S>().Add(ToOrm(x)));
|
dbModel.SaveChanges();
|
return ts.Count;
|
}
|
|
/// <summary>
|
/// 将自定义的实体T的实例保存到数据库
|
/// </summary>
|
/// <param name="t">自定义类型T的实例</param>
|
/// <param name="msg">异常错误消息</param>
|
/// <returns>新添加T对象的id</returns>
|
public virtual int Save(T t, DbContext dbModel)
|
{
|
var s = ToOrm(t);
|
dbModel.Set<S>().Add(s);
|
dbModel.SaveChanges();
|
return ToEntity(s).Id;
|
}
|
|
#endregion
|
|
#region 修改对象
|
/// <summary>
|
/// 修改自定义类型T的实体并保存到数据库
|
/// </summary>
|
/// <param name="t">自定义类型T的实例</param>
|
/// <param name="msg">异常错误消息</param>
|
/// <returns>需改实体的id</returns>
|
public virtual int Update(T t, DbContext dbModel)
|
{
|
var entry = dbModel.Entry<S>(ToOrm(t));
|
entry.State = System.Data.Entity.EntityState.Unchanged;
|
var propStrs = GetColumnNames();
|
propStrs.ForEach(x => entry.Property(GetColumnName(x)).IsModified = true);
|
dbModel.SaveChanges();
|
return t.Id;
|
}
|
|
/// <summary>
|
/// 修改自定义类型T的实体并保存到数据库
|
/// </summary>
|
/// <param name="t">自定义类型T的实例</param>
|
/// <param name="msg">异常错误消息</param>
|
/// <returns>需改实体的id</returns>
|
///
|
public virtual int Update(List<T> ts, DbContext dbModel)
|
{
|
foreach (var t in ts)
|
{
|
var entry = dbModel.Entry<S>(ToOrm(t));
|
entry.State = System.Data.Entity.EntityState.Unchanged;
|
var propStrs = GetColumnNames();
|
propStrs.ForEach(x => entry.Property(GetColumnName(x)).IsModified = true);
|
}
|
dbModel.SaveChanges();
|
return ts.Count;
|
}
|
|
#endregion
|
|
#region 删除对象
|
|
/// <summary>
|
/// 从数据库中删除主键为id的记录
|
/// </summary>
|
/// <param name="id">需要删除对象的id</param>
|
/// <param name="msg">异常错误信息</param>
|
/// <returns>被删除对象的主键</returns>
|
public virtual int Delete(int id, DbContext dbModel)
|
{
|
var count = Delete(new List<int> { id }, dbModel);
|
return id;
|
}
|
|
/// <summary>
|
/// 从数据库中批量删除主键列表为ids的记录
|
/// </summary>
|
/// <param name="id">需要删除对象的id列表</param>
|
/// <param name="msg">异常错误信息</param>
|
/// <returns>被删除对象的数量</returns>
|
public virtual int Delete(List<int> ids, DbContext dbModel)
|
{
|
foreach (var id in ids)
|
{
|
var t = Activator.CreateInstance(typeof(T));
|
((T)t).Id = id;
|
var s = ToOrm((T)t);
|
dbModel.Set<S>().Attach(s);
|
dbModel.Set<S>().Remove(s);
|
}
|
|
|
dbModel.SaveChanges();
|
return ids.Count;
|
}
|
#endregion
|
|
|
#region 拼装字典
|
/// <summary>
|
/// 将满足条件的T类型拼装成以id为键，类型本身为值的字典
|
/// </summary>
|
/// <param name="param">查询条件</param>
|
/// <param name="msg">异常错误消息</param>
|
/// <returns>以id为键，类型本身为值的字典</returns>
|
public virtual Dictionary<int, T> ToDictionary(QueryParam param, DbContext dbModel)
|
{
|
var res = new Dictionary<int, T>();
|
var datas = QueryByParam(param, dbModel);
|
datas.ForEach(x => res.Add(x.Id,x));
|
return res;
|
}
|
|
#endregion
|
}
|
|
}
|
