public class BaseDal where T : class
{
#region 通用方法
///
/// 新增
///
///
///
public virtual int Insert(T model)
{
int res = DbHelper.Insert(model);
return res;
}
///
/// 批量新增
///
///
///
public virtual bool InsertBatch(List list)
{
bool res = DbHelper.InsertBatch(list);
return res;
}
///
/// 修改
///
///
///
public virtual bool Update(T model)
{
bool res = DbHelper.Update(model);
return res;
}
///
/// 更新非空字段\部分字段 默认第一个字段为更新主键
///
///
///
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;
}
///
/// 删除
///
///
///
public virtual bool Delete(T model)
{
bool res = DbHelper.Delete(model);
return res;
}
///
/// 批量删除
///
///
///
public virtual bool DeleteBatch(List list)
{
bool res = DbHelper.DeleteBatch(list);
return res;
}
///
/// 查询一个实体
///
///
///
public virtual T Get(string id)
{
T res = DbHelper.Get(id);
return res;
}
///
/// 查询全部
///
///
public virtual List GetListAll()
{
List res = DbHelper.GetListAll();
return res;
}
#endregion
#region 示例方法需重写
///
/// 以条件查询
///
///
///
public virtual List GetList(JObject where)
{
//var where = new { UserID = 5 };
var p = new DynamicParameters();
IList sort = new List();
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 res = DbHelper.GetList(p, sort);
return res;
}
///
/// 执行sql 查询 返回一个结果集
///
///
///
public virtual List 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(sql + sb.ToString(), p);
}
///
/// 查询第一行
///
///
///
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(sql + sb.ToString(), p);
}
///
/// Dapper扩展分页
///
///
///
///
///
///
public virtual List GetPage(int pageIndex, int pageSize, out long allRowsCount, JObject jObject = null)
{
var p = new DynamicParameters();
p.Add("CustomerName", "张三");
IList sort = new List();
sort.Add(new Sort { PropertyName = "ID", Ascending = true });
List res = DbHelper.GetPage(pageIndex, pageSize, out allRowsCount, p, sort).ToList();
return res;
}
///
/// SQL分页示例
///
///
public virtual PageDataView 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 res = DbHelper.GetPageListForSQL(pageCriteria);
return res;
}
///
/// SQL分页示例 主要用于 MVC控制器 Layui
///
/// 当前页
/// 每页记录数
/// 条件、排序等
///
public virtual PageDataView 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 res = DbHelper.GetPageListForSQL(pageCriteria);
return res;
}
#endregion
}