答:用来兼容多种数据库的弱类型操作数据库语法
可以在这个基础上,封装到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
下面2种方式都支持无代码查询的相关API
//这种是动态类模式,支持AOP 导航等功能(类不存在看文档:动态建类 ) db.QueryableByObject(typeof(Order), "x").ToList(); //这种是无实体方式,就是我们不去创建类 ,缺失些功能比如导航 AOP db.Queryable<object>().AS("order", "o").ToList();
支持多库,适合代码封装
//方式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}
所有函数 :用法和sqlfunc类似
升级到5.1.4.113-preview15+
//哪个方法不会写加QQ咨询610262374 Format //重要:用于拼接SQL 看标题6.4用法 JsonIndex CharIndexNew WeekOfYear DateValue HasValue JsonField JsonListObjectAny JsonArrayAny TrimEnd TrimStart PadLeft NewUid FullTextContains RowNumber //用法:https://www.donet5.com/Ask/9/28715 RowCount RowSum RowAvg RowMin RowMax IIF //条件 CASE WHEN ([UserId] = @p0 ) THEN [UserId] ELSE @p1 END new ObjectFuncModel() { FuncName = "IIF", Parameters = new List<object>{ new ObjectFuncModel(){ FuncName="Equals",//嵌套了一个等于函数 Parameters=new List<object>{ "UserId", "{int}:0" } },"UserId","{int}:1" } } IsNullOrEmpty HasNumber ToUpper ToLower Trim Contains //like ContainsArray //UserId in(1,2,3) 升级到:5.1.4.135-preview07+ var whereObj=ObjectFuncModel.Create("ContainsArray", "{int}:" + db.Utilities.SerializeObject(new int[] { 1, 2 ,3}),"UserId") //UserId not in(1,2,3) IFuncModel func = ObjectFuncModel.Create("ContainsArray", "{int}:[1,2,3]", "userid"); func = ObjectFuncModel.Create("format","not", "(", func, ")"); ContainsArrayUseSqlParameters Equals EqualsNull DateIsSameDay//是否同一天 下面是示例 var whereObj= ObjectFuncModel.Create("DateIsSameDay", "CreateTime", "{DateTime}:2020-1-1"); DateIsSameByType//同月、天、同年等 (升级到5.1.4.113-preview15+) var whereObj2 = ObjectFuncModel.Create("DateIsSameByType", "CreateTime", "{DateTime}:2020-1-1", "{string}:" + DateType.Month ); DateAddByType DateAddDay Between StartsWith EndsWith ToInt32 ToInt64 ToString ToGuid ToDouble ToBool ToDate ToDateShort ToTime ToDecimal Substring Length Replace AggregateSum AggregateAvg AggregateMin AggregateMax AggregateCount AggregateDistinctCount MappingColumn IsNull GuidNew GetSelfAndAutoFill MergeString GetDate GetRandom CaseWhen CharIndex ToVarchar BitwiseAnd BitwiseInclusiveOR Oracle_ToDate Oracle_ToChar SqlServer_DateDiff FormatRowNumber Format Abs Round DateDiff GreaterThan GreaterThanOrEqual LessThan LessThanOrEqual Asc Desc Stuff Exists GetDateString JsonContainsFieldName JsonArrayLength JsonParse JsonLike Collate AggregateSumNoNull AggregateAvgNoNull CompareTo SplitIn Like ToSingle ListAny ListAll Modulo Left Right Floor Ceil ToString Equals //特殊函数 Case when 用法: https://www.donet5.com/Ask/9/27019
哪个方法不会写加QQ咨询610262374
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 model=new SelectModel() { FiledName=ObjectFuncModel.Create("MappingColumn","(select 1 as id)"), AsName = "id" } var selector=SelectModel.Create(model); db.Queryable<Order>().Select(selector).ToList();
2016 © donet5.comApache Licence 2.0