5.1.4.69
答:用来兼容多种数据库的弱类型操作数据库语法
可以在这个基础上,封装到XML等配置 ( 自定义XML格式 )
//聚合过滤 ISugarQueryable<T> Having(IFuncModel model); //排序 ISugarQueryable<T> OrderBy(List<OrderByModel> models); //分组 ISugarQueryable<T> GroupBy(List<GroupByModel> models); //查询列 ISugarQueryable<T> Select(List<SelectModel> models); //表名 ISugarQueryable<T> AS(string tableName); //表名+别名 ISugarQueryable<T> AS(string tableName, string shortName); //联表查询 ISugarQueryable<T> AddJoinInfo(string tableName, string shortName, IFuncModel models, JoinType type = JoinType.Left); //联表查询 集合参数 ISugarQueryable<T> AddJoinInfo(List<JoinInfoParameter> joinInfoParameters); //查询条件 ISugarQueryable<T> Where(List<IConditionalModel> conditionalModels); //查询条件外面包一层 ISugarQueryable<T> Where(List<IConditionalModel> conditionalModels,bool isWrap); //查询条件根据函数 ISugarQueryable<T> Where(IFuncModel model); //上面的API都是支持多库,不支持多库的可以看下面的链接 https://www.donet5.com/Home/Doc?typeId=2313
支持多库,适合代码封装
//方式1:函数实现 db.Queryable<object>().AS("order", "o") .AddJoinInfo("orderdetail","d",ObjectFuncModel.Create("Equals", "d.orderid", "o.id"),JoinType.Left) .Select(new List<SelectModel>(){ new SelectModel() { AsName = "id", FiledName = "o.id" } }) .ToList(); //生成Sql如下 //SELECT `o`.`id` AS `id` FROM `Order` o Left JOIN `orderdetail` d ON (`d`.`orderid` = `o`.`id`) //方式2:多个条件 var onList=ObjectFuncModel.Create("Format", "d.orderid", ">", "o.id", "&&", "o.id", ">", "{int}:1"); var list= db.Queryable<object>() .AS("order", "o") .AddJoinInfo("orderdetail", "d", onList, JoinType.Left) .Select(SelectModel.Create( new SelectModel() { FiledName="o.id" , AsName="id" } )) .ToList(); //SELECT `o`.`id` AS `id` FROM `order` o //Left JOIN `orderdetail` d ON `d`.`orderid` > `o`.`id` And `o`.`id` > @p0 //拼接符有: ">", ">=", "<", "<=", "(", ")", "=", "||", "&&","&","|","null","is","isnot","like", //"nolike","+","-","*","/","%" //拼接里面可能使用函数:"{SqlFunc_AggregateMin:["id"]}"
就是写SQL,不适合代码封装 ,复杂的多库兼容差
var list=db.Queryable<object>().AS("order", "o") .AddJoinInfo("table1", "a", "a.id=o.id and a.id>1", JoinType.Left) .Select("a.*").ToList();
适合产品级多库使用,支持多库
List<OrderByModel> orderList = OrderByModel.Create ( new OrderByModel(){ FieldName="id",OrderByType=OrderByType.Desc}, new OrderByModel(){FieldName="name"}//用到函数看标题6 ); var list=db.Queryable<object>().AS("order").OrderBy(orderList).ToList(); //SELECT * FROM `Order` ORDER BY `id` DESC , `name` ASC
直接写SQL,如果用到函数就不支持多库了
var list=db.Queryable<object>().AS("order").OrderBy("id desc,name asc").ToList();
//方式1:多库兼容 var groupList=GroupByModel.Create(new GroupByModel() { FieldName="ID" }); //Sql: `id` var having=ObjectFuncModel.Create("GreaterThan", ObjectFuncModel.Create("AggregateAvg", "ID"), "{int}:1"); var list=db.Queryable<object>().AS("order").GroupBy(groupList) .Having(having) .Select("ID").ToList() //SELECT ID FROM `Order` GROUP BY `id` HAVING (AVG(`ID`) > @p0) //方式2: 直接写SQL var list=db.Queryable<object>().AS("order").GroupBy("id") .Having("avg(id)>@p") .AddParameters(new {p=1}) .Select("ID").ToList()
//方式1:多库兼容 var selector= new List<SelectModel>() { new SelectModel(){AsName = "id1",FiledName = "id"}, new SelectModel(){ FiledName = "id"}}; var list=db.Queryable<object>().AS("order").Select(selector).ToList(); //SELECT `id` AS `id1` , `id` AS `id` FROM `Order` //列不是字段用法 int num= 0; var selectList=SelectModel.Create(new SelectModel(){AsName="Id",FiledName="{long}:"+num}); var list= db.Queryable<Order>().Select().ToList(); //SELECT @p AS [Id] FROM [Order] //@p=0 Dbtype: int64 //函数用法 var selectItem= new SelectModel() { AsName = "Id", FiledName =ObjectFuncModel.Create("ToInt64","Id") }; var list= db.Queryable<Order>().Select(new List<SelectModel> { selectItem}).ToList(); //SELECT CAST([Id] AS BIGINT) AS [Id] FROM [Order] //方式2:直接写SQL var list=db.Queryable<object>().AS("order").Select("ID AS id1,id as id").ToList();
第一个参数为方法名,其它参数都是函数的参数
支持的函数大致上和SqlFunc.xxx 相同 (文档:查询函数有介绍)
ObjectFuncModel.Create("函数名",参数1,参数2 ....)
函数的参数可以是3种类型:字段名,变量,函数
具体有哪些函数,看文档:查询函数和表达式中用的函数是一致的
/***字段参数***/ ObjectFuncModel.Create("ToInt64","Id") //sql: Cast( [id] As BigInt) /***变量参数***/ ObjectFuncModel.Create("ToInt64","{int}:1") //sql: Cast( @p As BigInt) ; @p=1 DbType=Int /***函数参数***/ ObjectFuncModel.Create("ToInt64", ObjectFuncModel.Create("ToInt64","Id")) //sql: Cast( Cast( Id As BigInt) As BigInt); /***综合用例***/ ObjectFuncModel.Create("GreaterThan", ObjectFuncModel.Create("AggregateAvg", "ID"), "{int}:1") //用到了SqlFunc.GreaterThan嵌套SqlFunc.AggregateAvg函数 //sql: avg([id])>@p (其中@p=1 并且 Dbtype=int) //可以使用的地方 // 带IFuncModel重载的方法, 比如 Having(IFuncModel model) 和 AddJoinInfo(+4重载) // 带有SelectModel重载方法 new SelectModel(){ FiledName=FuncModel} // 带有OrderByModel重载方法 new OrderByModel(){ FiledName=FuncModel}
Parameters是函数的参数他是一个数组
string[]{"id","{int}:1"} //等于: //("id",@p) @p=1
总结: 参数可以是 函数、变量 、字段
如果符号提示没有,请升最新预览版本
//id+num var onList=ObjectFuncModel.Create("Format","id","+","num");//Format表示函数名 //id+@p ,new {p=1} var onList=ObjectFuncModel.Create("Format","id","+","{int}:1");//Format表示函数名 //拼接符有: ">", ">=", "<", "<=", "(", ")", "=", "||", "&&","&","|","null","is","isnot","like", //"nolike","+","-","*","/","%" //可以使用的地方 // 带IFuncModel重载的方法, 比如 Having(IFuncModel model) 和 AddJoinInfo(+4重载) // 带有SelectModel重载方法 new SelectModel(){ FiledName=FuncModel} // 带有OrderByModel重载方法 new OrderByModel(){ FiledName=FuncModel}
支持多库 ,也方便构造或者前端序列化 ,手写硬编码逻辑比较吃力,代码量多,适合封装使用
//手动构造 var conModels = new List<IConditionalModel>(); conModels.Add(new ConditionalModel{ FieldName = "id",ConditionalType=ConditionalType.Equal,FieldValue="1"}); conModels.Add(new ConditionalModel{ FieldName = "name",ConditionalType=ConditionalType.Equal,FieldValue="jack"}); //可以设置类型 CSharpTypeName="int" var student = db.Queryable<Student>().Where(conModels).ToList(); //select * from Student where id=1 and name = 'jack' //详细用法:https://www.donet5.com/Home/Doc?typeId=2314
用起来灵活也支持多库,不方便封装,适合手写
var whereFunc = ObjectFuncModel.Create("Format", "id", ">", "{int}:1", "&&", "name", "=", "{string}:a"); db.Queryable<object>().AS("Order").Where(whereFunc).ToList(); //生成的Sql //SELECT * FROM [Order] WHERE [id] > @p0 AND [name] = @p1 参数说明: (1)变量 {string}:a 等于 @p ,new {p=a.ToString()} (2)拼接符号 ">", ">=", "<", "<=", "(", ")", "=", "||", "&&","&","|","null","is","isnot","like","nolike", "+","-","*","/","%" (3)函数 {SqlFunc_AggregateMin:["id"]} (4)字段 不是上面3种就认为是字段(会进行严格SQL注入验证) id 就会变成 "id" 或者 [id] 或者 `id`
用起来简单 对多库兼容差
db.Queryable<object>().AS("Order").Where("id=@id".new {id=1}).ToList();
我们同样可以使用MappingColumn函数实现嵌套Sql
//考虑到安全需要配置开启MappingColumn DbType = SqlSugar.DbType.SqlServer, ConnectionString = Config.ConnectionString, MoreSettings=new ConnMoreSettings() { EnableModelFuncMappingColumn=true//这一句 }, //用例 var selector=SelectModel.Create( new SelectModel() { FiledName =ObjectFuncModel.Create("MappingColumn", "(select 1 as id)"), AsName = "id" }); db.Queryable<Order>().Select(selector).ToList();
2016 © donet5.comApache Licence 2.0