C# LLSQL快速查询框架

介绍一种新类型查询方法,类似linq,lambda语法,类似标准的sql使用习惯,支持匿名类型,泛型,目前支持mssql,mysql,

切换只需要DatabaseConfig.DatabaseType = DatabaseType.SQLServer;无需改任何代码,dll后续开放下载

使用说明:基于实体查询,实体名和表名相同,实体需要使用代码生成器生成,工具后续开放下载

只需要生成所有表的实体,其它数据任意查,不需要手写任何Model, 

查询结果

1   public class Student2   {3     public int ID { get; set; }4     public string Name { get; set; }5     public int Age { get; set; }6     public bool IsGraduate { get; set; }7     public string Grade { get; set; }8     public decimal Money { get; set; }9   }

 1     /// <summary> 2     /// 单表查询 3     /// </summary> 4     public static void single_able() 5     { 6       using (var sql = new SqlModel<T_BAS_Areas>()) 7       { 8         //!+常用方法 9         /* 10           *使用说明          11           *数据查询方法,只有调用后才会有数据返回 12           *ToDataTable(),ToList(),ToPageList(),First(),FirstOrDefault() 13           */ 14         //?查默认一条数据 15         var data1 = sql 16           .SqlSelect(a => new { a.AreaName, a.AreaCode }) 17           .FirstOrDefault(); 18         //?所有字段查询 19         var data1_1 = sql 20           .SqlSelect() 21           .FirstOrDefault(); 22         //查询top 10 23         var data1_2 = sql 24           .SqlSelect() 25           .SqlTop(10) 26           .ToList(); 27         //?DataTable 28         var data2 = sql 29           .SqlSelect(a => new { a.AreaName, a.AreaCode }) 30           .ToDataTable(); 31         //?List 32         var data3 = sql 33           .SqlSelect(a => new { a.AreaName, a.AreaCode }) 34           .ToList(); 35         //?分页 36         var data4 = sql 37           .SqlPage(a => new { a.AreaName, a.AreaCode }) 38           .ToPageList(); 39         var data4_1 = sql 40           .SqlPage(a => new { a.AreaName, a.AreaCode }, 1, 20) 41           .ToPageList(); 42         //?count 43         var data5 = sql 44           .SqlCount(a => a.AreaCode == ""); 45         //?sum 46         var data6 = sql 47           .SqlSum(a => new { a.AreaCode }) 48           .First(); 49  50         //?无锁查询 51         var data7 = sql 52           .SqlSelect(a => new { a.AreaName, a.AreaCode }, LockType.NOLOCK) 53           .FirstOrDefault(); 54         //?指定索引查询 55         var data8 = sql 56           .SqlSelect(a => new { a.AreaName, a.AreaCode } 57           , "PK_T_BAS_AREAS") 58           .FirstOrDefault(); 59  60         //?条件查询 61         var data9 = sql 62           .SqlSelect(a => new { a.AreaName, a.AreaCode }) 63           .SqlWhere(a => a.ParentAreaCode == "1251" && a.AreaName.SqlLike("九江")) 64           .FirstOrDefault(); 65         //? 查前10条并排序 66         var data10 = sql 67           .SqlSelect(a => new { a.AreaName, a.ParentAreaCode, a.AreaCode }) 68           .SqlTop(10) 69           .SqlOrderBy(b => new { b.ParentAreaCode, b.AreaCode }) 70           .ToList(); 71         var data11 = sql 72           .SqlSelect(a => new { a.AreaName, a.ParentAreaCode, a.AreaCode }) 73           .SqlTop(10) 74           .SqlOrderDescBy(b => new { b.ParentAreaCode, b.AreaCode }) 75           .ToList(); 76         //? 查前10%条并排序 77         var data12 = sql 78           .SqlSelect(a => new { a.AreaName, a.ParentAreaCode, a.AreaCode }) 79           .SqlPercent(10).SqlOrderBy(b => new { b.ParentAreaCode }).ToList(); 80         data12 = sql.SqlSelect(a => new { a.AreaName, a.ParentAreaCode, a.AreaCode }) 81           .SqlPercent(10) 82           .SqlOrderDescBy(b => new { b.ParentAreaCode }) 83           .SqlOrderBy(c => new { c.AreaCode }) 84           .ToList(); 85  86         //?查前10条不重复的项 87         var data13 = sql 88           .SqlSelect(a => new { a.ParentAreaCode, a.AreaCode }) 89           .SqlDistinct() 90           .SqlTop(10) 91           .ToList(); 92  93         //!更新 94         sql.SqlUpdate(a => new object[] { a.AreaName == "溪湖区" },//a.SqlFunc("AreaCode='2'") 95           b => b.AreaGuidGuid == Guid.Parse("949B2F9D-F730-48ED-8B58-000144166BE9")); 96  97  98         //!添加 99         T_BAS_Areas area = new T_BAS_Areas()100         {101           AreaGuidGuid = Guid.NewGuid(),102           AreaCode = "1",103           AreaName = "测试",104           ParentAreaCode = "-1",105         };106         sql.SqlAdd(area);107         //!删除108         sql.SqlDelete(a => a.AreaGuidGuid == area.AreaGuidGuid);109 110         //where 查询111         var data14 = sql112           .SqlSelect(a => new { a.AreaName, a.AreaCode })113           .SqlWhere(a => a.AreaName.SqlLike("六一") && a.AreaCode.SqlFunc("AreaCode=1"))114           .ToList();115         var data141 = sql116           .SqlSelect(a => new { a.AreaName, a.AreaCode })117           .SqlWhere(a => a.AreaName.SqlLike("%{0}?", "六一")118             && a.AreaCode.SqlFunc("AreaCode='1'")//a.SqlFunc("AreaCode='1'")119             || a.AreaCode.SqlFunc("AreaCode=Parent"))120             .ToList();121       }122     }

 1     /// <summary> 2     /// 多表查询 3     /// </summary> 4     public static void multilist_table() 5     { 6  7       using (var sql = new SqlModel<T_ST_Role, T_ST_Role_Permission>()) 8       { 9         int count;10         var left = sql.SqlSelect((a, b) => new { NameTest = a.RoleName, b.PermissionCode })11               .SqlJionLeft((a, b) => b)12                   .SqlOn((a, b) => a.RoleGuid == b.RoleGuid)13                   .SqlOrderBy((a, b) => b.PermissionCode)14                   .SqlWhere((a, b) => a.RoleCode == "1")15                   .ToList();16         count = left.Count;17 18         var right = sql.SqlSelect((a, b) => new { NameTest = a.RoleName, b.PermissionCode })19               .SqlJionRight((a, b) => b)20                   .SqlOn((a, b) => a.RoleGuid == b.RoleGuid)21                   .ToList();22 23         var full = sql.SqlSelect((a, b) => new { NameTest = a.RoleName, b.PermissionCode, })24               .SqlJionFull((a, b) => b)25                   .SqlOn((a, b) => a.RoleGuid == b.RoleGuid)26                   .ToList();27 28         var inner = sql.SqlSelect((a, b) => new { NameTest = a.RoleName, b.PermissionCode })29               .SqlJionInner((a, b) => b)30                   .SqlOn((a, b) => a.RoleGuid == b.RoleGuid)31                   .ToList();32 33         var page = sql.SqlPage((a, b) => new { a.RoleGuid, NameTest = a.RoleName, b.PermissionCode }, 1, 20)34               .SqlJionLeft((a, b) => b).SqlOn((a, b) => a.RoleGuid == b.RoleGuid)35                 .SqlWhere((a, b) => a.RoleCode == "R000001")36                 .SqlOrderBy((a, b) => b.PermissionCode)//.SqlOrderBy(o => new { o.PermissionCode })37                 .ToPageList();38 39       }40 41       using (var sql = new SqlModel<T_ST_Role, T_ST_Role_Permission, T_ST_Permission, T_ST_User_Role>())42       {43         var data = sql.SqlSelect((a, b, c, d) => new { a.RoleGuid, a.RoleName, b.Role_PermissionGuid, c.PermissionCode })44               .SqlJionLeft((a, b, c, d) => b).SqlOn((a, b, c, d) => a.RoleGuid == b.RoleGuid)45               .SqlJionRight((a, b, c, d) => c).SqlOn((a, b, c, d) => b.PermissionGuid == c.PermissionGuid)46               .SqlJionLeft((a, b, c, d) => d).SqlOn((a, b, c, d) => a.RoleGuid == d.RoleGuid)47                   .ToList();48 49         var page = sql.SqlPage((a, b, c, d) => new { a.RoleGuid, a.RoleName, b.Role_PermissionGuid, c.PermissionCode })50             .SqlJionLeft((a, b, c, d) => b).SqlOn((a, b, c, d) => a.RoleGuid == b.RoleGuid)51             .SqlJionRight((a, b, c, d) => c).SqlOn((a, b, c, d) => b.PermissionGuid == c.PermissionGuid)52               .SqlJionLeft((a, b, c, d) => d).SqlOn((a, b, c, d) => a.RoleGuid == d.RoleGuid)53                 .ToPageList();54       }55     }

 1     /// <summary> 2     /// groupby case when then 3     /// </summary> 4     public static void groupby_casewhenthe() 5     { 6       using (var sql = new SqlModel<Students>()) 7       { 8         //case when then查询方式1 9         //CASE WHEN age=16 THEN '16岁' WHEN age=18 THEN '18岁' WHEN age=20 THEN '20岁'else '可选' END AS remark10         var casewh = sql11           .SqlNewSelect(a =>12             new13             {14               a.Name,15               Remark = a.CaseWhen(b => b.Age == 16).Then("16岁").When(b => b.Age == 18).Then("18岁").When(b => b.Age == 20).Then("20岁").Else("其它").End()16             }).First();17         //case when then查询方式2 多条件选择18         //CASE Age WHEN 16 THEN '16岁' WHEN 18 THEN '18岁' WHEN 20 THEN '20岁'else '可选' END AS remark19         var casewh2 = sql20           .SqlNewSelect(a =>21             new22             {23               a.Name,24               Remark = a.Case(a.Age).When(16).Then("16岁").When(18).Then("18岁").When(20).Then("20岁").Else("其它").End()25             }).First();26         //group by查询27         var fun = sql28           .SqlNewSelect(a => new29           {30             a.Name,31             asName = a.Name.SqlCount(),32             asAge = a.Age.SqlSum(),33             asMaxAge = a.Age.SqlMax(),34             asMinAge = a.Age.SqlMin(),35             asAvg = a.Age.SqlAVG()36           })37           .SqlWhere(a => a.Age == 14)38           .SqlGroupBy(a => new { a.Name, a.Age })39           .SqlHaving(a => a.Name == "name_1500081")40           .SqlOrderBy(a => a.Name).First();41 42         Console.WriteLine(fun.asMaxAge + casewh.Name);43       }44     }

原标题：C# LLSQL快速查询框架

关键词：C#