介绍一种新类型查询方法,类似linq,lambda语法,类似标准的sql使用习惯,支持匿名类型,泛型,目前支持mssql,mysql,
切换只需要DatabaseConfig.DatabaseType = DatabaseType.SQLServer;无需改任何代码,dll后续开放下载
使用说明:基于实体查询,实体名和表名相同,实体需要使用代码生成器生成,工具后续开放下载
只需要生成所有表的实体,其它数据任意查,不需要手写任何Model,
查询结果
public class Student
{
public int ID { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public bool IsGraduate { get; set; }
public string Grade { get; set; }
public decimal Money { get; set; }
}
表对应实体
/// <summary>
/// 单表查询
/// </summary>
public static void single_able()
{
using (var sql = new SqlModel<T_BAS_Areas>())
{
//!+常用方法
/*
*使用说明
*数据查询方法,只有调用后才会有数据返回
*ToDataTable(),ToList(),ToPageList(),First(),FirstOrDefault()
*/
//?查默认一条数据
var data1 = sql
.SqlSelect(a => new { a.AreaName, a.AreaCode })
.FirstOrDefault();
//?所有字段查询
var data1_1 = sql
.SqlSelect()
.FirstOrDefault();
//查询top 10
var data1_2 = sql
.SqlSelect()
.SqlTop()
.ToList();
//?DataTable
var data2 = sql
.SqlSelect(a => new { a.AreaName, a.AreaCode })
.ToDataTable();
//?List
var data3 = sql
.SqlSelect(a => new { a.AreaName, a.AreaCode })
.ToList();
//?分页
var data4 = sql
.SqlPage(a => new { a.AreaName, a.AreaCode })
.ToPageList();
var data4_1 = sql
.SqlPage(a => new { a.AreaName, a.AreaCode }, , )
.ToPageList();
//?count
var data5 = sql
.SqlCount(a => a.AreaCode == "");
//?sum
var data6 = sql
.SqlSum(a => new { a.AreaCode })
.First(); //?无锁查询
var data7 = sql
.SqlSelect(a => new { a.AreaName, a.AreaCode }, LockType.NOLOCK)
.FirstOrDefault();
//?指定索引查询
var data8 = sql
.SqlSelect(a => new { a.AreaName, a.AreaCode }
, "PK_T_BAS_AREAS")
.FirstOrDefault(); //?条件查询
var data9 = sql
.SqlSelect(a => new { a.AreaName, a.AreaCode })
.SqlWhere(a => a.ParentAreaCode == "" && a.AreaName.SqlLike("九江"))
.FirstOrDefault();
//? 查前10条并排序
var data10 = sql
.SqlSelect(a => new { a.AreaName, a.ParentAreaCode, a.AreaCode })
.SqlTop()
.SqlOrderBy(b => new { b.ParentAreaCode, b.AreaCode })
.ToList();
var data11 = sql
.SqlSelect(a => new { a.AreaName, a.ParentAreaCode, a.AreaCode })
.SqlTop()
.SqlOrderDescBy(b => new { b.ParentAreaCode, b.AreaCode })
.ToList();
//? 查前10%条并排序
var data12 = sql
.SqlSelect(a => new { a.AreaName, a.ParentAreaCode, a.AreaCode })
.SqlPercent().SqlOrderBy(b => new { b.ParentAreaCode }).ToList();
data12 = sql.SqlSelect(a => new { a.AreaName, a.ParentAreaCode, a.AreaCode })
.SqlPercent()
.SqlOrderDescBy(b => new { b.ParentAreaCode })
.SqlOrderBy(c => new { c.AreaCode })
.ToList(); //?查前10条不重复的项
var data13 = sql
.SqlSelect(a => new { a.ParentAreaCode, a.AreaCode })
.SqlDistinct()
.SqlTop()
.ToList(); //!更新
sql.SqlUpdate(a => new object[] { a.AreaName == "溪湖区" },//a.SqlFunc("AreaCode='2'")
b => b.AreaGuidGuid == Guid.Parse("949B2F9D-F730-48ED-8B58-000144166BE9")); //!添加
T_BAS_Areas area = new T_BAS_Areas()
{
AreaGuidGuid = Guid.NewGuid(),
AreaCode = "",
AreaName = "测试",
ParentAreaCode = "-1",
};
sql.SqlAdd(area);
//!删除
sql.SqlDelete(a => a.AreaGuidGuid == area.AreaGuidGuid); //where 查询
var data14 = sql
.SqlSelect(a => new { a.AreaName, a.AreaCode })
.SqlWhere(a => a.AreaName.SqlLike("六一") && a.AreaCode.SqlFunc("AreaCode=1"))
.ToList();
var data141 = sql
.SqlSelect(a => new { a.AreaName, a.AreaCode })
.SqlWhere(a => a.AreaName.SqlLike("%{0}?", "六一")
&& a.AreaCode.SqlFunc("AreaCode='1'")//a.SqlFunc("AreaCode='1'")
|| a.AreaCode.SqlFunc("AreaCode=Parent"))
.ToList();
}
}
单表查询
/// <summary>
/// 多表查询
/// </summary>
public static void multilist_table()
{ using (var sql = new SqlModel<T_ST_Role, T_ST_Role_Permission>())
{
int count;
var left = sql.SqlSelect((a, b) => new { NameTest = a.RoleName, b.PermissionCode })
.SqlJionLeft((a, b) => b)
.SqlOn((a, b) => a.RoleGuid == b.RoleGuid)
.SqlOrderBy((a, b) => b.PermissionCode)
.SqlWhere((a, b) => a.RoleCode == "")
.ToList();
count = left.Count; var right = sql.SqlSelect((a, b) => new { NameTest = a.RoleName, b.PermissionCode })
.SqlJionRight((a, b) => b)
.SqlOn((a, b) => a.RoleGuid == b.RoleGuid)
.ToList(); var full = sql.SqlSelect((a, b) => new { NameTest = a.RoleName, b.PermissionCode, })
.SqlJionFull((a, b) => b)
.SqlOn((a, b) => a.RoleGuid == b.RoleGuid)
.ToList(); var inner = sql.SqlSelect((a, b) => new { NameTest = a.RoleName, b.PermissionCode })
.SqlJionInner((a, b) => b)
.SqlOn((a, b) => a.RoleGuid == b.RoleGuid)
.ToList(); var page = sql.SqlPage((a, b) => new { a.RoleGuid, NameTest = a.RoleName, b.PermissionCode }, , )
.SqlJionLeft((a, b) => b).SqlOn((a, b) => a.RoleGuid == b.RoleGuid)
.SqlWhere((a, b) => a.RoleCode == "R000001")
.SqlOrderBy((a, b) => b.PermissionCode)//.SqlOrderBy(o => new { o.PermissionCode })
.ToPageList(); } using (var sql = new SqlModel<T_ST_Role, T_ST_Role_Permission, T_ST_Permission, T_ST_User_Role>())
{
var data = sql.SqlSelect((a, b, c, d) => new { a.RoleGuid, a.RoleName, b.Role_PermissionGuid, c.PermissionCode })
.SqlJionLeft((a, b, c, d) => b).SqlOn((a, b, c, d) => a.RoleGuid == b.RoleGuid)
.SqlJionRight((a, b, c, d) => c).SqlOn((a, b, c, d) => b.PermissionGuid == c.PermissionGuid)
.SqlJionLeft((a, b, c, d) => d).SqlOn((a, b, c, d) => a.RoleGuid == d.RoleGuid)
.ToList(); var page = sql.SqlPage((a, b, c, d) => new { a.RoleGuid, a.RoleName, b.Role_PermissionGuid, c.PermissionCode })
.SqlJionLeft((a, b, c, d) => b).SqlOn((a, b, c, d) => a.RoleGuid == b.RoleGuid)
.SqlJionRight((a, b, c, d) => c).SqlOn((a, b, c, d) => b.PermissionGuid == c.PermissionGuid)
.SqlJionLeft((a, b, c, d) => d).SqlOn((a, b, c, d) => a.RoleGuid == d.RoleGuid)
.ToPageList();
}
}
多表查询
/// <summary>
/// groupby case when then
/// </summary>
public static void groupby_casewhenthe()
{
using (var sql = new SqlModel<Students>())
{
//case when then查询方式1
//CASE WHEN age=16 THEN '16岁' WHEN age=18 THEN '18岁' WHEN age=20 THEN '20岁'else '可选' END AS remark
var casewh = sql
.SqlNewSelect(a =>
new
{
a.Name,
Remark = a.CaseWhen(b => b.Age == ).Then("16岁").When(b => b.Age == ).Then("18岁").When(b => b.Age == ).Then("20岁").Else("其它").End()
}).First();
//case when then查询方式2 多条件选择
//CASE Age WHEN 16 THEN '16岁' WHEN 18 THEN '18岁' WHEN 20 THEN '20岁'else '可选' END AS remark
var casewh2 = sql
.SqlNewSelect(a =>
new
{
a.Name,
Remark = a.Case(a.Age).When().Then("16岁").When().Then("18岁").When().Then("20岁").Else("其它").End()
}).First();
//group by查询
var fun = sql
.SqlNewSelect(a => new
{
a.Name,
asName = a.Name.SqlCount(),
asAge = a.Age.SqlSum(),
asMaxAge = a.Age.SqlMax(),
asMinAge = a.Age.SqlMin(),
asAvg = a.Age.SqlAVG()
})
.SqlWhere(a => a.Age == )
.SqlGroupBy(a => new { a.Name, a.Age })
.SqlHaving(a => a.Name == "name_1500081")
.SqlOrderBy(a => a.Name).First(); Console.WriteLine(fun.asMaxAge + casewh.Name);
}
}
Group查询,Case查询
