介紹一種新類型查詢方法,類似linq,lambda語法,類似標準的sql使用習慣,支持匿名類型,泛型,目前支持mssql,mysql, 切換隻需要DatabaseConfig.DatabaseType = DatabaseType.SQLServer;無需改任何代碼,dll後續開放下載 使用說明:基 ...
介紹一種新類型查詢方法,類似linq,lambda語法,類似標準的sql使用習慣,支持匿名類型,泛型,目前支持mssql,mysql,
切換隻需要DatabaseConfig.DatabaseType = DatabaseType.SQLServer;無需改任何代碼,dll後續開放下載
使用說明:基於實體查詢,實體名和表名相同,實體需要使用代碼生成器生成,工具後續開放下載
只需要生成所有表的實體,其它數據任意查,不需要手寫任何Model,
查詢結果
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查詢
