C# LLSQL快速查询框架_.NET_编程开发_程序员俱乐部

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

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

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

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

查询结果

class="code_img_closed" src="/Upload/Images/2016060405/0015B68B3C38AA5B.gif" alt="" />logs_code_hide('8f26c9cd-4b3f-45c7-8792-f809e259f176',event)" src="/Upload/Images/2016060405/2B1B950FA3DF188F.gif" alt="" />

1     public class Student
2     {
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 = sql
112                     .SqlSelect(a => new { a.AreaName, a.AreaCode })
113                     .SqlWhere(a => a.AreaName.SqlLike("六一") && a.AreaCode.SqlFunc("AreaCode=1"))
114                     .ToList();
115                 var data141 = sql
116                     .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 remark
10                 var casewh = sql
11                     .SqlNewSelect(a =>
12                         new
13                         {
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 remark
19                 var casewh2 = sql
20                     .SqlNewSelect(a =>
21                         new
22                         {
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 = sql
28                     .SqlNewSelect(a => new
29                     {
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         }

Group查询,Case查询