FreeSql学习笔记——12.执行Sql

FreeSql支持通过Sql语句配合ISelect生成最终的Sql语句，也可以执行原生自定义Sql语句，使操作更灵活；通过sql语句与Iselect配合使用更好控制sql语句；

简单查询

_freeSql.Select<Student>()
.WithSql(@"SELECT TOP 10 * FROM dbo.Student
            WHERE Age > 5 AND Status = 1
            ORDER BY Age DESC")
.ToList();

SELECT a.[Id], a.[Name], a.[Age], a.[Status], a.[AddTime], a.[Remark], a.[Version], a.[ClassId]
FROM ( SELECT TOP 10 * FROM dbo.Student
WHERE Age > 5 AND Status = 1
ORDER BY Age DESC ) a

//指定字段
_freeSql.Select<Student>()
                .WithSql(@"SELECT TOP 10 * FROM dbo.Student
                           WHERE Age > 5 AND Status = 1
                           ORDER BY Age DESC")
                .ToList<object>("Name,Age");
SELECT Name,Age
FROM ( SELECT TOP 10 * FROM dbo.Student
WHERE Age > 5 AND Status = 1
ORDER BY Age DESC ) a

当只需要各别字段时，需要将字段卸载Tolist()中，而非WithSql，WithSql中的sql语句会被包起来作为一个表，如果外层（Tolist中的字段）查询内层（WithSql中的字段）没有字段会报错

以下是错误示例

_freeSql.Select<Student>()
.WithSql(@"SELECT TOP 10 Nmae,Age FROM dbo.Student
            WHERE Age > 5 AND Status = 1
            ORDER BY Age DESC")
.ToList("Id,Name");
SELECT a.[Id], a.[Name]
FROM ( SELECT TOP 10  Nmae,Age FROM dbo.Student
WHERE Age > 5 AND Status = 1
ORDER BY Age DESC ) a

分页

_freeSql.Select<Student>()
                .WithSql(@"SELECT * FROM dbo.Student")
                .WhereIf(true, "Age > 5")
                .OrderBy(x => x.Age)
                .Page(2, 10)
                .ToList();
//SELECT a.[Id], a.[Name], a.[Age], a.[Status], a.[AddTime], a.[Remark], a.[Version], a.[ClassId]
//FROM(SELECT * FROM dbo.Student) a
//WHERE(Age > 5)
//ORDER BY a.[Age]
//OFFSET 10 ROW
//FETCH NEXT 10 ROW ONLY

Union All

FreeSql提供WithSql生成Union All查询，多个WithSql组合在一起会用Union All拼接，从而实现Union All

var sql1 = _freeSql.Select<Student>()
                .WithSql(@"SELECT * FROM dbo.Student WHERE ClassId=1")
                .ToSql();
//SELECT a.[Id], a.[Name], a.[Age], a.[Status], a.[AddTime], a.[Remark], a.[Version], a.[ClassId] 
            //FROM(SELECT * FROM dbo.Student WHERE ClassId = 1) a
var sql2 = _freeSql.Select<Student>()
                .WithSql(@"SELECT * FROM dbo.Student WHERE ClassId=2")
                .ToSql();
//SELECT a.[Id], a.[Name], a.[Age], a.[Status], a.[AddTime], a.[Remark], a.[Version], a.[ClassId]
//FROM(SELECT * FROM dbo.Student WHERE ClassId = 2) a

//合并
_freeSql.Select<Student>()
                .WithSql(sql1)
                .WithSql(sql2)
                .ToList();
//SELECT* from(SELECT a.[Id], a.[Name], a.[Age], a.[Status], a.[AddTime], a.[Remark], a.[Version], a.[ClassId]
//FROM (SELECT a.[Id], a.[Name], a.[Age], a.[Status], a.[AddTime], a.[Remark], a.[Version], a.[ClassId]
//    FROM (SELECT* FROM dbo.Student WHERE ClassId= 1) a ) a) ftb

//UNION ALL

//SELECT* from(SELECT a.[Id], a.[Name], a.[Age], a.[Status], a.[AddTime], a.[Remark], a.[Version], a.[ClassId]
//FROM (SELECT a.[Id], a.[Name], a.[Age], a.[Status], a.[AddTime], a.[Remark], a.[Version], a.[ClassId]
//    FROM (SELECT* FROM dbo.Student WHERE ClassId= 2) a ) a) ftb

拼接sql结果集

利用ToSql拼接新的SQL，将ToSql后获得的sql语句使用IAdo执行，可以实现多个sql语句的拼接，最后使用ado执行sql，以union all为例

var sql3 = _freeSql.Select<Student>()
                .WithSql(@"SELECT * FROM dbo.Student WHERE ClassId=1")
                .ToSql();
var sql4 = _freeSql.Select<Student>()
                .WithSql(@"SELECT * FROM dbo.Student WHERE ClassId=2")
                .ToSql();
_freeSql.Ado.CommandFluent($"{sql3} UNION ALL {sql4}")
                .ExecuteDataTable();
//            SELECT a.[Id], a.[Name], a.[Age], a.[Status], a.[AddTime], a.[Remark], a.[Version], a.[ClassId]
//FROM(SELECT * FROM dbo.Student WHERE ClassId = 1) a UNION ALL SELECT a.[Id], a.[Name], a.[Age], a.[Status], a.[AddTime], a.[Remark], a.[Version], a.[ClassId]
//FROM(SELECT * FROM dbo.Student WHERE ClassId = 2) a

分页坑

当有两个sql语句，使用WithSql拼接两个sql或使用Page分页会导致两个sql都被加上了分页

可以看到在sql7生成的时候使用了Page(1,10)，导致sql查询的时候添加了Top 10，而且两条查询语句都添加了！

官方提供的解决方案是先将sql5和sql6先不分页生成sql7，最终执行的sql7的时候再分页，这样就能正确拼接两条sql语句再分页；

前言

简单查询

分页

Union All

拼接sql结果集

分页坑

公告