Asp.Net 高性能框架 SqlSugar.ORM 2.3
程序员文章站
2022-04-12 19:49:40
前言
sqlsugar从去年到现在已经一年了，版本从1.0升到了现在的2.3 ，这是一个稳定版本 ，有数家公司已经项目上线，在这里我将sqlsugar的功能重新整理成一篇新的贴子，希望大家喜欢。
公...
前言
sqlsugar从去年到现在已经一年了，版本从1.0升到了现在的2.3 ，这是一个稳定版本 ，有数家公司已经项目上线，在这里我将sqlsugar的功能重新整理成一篇新的贴子，希望大家喜欢。
公司团队项目、产品已经完全抛弃ef，sqlsugar定位不是orm,而是为了方面的让你去写sql。
支持json 、dynamic、 list
执行十万次【将拉姆达】转成【sql】只用了1秒多（机型：surface pro4 i5 8g）
更多测试（机型：过时机器）
查询
1、sqlquery 用于执行原生sql和存储过程
//转成list
list list1 = db.sqlquery("select * from student");
//转成list带参
list list2 = db.sqlquery("select * from student where id=@id", new { id = 1 });
//转成dynamic
dynamic list3 = db.sqlquerydynamic("select * from student");
//转成json
string list4 = db.sqlqueryjson("select * from student");
//返回int
var list5 = db.sqlquery("select top 1 id from student").single();
//反回键值
dictionary list6 = db.sqlquery>("select id,name from student").todictionary(it => it.key, it => it.value);
//反回list
var list7 = db.sqlquery("select top 1 id,name from student").single();
//存储过程
var spresult = db.sqlquery("exec sp_school @p1,@p2", new { p1=1,p2=2 });
sql查询
2、queryable 用于单表或者视图查询
//---------queryable,扩展函数查询---------//
//针对单表或者视图查询
//查询所有
var student = db.queryable().tolist();
var studentdynamic = db.queryable().todynamic();
var studentjson = db.queryable().tojson();
//查询单条
var single = db.queryable().single(c => c.id == 1);
//查询单条没有记录返回空对象
var single2 = db.queryable().where(c => c.id == 1).singleordefault();
//查询第一条
var first = db.queryable().where(c => c.id == 1).first();
var first2 = db.queryable().where(c => c.id == 1).firstordefault();
//取10-20条
var page1 = db.queryable().where(c => c.id > 10).orderby("id").skip(10).take(20).tolist();
//上一句的简化写法，同样取10-20条
var page2 = db.queryable().where(c => c.id > 10).orderby("id").topagelist(2, 10);
//查询条数
var count = db.queryable().where(c => c.id > 10).count();
//从第2条开始以后取所有
var skip = db.queryable().where(c => c.id > 10).orderby("id").skip(2).tolist();
//取前2条
var take = db.queryable().where(c => c.id > 10).orderby("id").take(2).tolist();
// not like
string conval = "a";
var notlike = db.queryable().where(c => !c.name.contains(conval.tostring())).tolist();
// 可以在拉姆达使用 tostring和 convert,比ef出色的地方
var convert1 = db.queryable().where(c => c.name == "a".tostring()).tolist();
var convert2 = db.queryable().where(c => c.id == convert.toint32("1")).tolist();//
var convert3 = db.queryable().where(c => datetime.now > convert.todatetime("2015-1-1")).tolist();
var convert4 = db.queryable().where(c => datetime.now > datetime.now).tolist();
//支持字符串where 让你解决，更复杂的查询
var student12 = db.queryable().where(c => "a" == "a").where("id>100").tolist();
var student13 = db.queryable().where(c => "a" == "a").where("id>100 and id in( select 1)").tolist();
//存在记录反回true，则否返回false
bool isany100 = db.queryable().any(c => c.id == 100);
bool isany1 = db.queryable().any(c => c.id == 1);
int maxid = db.queryable().max("id");
int minid = db.queryable().where(c => c.id > 0).min("id");
//in
var list1= db.queryable().in("id", "1", "2", "3").tolist();
var list2=db.queryable().in("id", new string[] { "1", "2", "3" }).tolist();
var list3=db.queryable().in("id", new list { "1", "2", "3" }).tolist();
var list4 = db.queryable().where(it=>it.id<10).in("id", new list { "1", "2", "3" }).tolist();
//分组查询
var list5= db.queryable().where(c => c.id < 20).groupby("sex").select("sex,count=count(*)").tolist();
//select sex,count=count(*) from student where 1=1 and (id < 20) group by sex --生成结果
单表查询
3、sqlable 用于多表查询
//---------sqlable,创建多表查询---------//
//多表查询
list datalist = db.sqlable()
.form("school", "s")
.join("student", "st", "st.id", "s.id", jointype.inner)
.join("student", "st2", "st2.id", "st.id", jointype.left)
.where("s.id>100 and s.id<@id")
.where("1=1")//可以多个where
.selecttolist("st.*", new { id = 1 });
//多表分页
list datapagelist = db.sqlable()
.form("school", "s")
.join("student", "st", "st.id", "s.id", jointype.inner)
.join("student", "st2", "st2.id", "st.id", jointype.left)
.where("s.id>100 and s.id<100")
.selecttopagelist("st.*", "s.id", 1, 10);
//多表分页where加子查询
list datapagelist2 = db.sqlable()
.form("school", "s")
.join("student", "st", "st.id", "s.id", jointype.inner)
.join("student", "st2", "st2.id", "st.id", jointype.left)
.where("s.id>100 and s.id<100 and s.id in (select 1 )" /*这里面写子查询都可以*/)
.selecttopagelist("st.*", "s.id", 1, 10);
//--------转成list dynamic 或者 json-----//
//不分页
var list1 = db.sqlable().form("student", "s").join("school", "l", "s.sch_id", "l.id and l.id=@id", jointype.inner).selecttodynamic("*", new { id = 1 });
var list2 = db.sqlable().form("student", "s").join("school", "l", "s.sch_id", "l.id and l.id=@id", jointype.inner).selecttojson("*", new { id = 1 });
var list3 = db.sqlable().form("student", "s").join("school", "l", "s.sch_id", "l.id and l.id=@id", jointype.inner).selecttodatatable("*", new { id = 1 });
//分页
var list4 = db.sqlable().form("student", "s").join("school", "l", "s.sch_id", "l.id and l.id=@id", jointype.inner).selecttopagedynamic("s.*", "l.id", 1, 10, new { id = 1 });
var list5 = db.sqlable().form("student", "s").join("school", "l", "s.sch_id", "l.id and l.id=@id", jointype.inner).selecttopagetable("s.*", "l.id", 1, 10, new { id = 1 });
var list6 = db.sqlable().form("student", "s").join("school", "l", "s.sch_id", "l.id and l.id=@id", jointype.inner).selecttopagedynamic("s.*", "l.id", 1, 10, new { id = 1 });
//--------拼接-----//
sqlable sable = db.sqlable().form("s").join("l", "s.sch_id", "l.id", jointype.inner);
string name = "a";
int id = 1;
if (!string.isnullorempty(name))
{
sable = sable.where("s.name=@name");
}
if (!string.isnullorempty(name))
{
sable = sable.where("s.id=@id or s.id=100");
}
if (id > 0)
{
sable = sable.where("l.id in (select top 10 id from school)");//where加子查询
}
var pars = new { id = id, name = name };
int pagecount = sable.count(pars);
var list7 = sable.selecttopagelist("s.*","l.id desc",1,20, pars);
多表查询
4、新容器转换
using (sqlsugarclient db = sugardao.getinstance())
{
var list2= db.queryable().where(c => c.id < 10).select(c => new classnew { newid = c.id, newname = c.name, xx_name = c.name }).tolist();//不支持匿名类转换,也不建议使用
var list3 = db.queryable().where(c => c.id < 10).select(c => new { newid = c.id, newname = c.name, xx_name = c.name }).todynamic();//匿名类转换
}
新容器转换
添加
using (sqlsugarclient db = sugardao.getinstance())//开启数据库连接
{
student s = new student()
{
name = "张" + new random().next(1, int.maxvalue)
};
db.insert(s); //插入一条记录 (有主键也好，没主键也好，有自增列也好都可以插进去)
list list = new list()
{
new student()
{
name="张"+new random().next(1,int.maxvalue)
},
new student()
{
name="张"+new random().next(1,int.maxvalue)
}
};
db.insertrange(list); //批量插入
}
}
添加
更新
using (var db = sugardao.getinstance())
{
//指定列更新
db.update(new { name = "蓝翔2" }, it => it.id == id);
db.update(new { name = "蓝翔2" }, 1, 3, 12);
db.update(new { name = "蓝翔2" }, new string[] { "1", "2" });
//整个实体更新
db.update(new school { id = id, name = "蓝翔2" });
db.update(new school { id = id, name = "蓝翔2" }, it => it.id == id);
//设置不更新列
db.disableupdatecolumns = new string[] { "createtime" };//设置createtime不更新
testupdatecolumns updobj = new testupdatecolumns()
{
vguid = guid.parse("542b5a27-6984-47c7-a8ee-359e483c8470"),
name = "xx",
name2 = "xx2",
identityfield = 0,
createtime = null
};
//createtime将不会被更新
db.update(updobj);
//以前实现这种更新需要用指定列的方式实现，现在就简单多了。
}
更新
删除和假删除
using (var db = sugardao.getinstance())
{
//真删除
db.delete(10);//注意主键必需为实体类的第一个属性
db.delete(it => it.id > 100);
db.delete(new string[] { "100", "101", "102" });
//假删除
//db.falsedelete("is_del", 100);
//等同于 update school set is_del=0 where id in(100)
//db.falsedelete("is_del", it=>it.id==100);
}
删除
全局过滤器
这功能主要用于通用权限设计等功能， 当有很多where重复，并且动态 这时候就需要在全局设置好过滤器接口集合，程序中使用只需要设key便可
///
/// 过滤器
///
public partial class filter : system.web.ui.page
{
protected void page_load(object sender, eventargs e)
{
using (sqlsugarclient db = sugardaofilter.getinstance())//开启数据库连接
{
//设置走哪个过滤器
db.currentfilterkey = "role";
//queryable
var list = db.queryable().tolist(); //通过全局过滤器对需要权限验证的数据进行过滤
//相当于db.queryable().where("id=@id",new{id=1})
//sqlable
var list2 = db.sqlable().form("s").selecttolist("*");
//同上
//sqlquery
var list3 = db.sqlquery("select * from student");
//同上
}
}
}
///
/// 扩展sqlsugarclient
///
public class sugardaofilter
{
//禁止实例化
private sugardaofilter()
{
}
///
/// 页面所需要的过滤函数
///
private static dictionary> _filterparas = new dictionary>()
{
{ "role",()=>{
return new keyvalueobj(){ key=" id=@id" , value=new{ id=1}};
}
},
{ "org",()=>{
return new keyvalueobj(){ key=" orgid=@orgid" , value=new{ orgid=1}};
}
},
};
public static sqlsugarclient getinstance()
{
string connection = system.configuration.configurationmanager.connectionstrings[@"sqlconn"].tostring(); //这里可以动态根据cookies或session实现多库切换
var reval = new sqlsugarclient(connection);
reval.setfilterfilterparas(_filterparas);
return reval;
}
}
过滤器
无参模式
将isgetpageparas设为true之后页面的参数都不需要使用 new{}来赋值
using (sqlsugarclient db = sugardao.getinstance())//开启数据库连接
{
db.isgetpageparas = true;//使用无参模式直接将requst中的id传给@id无需在代码中写出来
var list = db.queryable().where("id=@id").tolist();
//sqlable queryable sqlquery通用
//获取页面所有参数到键值对
//var kvs= sqlsugartool.getparameterdictionary();
//以前写法
//var xx = db.queryable().where("id=@id", new { id=request["id"] }).tolist();
}
无参
事务
using (sqlsugarclient db = sugardao.getinstance())//开启数据库连接
{
db.isnolock = true;//启用无锁查询
db.commandtimeout = 30000;//设置超时时间
try
{
db.begintran();//开启事务
db.committran();//提交事务
}
catch (exception)
{
db.rollbacktran();//回滚事务
throw;
}
}
事务
公开函数
//tojoinsqlinval
var par = new string[] { "a", "c", "3" };
var ids = par.tojoinsqlinval();//将数组转成 'a','c','3' 有防sql注入处理
//tosqlvalue
try
{
var par2 = "a'";
var newpar2 = par2.tosqlvalue();//对字符串防注入处理
}
catch (exception ex)
{
response.write(ex.message);
}
//sqllikewordencode 处理like特殊字符
var likestr = sqlsugartool.sqllikewordencode("[%%%");
//getparameterarray 获取页面参数所有参数的键和值
var pars = sqlsugartool.getparameterarray();
//将匿名对象转成sqlparameter
var par3= sqlsugartool.getparameters(new { id=1});
函数
实体生成
//根据当前数据库生成所有表的实体类文件 （参数：sqlsugarclient ，文件目录，命名空间）
//db.classgenerating.createclassfiles(db,server.mappath("~/models"),"models");
//根据表名生成实体类文件
//db.classgenerating.createclassfilesbytablenames(db, server.mappath("~/models"), "models" , "student","school");
//根据表名生成class字符串
var str = db.classgenerating.tablenametoclass(db, "student");
//根据sql语句生成class字符串
var str2 = db.classgenerating.sqltoclass(db, "select top 1 * from student", "student");
实体生成
t4模版
请查看demos文件下的t4
别名表
//-----单个-------//
var list = db.queryable("student").tolist();//查询的是 select * from student 而我的实体名称为v_student
//-----全局多个设置-------//
//设置 mapping table 如果没这方面需求可以传null
list mappingtablelist = new list(){
new keyvalue(){ key="formattr", value="flow_formattr"},
new keyvalue(){ key="student3", value="student"}
};
db.setmappingtables(mappingtablelist);
别名表
多库并行计算功能
using (cloudclient db = clouddao.getinstance())
{
db.pagemaxhandlenumber = 1500;
using (committabletransaction trans = new committabletransaction())//启用分布式事务
{
db.tran = trans;
/*** 增、删、改 ***/
//根据配置的百分比，随机插入到某个节点库
var s = new student()
{
createtime = datetime.now,
id = guid.newguid(),
name = guid.newguid() + ""
};
//db.insert(s, false/*false表示不是自增列*/); //数据库设计主键为guid为佳多库计算不会冲突
////并发请求所有节点找出这条数据更新
//s.name = "改11";
//db.update(s, it => it.id == s.id);//根据表达示更新
//db.update(s, s.id);//根据主键更新
//db.update(s, new guid[] { s.id });//根据主键数组更新
////并发请求所有节点找出这条数据删除
//db.delete(it => it.id == s.id);
trans.commit();
}
db.trandispose();
/*** 使用taskable实现云计算 ***/
//多线程请求所有数据库节点，获取汇总结果
var taskdatatable = db.taskable("select max(num) from student").tasks;
foreach (var dr in taskdatatable)//遍历所有节点数据
{
var dt = dr.result.datatable;//每个节点的数据 类型为datatable
var connectionname = dr.result.connectionstring;
}
//多线程请求所有数据库节点，获取汇总结果
var taskint = db.taskable("select max(num) from student").tasks;
foreach (var dr in taskint)//遍历所有节点数据
{
var dt = dr.result.value;//获取单个值
var connectionname = dr.result.connectionstring;
}
//多线程请求所有数据库节点，获取汇总结果
var taskentity = db.taskable("select top 100 * from student").tasks;
foreach (var dr in taskentity)//遍历所有节点数据
{
var dt = dr.result.entities;//获取实体集合
var connectionname = dr.result.connectionstring;
}
// //taskable实现分组查询
// var grouplist = db.taskable("select name,count(*) as [count],avg(num) as num from student where id is not null group by name ")
// .tasks
// .selectmany(it => it.result.datatable.asenumerable())
// .select(dr => new { num = convert.toint32(dr["num"]), name = dr["name"].tostring(), count = convert.toint32(dr["count"]) })
// .groupby(dr => dr.name).select(dt => new { name = dt.first().name, count = dt.sum(dtitem => dtitem.count), num = dt.sum(dtitem => dtitem.num) / dt.sum(dtitem => dtitem.count) }).tolist();
// //输出结果
// foreach (var it in grouplist)
// {
// var num = it.num;
// var name = it.name;
// var count = it.count;
// }
// //简化
// var grouplist2 = db.taskable("select name,count(*) as [count],avg(num) as num from student where id is not null group by name ")
//.mergetable()//将结果集合并到一个集合
//.select(dr => new { num = convert.toint32(dr["num"]), name = dr["name"].tostring(), count = convert.toint32(dr["count"]) })
//.groupby(dr => dr.name).select(dt => new { name = dt.first().name, count = dt.sum(dtitem => dtitem.count), num = dt.sum(dtitem => dtitem.num) / dt.sum(dtitem => dtitem.count) }).tolist();
// //再简化
// list grouplist3 = db.taskable("select name,count(*) as [count],avg(num) as num from student where id is not null group by name ")
//.mergeentities()//将结果集合并到一个集合
//.groupby(dr => dr.name).select(dt => new v_student { name = dt.first().name, count = dt.sum(dtitem => dtitem.count), num = dt.sum(dtitem => dtitem.num) / dt.sum(dtitem => dtitem.count) }).tolist();
//更多简化函数
int maxvalue = db.taskable("select max(num) from student").max();//求出所有节点的最大值
int minvalue = db.taskable("select min(num) from student").min();//求出所有节点的最小值
var datacount = db.taskable("select count(1) from student").count();//求出所有节点数据
// var avg = db.taskablewithcount("select avg(num)", " from student").avg();
var all = db.taskable("select * from student where contains(name,@name)"/*使用contains需要建全文索引*/, new { name = "张三" }).tolist();//获取所有节点name为张三的数据,转为list
var dnsingle = datetime.now;
var data = db.taskable("select * from student where id=@id", new { id = id }).tosingle();//从所有节点中查询一条记录
var timesingle = (datetime.now - dnsingle).totalseconds;
var dn = datetime.now;
//单列排序
// var list = db.taskablewithpage("id", "select * from student "/*使用contains需要建全文索引*/, 2160, 25, ref pagecount, "num", orderbytype.desc, new { name = "\"*李*\"" });
var time = (datetime.now - dn).totalseconds;
//多组排序 order by num,time
var dn2 = datetime.now;
var listmultipleorderby = db.taskablewithpage("id", "select * from student "/*使用contains需要建全文索引*/, 5000, 25, ref pagecount, new list() {
new orderbydictionary(){ orderbyfield="num", orderbytype=orderbytype.asc},
// new orderbydictionary(){ orderbyfield="createtime", orderbytype=orderbytype.desc},
// new orderbydictionary(){ orderbyfield="createtime", orderbytype=orderbytype.asc}
}, new { name = "\"*小*\"" });
var time2 = (datetime.now - dn2).totalseconds;
}
多库模式
///
/// 测试类
///
public class student
{
public guid id { get; set; }
public string name { get; set; }
public datetime createtime { get; set; }
public int num { get; set; }
}
///
/// 获取数据库连接对象
///
public class clouddao
{
private clouddao() { }
public static cloudclient getinstance()
{
return new cloudclient(new list() {
new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c1"].tostring(), rate=1},
new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c2"].tostring(), rate=1},
new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c3"].tostring(), rate=1},
new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c4"].tostring(), rate=1},
new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c5"].tostring(), rate=1},
new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c6"].tostring(), rate=1},
new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c7"].tostring(), rate=1},
new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c8"].tostring(), rate=1},
new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c9"].tostring(), rate=1},
new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c10"].tostring(), rate=1},
new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c15"].tostring(), rate=1},
new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c16"].tostring(), rate=1},
new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["c17"].tostring(), rate=1},
//new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["cy1"].tostring(), rate=1},
// new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["cy2"].tostring(), rate=1},
// new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["cy3"].tostring(), rate=1},
// new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["zq1"].tostring(), rate=1},
// new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["zq2"].tostring(), rate=1},
// new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["zq3"].tostring(), rate=1},
// new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["qp1"].tostring(), rate=1},
// new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["qp2"].tostring(), rate=1},
// new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["qp3"].tostring(), rate=1},
// new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["wy1"].tostring(), rate=1},
// new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["wy2"].tostring(), rate=1},
// new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["wy3"].tostring(), rate=1},
// new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["wwf1"].tostring(), rate=1},
// new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["wwf2"].tostring(), rate=1},
// new cloudconnectionconfig(){ connectionstring=configurationmanager.connectionstrings["wwf3"].tostring(), rate=1},
});
}
}
多库配置