public class BaseDal<T> where T : class
{
#region 通用方法
/// <summary>
/// 新增
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public virtual int Insert(T model)
{
int res = DbHelper.Insert<T>(model);
return res;
}
/// <summary>
/// 批量新增
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public virtual bool InsertBatch(List<T> list)
{
bool res = DbHelper.InsertBatch<T>(list);
return res;
}
/// <summary>
/// 修改
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public virtual bool Update(T model)
{
bool res = DbHelper.Update<T>(model);
return res;
}
/// <summary>
/// 更新非空字段\部分字段 默认第一个字段为更新主键
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public virtual bool UpdateNotNull(T model)
{
object val = "";
var p = new DynamicParameters();
StringBuilder sql = new StringBuilder();
sql.Append("UPDATE " + model.GetType().Name + " set ");
Type t = typeof(T);
System.Reflection.PropertyInfo[] properties = t.GetProperties();
foreach (System.Reflection.PropertyInfo info in properties)
{
val = model.GetType().GetProperty(info.Name).GetValue(model, null);
if (val != null && (info.Name != properties[0].Name))
{
sql.Append("" + info.Name + "=@" + info.Name + ",");
p.Add(info.Name, val);
}
string str = "name=" + info.Name + ";" + "type=" + info.PropertyType.Name + "," + info.GetType().Name + ";value=" + model.GetType().GetProperty(info.Name).GetValue(model, null) + "";
}
sql.Remove(sql.Length - 1, 1);
sql.Append(" where " + properties[0].Name + "=@" + properties[0].Name + "");
p.Add(properties[0].Name, model.GetType().GetProperty(properties[0].Name).GetValue(model, null));
DbHelper.Execute(sql.ToString(), p);
return true;
}
/// <summary>
/// 删除
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public virtual bool Delete(T model)
{
bool res = DbHelper.Delete<T>(model);
return res;
}
/// <summary>
/// 批量删除
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public virtual bool DeleteBatch(List<T> list)
{
bool res = DbHelper.DeleteBatch<T>(list);
return res;
}
/// <summary>
/// 查询一个实体
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public virtual T Get(string id)
{
T res = DbHelper.Get<T>(id);
return res;
}
/// <summary>
/// 查询全部
/// </summary>
/// <returns></returns>
public virtual List<T> GetListAll()
{
List<T> res = DbHelper.GetListAll<T>();
return res;
}
#endregion
#region 示例方法需重写
/// <summary>
/// 以条件查询
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public virtual List<T> GetList(JObject where)
{
//var where = new { UserID = 5 };
var p = new DynamicParameters();
IList<ISort> sort = new List<ISort>();
sort.Add(new Sort { PropertyName = "ID", Ascending = true });
if (where["ID"] != null)
{
p.Add("ID", new int[3] { 1, 5, 6 });
}
if (where["Name"] != null)
{
p.Add("Name", "张三");
}
List<T> res = DbHelper.GetList<T>(p, sort);
return res;
}
/// <summary>
/// 执行sql 查询 返回一个结果集
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public virtual List<T> Query(JObject value)
{
var p = new DynamicParameters();
StringBuilder sb = new StringBuilder();
sb.Append(" where 1=1");
if (value["ID"] != null)
{
sb.Append(" and a.ID in @ID");
p.Add("ID", new int[3] { 1, 5, 6 });
}
if (value["Name"] != null)
{
sb.Append(" and a.Name=@Name");
p.Add("Name", "张三");
}
string sql = @"select a.*,b.[Name]as ProjectName from ServiceRecordReport a
left join ProjectInfo b on a.ProjectId=b.ID ";
return DbHelper.Query<T>(sql + sb.ToString(), p);
}
/// <summary>
/// 查询第一行
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public virtual T QueryFirstOrDefaultView(JObject value)
{
var p = new DynamicParameters();
StringBuilder sb = new StringBuilder();
sb.Append(" where 1=1");
if (!string.IsNullOrWhiteSpace(value["ID"].ToString()))
{
sb.Append(" and a.ID=@ID");
p.Add("ID", value["ID"].ToString());
}
string sql = @"select a.*,b.[Name]as ProjectName from ServiceRecordReport a
left join ProjectInfo b on a.ProjectId=b.ID ";
return DbHelper.QueryFirstOrDefault<T>(sql + sb.ToString(), p);
}
/// <summary>
/// Dapper扩展分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="allRowsCount"></param>
/// <returns></returns>
public virtual List<T> GetPage(int pageIndex, int pageSize, out long allRowsCount, JObject jObject = null)
{
var p = new DynamicParameters();
p.Add("CustomerName", "张三");
IList<ISort> sort = new List<ISort>();
sort.Add(new Sort { PropertyName = "ID", Ascending = true });
List<T> res = DbHelper.GetPage<T>(pageIndex, pageSize, out allRowsCount, p, sort).ToList();
return res;
}
/// <summary>
/// SQL分页示例
/// </summary>
/// <returns></returns>
public virtual PageDataView<T> GetPageListForSQL(JObject value)
{
PageCriteria pageCriteria = new PageCriteria();
StringBuilder sb = new StringBuilder();
sb.Append("1=1");
//工程师（上传人）
if (!string.IsNullOrWhiteSpace(value["EngineerSignature"].ToString()))
{
sb.Append(" and EngineerSignature=@EngineerSignature");
pageCriteria.ParameterList.Add(new ParameterDict() { ParamName = "EngineerSignature", ParamValue = value["EngineerSignature"].ToString() });
}
//项目id
if (!string.IsNullOrWhiteSpace(value["ProjectId"].ToString()))
{
sb.Append(" and a.ProjectId=@ProjectId");
pageCriteria.ParameterList.Add(new ParameterDict() { ParamName = "ProjectId", ParamValue = value["ProjectId"].ToString() });
}
//上传日期
if (!string.IsNullOrWhiteSpace(value["CreaterDate"].ToString()))
{
sb.Append(" and CONVERT(varchar(100), a.CreaterDate, 23)=@CreaterDate");
pageCriteria.ParameterList.Add(new ParameterDict() { ParamName = "CreaterDate", ParamValue = value["CreaterDate"].ToString() });
}
pageCriteria.Condition = sb.ToString();
pageCriteria.CurrentPage = Convert.ToInt32(value["CurrentPage"]);
pageCriteria.Fields = " a.*,b.[Name]as ProjectName ";
pageCriteria.PageSize = 20;
//pageCriteria.PrimaryKey = " id";
pageCriteria.Sort = " a.ProjectId,a.ID desc";
pageCriteria.TableName = "ServiceRecordReport a left join ProjectInfo b on a.ProjectId = b.ID";
PageDataView<T> res = DbHelper.GetPageListForSQL<T>(pageCriteria);
return res;
}
/// <summary>
/// SQL分页示例 主要用于 MVC控制器 Layui
/// </summary>
/// <param name="page">当前页</param>
/// <param name="limit">每页记录数</param>
/// <param name="value">条件、排序等</param>
/// <returns></returns>
public virtual PageDataView<T> GetPageListForSQL(int page, int limit, T model)
{
PageCriteria pageCriteria = new PageCriteria();
StringBuilder sb = new StringBuilder();
sb.Append("1=1");
//if (!string.IsNullOrWhiteSpace(model.Name))
//{
// sb.Append(" and Name=@Name");
// pageCriteria.ParameterList.Add(new ParameterDict() { ParamName = "Name", ParamValue = model.Name });
//}
pageCriteria.Condition = sb.ToString();
pageCriteria.CurrentPage = page;
pageCriteria.Fields = " a.*,b.F_Name,b.F_Path,b.F_UploadTime,b.F_TargetID,b.F_TargetTable,b.F_TargetField ";
pageCriteria.PageSize = limit;
//pageCriteria.PrimaryKey = " id";
pageCriteria.Sort = "R_ID";
pageCriteria.TableName = " RepairRecord a left join FileManage b on a.R_SerialNumber = b.F_TargetID ";
PageDataView<T> res = DbHelper.GetPageListForSQL<T>(pageCriteria);
return res;
}
#endregion
}
