接口层就不重点讲述了,直接DAL层
DAL层
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using FuturesContest.IDAL;
using SqlSugar; namespace FuturesContest.DAL
{
public class BaseDal<T>: IBaseDal<T> where T:class,new()
{
public int Add(T entity)
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Insertable(entity).ExecuteCommand();
return result;
}
} public int AddReutrnIdentity(T entity)
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Insertable(entity).ExecuteReturnIdentity();
return result;
}
} public T AddReturnEntity(T entity)
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Insertable(entity).ExecuteReturnEntity();
return result;
}
} public bool AddReturnBool(T entity)
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Insertable(entity).ExecuteCommandIdentityIntoEntity();
return result;
}
} public int AddBatch(IEnumerable<T> list)
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Insertable(list.ToArray()).ExecuteCommand();
return result;
}
} public int Delete(T entity)
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Deleteable(entity).ExecuteCommand();
return result;
}
} public int Delete(IEnumerable<T> list)
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Deleteable<T>(list).ExecuteCommand();
return result;
}
} public int Delete(int id)
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Deleteable<T>(id).ExecuteCommand();
return result;
} } public int Delete(int[] ids)
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Deleteable<T>(ids).ExecuteCommand();
return result;
}
} public int Delete(IEnumerable<int> ids)
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Deleteable<T>(ids).ExecuteCommand();
return result;
}
} public int Modify(T entity)
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Updateable(entity).ExecuteCommand();
return result;
}
} public int ModifyBatch(List<T> list)
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Updateable(list).ExecuteCommand();
return result;
}
} public IEnumerable<T> QueryAll()
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Queryable<T>().ToList();
return result;
}
} public IEnumerable<T> QueryTop(int top)
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Queryable<T>().Take(top).ToList();
return result;
}
} public T QueryById(int id)
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Queryable<T>().InSingle(id);
return result;
}
} public IEnumerable<T> QueryPage(Expression<Func<T, object>> orderBy, int orderType, int pageIndex, int pageSize, ref int totalCount)
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Queryable<T>().OrderBy(orderBy, (OrderByType)orderType).ToPageList(pageIndex, pageSize, ref totalCount);
return result;
}
} public IEnumerable<T> QueryByIf(Expression<Func<T, bool>> where)
{
using (var db = SqlSugarFatory.GetInstance())
{
if (where != null)
{
var list = db.Queryable<T>().WhereIF(true, where).ToList();
return list;
}
else
{
var list = db.Queryable<T>().ToList();
return list;
}
} } public IEnumerable<T> QueryByIfPage(Expression<Func<T, object>> orderBy, int orderType, Expression<Func<T, bool>> where, int pageIndex, int pageSize, ref int totalCount)
{
using (var db = SqlSugarFatory.GetInstance())
{
var list = db.Queryable<T>().WhereIF(true, where).OrderBy(orderBy, (OrderByType)orderType).ToPageList(pageIndex, pageSize, ref totalCount);
return list;
}
}
}
}
public class BaseDal<T>: IBaseDal<T> where T:class,new()
T为泛型类型,可以用任意类型代替
where为泛型约束
class约束了T必须是一个类
new()约束了T必须有一个无参的构造函数
类中封装了很多的公用方法,这些方法在每一个实体类中都适用.
public IEnumerable<T> QueryByIf(Expression<Func<T, bool>> where)
{
using (var db = SqlSugarFatory.GetInstance())
{
if (where != null)
{
var list = db.Queryable<T>().WhereIF(true, where).ToList();
return list;
}
else
{
var list = db.Queryable<T>().ToList();
return list;
}
}
}
这是个多条件查询的方法,如果按照以前的思路,我们肯定要传入所有可能为筛选条件的参数,然后用if判断是否等于空,并用where1=1的拼接sql语句方法拼写,太繁琐
而SqlSugar支持lamada查询,所以我直接传入一个Expression<Func<T, bool>> where类型的参数,这就是lamada表达式参数类型的写法
调用的时候就可以这样写dal.QueryByIf(m=>m.name=="test"&&m.password=="test")
public IEnumerable<T> QueryPage(Expression<Func<T, object>> orderBy, int orderType, int pageIndex, int pageSize, ref int totalCount)
{
using (var db = SqlSugarFatory.GetInstance())
{
var result = db.Queryable<T>().OrderBy(orderBy, (OrderByType)orderType).ToPageList(pageIndex, pageSize, ref totalCount);
return result;
}
}
这是一个分页查询
SqlSugar为我们封装好了分页查询的方法ToPageList(),也为我们封装好了排序的OrderBy()
注意,OrderBy一定要在ToPageList之前
OrderBy接收一个lamada的排序字段和枚举的排序类型
这里介绍一个小知识,枚举和int是可以互相转换的,枚举顺序默认从0开始,0就代表枚举中的第一个值,1代表第二个值
所以我们传入了一个int类型的orderType参数
public IEnumerable<T> QueryByIfPage(Expression<Func<T, object>> orderBy, int orderType, Expression<Func<T, bool>> where, int pageIndex, int pageSize, ref int totalCount)
{
using (var db = SqlSugarFatory.GetInstance())
{
var list = db.Queryable<T>().WhereIF(true, where).OrderBy(orderBy, (OrderByType)orderType).ToPageList(pageIndex, pageSize, ref totalCount);
return list;
}
}
当分页需要筛选的时候,我们在添加一个lamada的where条件,在orderBy之前进行筛选