每一个拥有数据库的项目，都会涉及到数据库数据的操作，而很多时候都会用到相同的方法，但是只是涉及到的表不一样，如果不对这些类似方法进行封装，开发上就会造成时间上的浪费。
那么如何对这些方法进行封装呢？
要会封装方法，最基本的得先了解 泛型 是什么，什么是泛型，博客园上有很多对这个的讲解，我也相信，科班的人对这个应该都有大概的了解，
其次得了解 反射，当然，很多人会说反射会影响性能，但是在如今设备都是很好的情况下，反射影响的性能微乎其微吧~
言归正传，说的再多不如实操，那么我们先新建数据库表的基类，并让数据库表类继承它，这样来约束泛型的类型只能是数据表对象，不能是其它类型，以此来避免不必要的错误！
/// <summary>
/// 数据库基类
/// </summary>
public abstract class entitybase : ientitybase
{
}
这里的 ientitybase 是前面第二篇中用到的一个空的接口基类，在这个抽象基类中，可以添加字段，这样继承该基类的数据库表都会加上这些字段，比如 创建时间、创建人等字段
因为这里会涉及到分页模型的因素，先新建泛型的分页模型类 pageresponse
/// <summary>
/// 分页模型
/// </summary>
/// <typeparam name="t"></typeparam>
public class pageresponse<t>
{
private long _recordtotal;
/// <summary>
/// 当前页码
/// </summary>
public int pageindex { get; set; }
/// <summary>
/// 总页数
/// </summary>
public int pagetotal { get; set; } = 1;
/// <summary>
/// 每页大小
/// </summary>
public int pagesize { get; set; }
/// <summary>
/// 总记录数
/// </summary>
public long recordtotal
{
get => _recordtotal;
set
{
_recordtotal = value;
if (pagesize <= 0) return;
pagetotal = (int)math.ceiling(recordtotal / (double)pagesize);
}
}
public list<t> data { get; set; }
public pageresponse()
{
data = new list<t>();
}
public pageresponse(list<t> data, int pageindex, int pagetotal)
{
data = data;
pageindex = pageindex;
pagetotal = pagetotal;
}
}
接下来我们新建一个数据库工厂类 来 进行 生产数据库上下文，代码如下
/// <summary>
/// 数据库工厂
/// </summary>
public class dbcontextfactory
{
/// <summary>
/// 数据库上下文
/// </summary>
/// <returns></returns>
public static demodbcontext getcurrentdbcontext()
{
if (demoweb.httpcontext.items["dbcontext"] is demodbcontext dbcontext) return dbcontext;
dbcontext = demoweb.iocmanager.resolve<demodbcontext>();//从容器中得到数据库上下文 放置在 items 中， 访问结束自动销毁
//dbcontext = demoweb.httpcontext.requestservices.getservice(typeof(demodbcontext)) as demodbcontext;
demoweb.httpcontext.items["dbcontext"] = dbcontext;
return dbcontext;
}
}
因为这里使用的是autofac模式，所以这样获取。至于为什么放到items中，也有简单的原因讲到。
再然后新建 ibasedao 接口文件，代码如下：
public interface ibasedao<t>
{
t add(t entity);
list<t> add(list<t> entity);
void delete(params object[] keyvalues);
void delete(object objectid);
void delete(expression<func<t, bool>> wherefun);
void update(t entity);
void update(expression<func<t, bool>> where, dictionary<string, object> dic);
bool exist(expression<func<t, bool>> anylambda);
t find(params object[] keyvalues);
iqueryable<t> where(expression<func<t, bool>> wherelambda);
t firstordefault(expression<func<t, bool>> wherelambda);
int count(expression<func<t, bool>> countlambda);
t first(expression<func<t, bool>> firstlambda);
iqueryable<t> loadentities(expression<func<t, bool>> wherelambda = null);
list<t> loadpageentities<tkey>(int pageindex, int pagesize,
out int totalcount, out int pagecount,
expression<func<t, bool>> wherelambda, bool isasc, expression<func<t, tkey>> orderby);
pageresponse<t> loadpageentities<tkey>(int pageindex, int pagesize,
expression<func<t, bool>> wherelambda, bool isasc, expression<func<t, tkey>> orderby);
iqueryable<tq> loadpageentities<tq, tkey>(iqueryable<tq> query, int pageindex, int pagesize,
out int totalcount, out int pagecount, bool isasc, expression<func<tq, tkey>> orderby) where tq : class, new();
pageresponse<tq> loadpageentities<tq, tkey>(iqueryable<tq> query, int pageindex, int pagesize,
bool isasc, expression<func<tq, tkey>> orderby) where tq : class, new();
int savechanges();
}
实现接口的类，代码如下： 代码有点长~~所以就折叠了~~
/// <summary>
/// 数据库基类
/// </summary>
/// <typeparam name="t"></typeparam>
public class basedao<t> : ibasedao<t> where t : entitybase, new()
{
public demodbcontext dbcontext => dbcontextfactory.getcurrentdbcontext();
public basedao()
{
//dbcontext = dbcontextfactory.getcurrentdbcontext();
}
#region 增删改的公共方法
public t add(t entity)
{
dbcontext.set<t>().add(entity);
//dbcontext.entry(entity).state = entitystate.added;
return entity;
}
public list<t> add(list<t> entitys)
{
dbcontext.set<t>().addrange(entitys); //注释掉下面的快许多 且不影响保存
//foreach (var model in entitys)
//{
// dbcontext.entry(model).state = entitystate.added;
//}
return entitys;
}
public void delete(expression<func<t, bool>> wherefun)
{
ienumerable<t> queryable = dbcontext.set<t>().where(wherefun);
//dbcontext.set<t>().removerange(queryable);
foreach (var model in queryable)
{
dbcontext.entry(model).state = entitystate.deleted;
}
}
public void update(t entity)
{
dbcontext.entry(entity).state = entitystate.modified;
}
public void update(expression<func<t, bool>> @where, dictionary<string, object> dic)
{
ienumerable<t> queryable = dbcontext.set<t>().where(@where).tolist();
type type = typeof(t);
list<propertyinfo> propertylist =
type.getproperties(bindingflags.public |
bindingflags.instance).tolist();
//遍历结果集
foreach (t entity in queryable)
{
foreach (var propertyinfo in propertylist)
{
string propertyname = propertyinfo.name;
if (dic.containskey(propertyname))
{
//设置值
propertyinfo.setvalue(entity, dic[propertyname], null);
}
}
update(entity);
}
}
public void delete(params object[] keyvalues)
{
var entity = dbcontext.set<t>().find(keyvalues);
dbcontext.entry(entity).state = entitystate.deleted;
}
public void delete(object objectid)
{
var entity = dbcontext.set<t>().find(objectid);
dbcontext.entry(entity).state = entitystate.deleted;
}
#endregion
#region 查询方法
/// <summary>
/// 查看是否存在
/// </summary>
/// <typeparam name="t"></typeparam>
/// <param name="anylambda"></param>
/// <returns></returns>
public bool exist(expression<func<t, bool>> anylambda)
{
return dbcontext.set<t>().any(anylambda);
}
/// <summary>
/// 根据主键得到数据
/// </summary>
/// <typeparam name="t"></typeparam>
/// <param name="keyvalues"></param>
/// <returns></returns>
public t find(params object[] keyvalues)
{
return dbcontext.set<t>().find(keyvalues);
}
/// <summary>
/// 根据where条件查找
/// </summary>
/// <typeparam name="t"></typeparam>
/// <param name="wherelambda"></param>
/// <returns></returns>
public iqueryable<t> where(expression<func<t, bool>> wherelambda)
{
return dbcontext.set<t>().where(wherelambda);
}
/// <summary>
/// 获取第一个或默认为空
/// </summary>
/// <param name="wherelambda"></param>
/// <returns></returns>
public t firstordefault(expression<func<t, bool>> wherelambda)
{
return dbcontext.set<t>().firstordefault(wherelambda);
}
/// <summary>
/// 得到条数
/// </summary>
/// <typeparam name="t"></typeparam>
/// <param name="countlambda"></param>
/// <returns></returns>
public int count(expression<func<t, bool>> countlambda)
{
return dbcontext.set<t>().asnotracking().count(countlambda);
}
/// <summary>
/// 获取第一个或默认的
/// </summary>
/// <typeparam name="t"></typeparam>
/// <param name="firstlambda"></param>
/// <returns></returns>
public t first(expression<func<t, bool>> firstlambda)
{
return dbcontext.set<t>().firstordefault(firstlambda);
}
/// <summary>
/// 得到iqueryable数据
/// </summary>
/// <typeparam name="t"></typeparam>
/// <param name="wherelambda"></param>
/// <returns></returns>
public iqueryable<t> loadentities(expression<func<t, bool>> wherelambda = null)
{
if (wherelambda == null)
{
return dbcontext.set<t>().asqueryable();
}
return dbcontext.set<t>().where(wherelambda).asqueryable();
}
/// <summary>
/// 从某个表中获取分页数据
/// </summary>
/// <typeparam name="t"></typeparam>
/// <typeparam name="tkey"></typeparam>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="totalcount"></param>
/// <param name="pagecount"></param>
/// <param name="wherelambda"></param>
/// <param name="isasc"></param>
/// <param name="orderby"></param>
/// <returns></returns>
public list<t> loadpageentities<tkey>(int pageindex, int pagesize, out int totalcount, out int pagecount, expression<func<t, bool>> wherelambda,
bool isasc, expression<func<t, tkey>> orderby)
{
var temp = dbcontext.set<t>().asnotracking().where(wherelambda); //去掉.asqueryable().asnotracking()，将下面改为
totalcount = temp.count();
pagecount = (int)math.ceiling((double)totalcount / pagesize);
if (isasc)
{
return temp.orderby(orderby)
.skip(pagesize * (pageindex - 1))
.take(pagesize).tolist(); //去掉.asqueryable()，添加.select(t=>new dto()).tolist()
}
return temp.orderbydescending(orderby)
.skip(pagesize * (pageindex - 1))
.take(pagesize).tolist(); //.select(t=>new dto()).tolist()
}
/// <summary>
/// 返回分页模型
/// </summary>
/// <typeparam name="t"></typeparam>
/// <typeparam name="tkey"></typeparam>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="wherelambda"></param>
/// <param name="isasc"></param>
/// <param name="orderby"></param>
/// <returns></returns>
public pageresponse<t> loadpageentities<tkey>(int pageindex, int pagesize, expression<func<t, bool>> wherelambda, bool isasc, expression<func<t, tkey>> orderby)
{
var temp = dbcontext.set<t>().asnotracking().where(wherelambda);
var rest = new pageresponse<t>();
rest.pageindex = pageindex;
rest.pagesize = pagesize;
rest.recordtotal = temp.count();//记录总条数时，自动设置了总页数
if (isasc)
{
rest.data = temp.orderby(orderby)
.skip(pagesize * (pageindex - 1))
.take(pagesize).tolist();
}
rest.data = temp.orderbydescending(orderby)
.skip(pagesize * (pageindex - 1))
.take(pagesize).tolist();
return rest;
}
/// <summary>
/// 将查询出来的数据 转换成iqueryable，然后进行分页 不跟踪数据状态
/// </summary>
/// <typeparam name="tq">返回类型</typeparam>
/// <typeparam name="tkey">根据哪个字段排序（必须）</typeparam>
/// <param name="query">数据集</param>
/// <param name="pageindex">页数</param>
/// <param name="pagesize">每页条数</param>
/// <param name="totalcount">总条数</param>
/// <param name="pagecount">总页数</param>
/// <param name="isasc">是否倒序</param>
/// <param name="orderby">排序字段</param>
/// <returns>iqueryable分页结果</returns>
public iqueryable<tq> loadpageentities<tq, tkey>(iqueryable<tq> query, int pageindex, int pagesize, out int totalcount, out int pagecount, bool isasc, expression<func<tq, tkey>> orderby) where tq : class, new()
{
iqueryable<tq> temp = query.asnotracking();
totalcount = temp.count();
pagecount = (int)math.ceiling((double)totalcount / pagesize);
if (isasc)
{
temp = temp.orderby(orderby)
.skip(pagesize * (pageindex - 1))
.take(pagesize).asqueryable();
}
else
{
temp = temp.orderbydescending(orderby)
.skip(pagesize * (pageindex - 1))
.take(pagesize).asqueryable();
}
return temp;
}
/// <summary>
/// 将查询出来的数据 转换成iqueryable，然后进行分页 不跟踪数据状态
/// </summary>
/// <typeparam name="tq">返回类型</typeparam>
/// <typeparam name="tkey">根据哪个字段排序（必须）</typeparam>
/// <param name="query">数据集</param>
/// <param name="pageindex">页数</param>
/// <param name="pagesize">每页条数</param>
/// <param name="isasc">是否倒序</param>
/// <param name="orderby">排序字段</param>
/// <returns>pageresponse分页结果</returns>
public pageresponse<tq> loadpageentities<tq, tkey>(iqueryable<tq> query, int pageindex, int pagesize, bool isasc, expression<func<tq, tkey>> orderby) where tq : class, new()
{
var rest = new pageresponse<tq>();
iqueryable<tq> temp = query.asnotracking();
rest.recordtotal = temp.count();
if (isasc)
{
rest.data = temp.orderby(orderby)
.skip(pagesize * (pageindex - 1))
.take(pagesize).tolist();
}
else
{
rest.data = temp.orderbydescending(orderby)
.skip(pagesize * (pageindex - 1))
.take(pagesize).tolist();
}
return rest;
}
#endregion
/// <summary>
/// 自带事务，调用此方法保存
/// </summary>
public int savechanges()
{
var res = -1;
try
{
res = dbcontext.savechanges();
//dispose();
}
catch (dbexception ex)
{
throw new customsystemexception($"数据库保存失败!{ex.message}", 999);
}
catch (exception ex)
{
throw new customsystemexception($"数据库保存失败!{ex.message}", 999);
}
return res;
}
public void dispose()
{
this.dbcontext.dispose();
gc.suppressfinalize(this);
}
}
到这里，根据每个数据库表建对应的 dao 类，这样一来开发效率就会明显提升，示例代码如下：
public class demomodeldao : basedao<demomodel>
{
private static object locker = new object();
private static demomodeldao _demomodeldao;
public static demomodeldao instance
{
get
{
if (_demomodeldao != null) return _demomodeldao;
lock (locker)
{
if (_demomodeldao == null)
{
_demomodeldao = new demomodeldao();
}
}
return _demomodeldao;
}
}
/// <summary>
/// 得到分页数据
/// </summary>
/// <param name="querydemo"></param>
/// <returns></returns>
public pageresponse<demomodel> demopageresponse(querydemodto querydemo)
{
var date = loadpageentities(querydemo.page, querydemo.pagesize,
c => c.customername.contains(querydemo.name), false, c => c.id);
return date;
}
}
然后添加测试方法，添加 biz 类，调用测试
public class demomodelbiz
{
private static object locker = new object();
private static demomodelbiz _demomodelbiz;
public static demomodelbiz instance
{
get
{
if (_demomodelbiz != null) return _demomodelbiz;
lock (locker)
{
if (_demomodelbiz == null)
{
_demomodelbiz = new demomodelbiz();
}
}
return _demomodelbiz;
}
}
public string adddemomodel(demomodel demomodel)
{
demomodeldao.instance.add(demomodel);
var count = demomodeldao.instance.savechanges();
return count > 0 ? "success" : "save error";
}
public string adddemomodel(list<demomodel> demomodels)
{
demomodeldao.instance.add(demomodels);
demomodeldao.instance.delete(c=>c.id == 1);
demomodeldao.instance.delete(c=>c.customername.startswith("2"));
testmodeldao.instance.add(new testmodel()
{
blogname = "net core",
blogphone = 123,
bloguseday = 90
});
var count = demomodeldao.instance.savechanges();
return count > 0 ? "success" : "save error";
}
/// <summary>
/// 得到分页数据
/// </summary>
/// <param name="querydemo"></param>
/// <returns></returns>
public pageresponse<demomodel> demomodellist(querydemodto querydemo)
{
return demomodeldao.instance.demopageresponse(querydemo);
}
}
再添加测试的控制器类，示例代码如下：
[route("api/[controller]")]
public class demomodelcontroller : basecontroller
{
[route("testadd"), httppost]
public async task<actionresult> adddemomodel()
{
var models = new list<demomodel>();
for (int i = 0; i < 100; i++)
{
var testmodel = new demomodel()
{
customername = i +"-levy" + datetime.now.tostring("hh:mm:ss"),
identitycardtype = 1
};
models.add(testmodel);
}
for (int i = 0; i < 100; i++)
{
var testmodel = new demomodel()
{
customername = i + "-zzzz" + datetime.now.tostring("hh:mm:ss"),
identitycardtype = 2
};
models.add(testmodel);
}
var res = await task.fromresult(demomodelbiz.instance.adddemomodel(models));
return succeed(res);
}
[route("demolist"), httppost]
public async task<actionresult> demomodellist([frombody] querydemodto querydemo)
{
var res = await task.fromresult(demomodelbiz.instance.demomodellist(querydemo));
return succeed(res);
}
}
涉及到的类：
public class querydemodto
{
public int page { get; set; }
public int pagesize { get; set; }
public string name { get; set; }
}
接下来就运行程序调试看结果吧~
这里数据保存成功之后我们进行数据的查询，
可以看到查询出结果，这里有两千多条数据，是因为执行了多次且每次保存前都会删除以2开始的数据。
题外话，因为我们是将数据库上下文放在 httpcontext.items 中的，可能有些人会担心程序运行完后会不释放，从而导致数据库链接过多而出现崩溃，
首先呢，访问结束时 httpcontext.items 就会销毁，也就意味着数据库链接也就销毁了，
如果还是不放心，可以在方法执行完成时，将数据库链接手动释放，
首先在工厂类中加上
/// <summary>
/// 释放dbcontext对象
/// </summary>
public static void disposedbcontext()
{
if (demoweb.httpcontext.items.containskey("dbcontext"))
{
demoweb.httpcontext.items.remove("dbcontext");
}
}
然后不管程序正常执行完成，还是遇到异常，都会走控制器的 onactionexecuted 方法，因此可以重载这个方法，然后调用释放方法 dbcontextfactory.disposedbcontext();
以上若有什么不对或可以改进的地方，望各位指出或提出意见，一起探讨学习~
有需要源码的可通过此 github 链接拉取 觉得还可以的给个 start 和点个 下方的推荐哦~~谢谢！
黄山市民网：https://www.huangshanshimin.com/
