SqlQuery 对象

[命名空间: Serenity.Data] - [程序集: Serenity.Data]

SqlQuery 通过一个流式接口编写动态 SQL SELECT 查询。

优点

SqlQuery 比手写 SQL 有如下优势:

  • 使用 Visual Studio 的智能感知功能编写 SQL;

  • 最小开销的流式接口;

  • 由于在编译时而不是运行时检查查询语法,所以可减少语法错误;

  • 像 Select、 Where、 Order By 这样的子句可以按任何顺序使用。当查询转换为字符串时,它们被放到正确的位置。类似地,这些子句可以使用多次并且在转换为字符串时被合并。因此你可以根据输入参数有条件地构建 SQL。

  • 不再搞砸参数和参数名称。所有使用的值转换为自动命名参数。如果需要,你也可以使用手动命名的参数;

  • 可以生成一个特殊的查询,可以在非原生支持分页的服务器类型 (如 SQL Server 2000)中执行分页;

  • 通过方言系统,可以针对特定的服务器类型和版本进行查询;

  • 如果与 Serenity 实体(可以像 Dapper 那样使用的微型 ORM)一起使用,有助于从 DataReader 零反射加载查询结果。它也支持自动左/右联接。

如何使用这里的示例

我推荐使用 LinqPad 执行这里给出的示例。

你应该添加 Serenity.CoreSerenity.DataSerenity.Data.Entity 的 NuGet 程序包引用。

另一种做法是从一个 Serene 的应用程序的 bin 或程序包目录中找到并直接引用这些 DLL。

请确保在查询属性(Query Properties)对话框中的导入额外命名空间(Additional Namespace Imports)中添加 SerenitySerenity.Data

一个简单的 Select 查询示例

void Main()
{
    var query = new SqlQuery();
    query.Select("Firstname");
    query.Select("Surname");
    query.From("People");
    query.OrderBy("Age");

    Console.WriteLine(query.ToString());
}

这将输出结果:

SELECT 
Firstname,
Surname 
FROM People 
ORDER BY Age

在程序的第一行,我们调用 SqlQuery 的唯一无参构造函数。如果此时调用 ToString(),将输出:

SELECT FROM

SqlQuery 不会进行任何语法验证。它只是通过调用其方法转换成你自己构建的查询。即使你没有选择任何字段或调用 From 方法,它也将生成基本的 SELECT FROM 语句。

SqlQuery 不能生成空查询。

然后,我们调用 Select 方法,并向其传递 "FirstName" 字符串参数。现在我们的查询将变为:

SELECT Firstname FROM

当执行 Select("Surname") 声明时,SqlQuery 在上一检索字段(Firstname)和当前检索字段之间添加逗号:

SELECT Firstname, Surname FROM

在执行 FromOrderBy 方法之后,最终输出:

SELECT Firstname, Surname FROM People ORDER BY Age

方法调用顺序和它的影响

在前面的示例,即使我们重新调整 FromOrderBySelect 行的顺序,输出的结果也不会发生变化。只有修改 Select 声明的顺序才会改变输出结果。

void Main()
{
    var query = new SqlQuery();
    query.From("People");
    query.OrderBy("Age");
    query.Select("Surname");
    query.Select("Firstname");

    Console.WriteLine(query.ToString());
}

但是,只有 SELECT 语句内部的列顺序会发生改变:

SELECT 
Surname,
Firstname 
FROM People 
ORDER BY Age

你可以按任意顺序使用 Select、 From、 OrderBy、 GroupBy 方法,并且可混合使用(如:先调用 Select,然后调用 OrderBy,之后再次调用 Select …… )。

建议把 FROM 放在查询的开头,特别是与 Serenity 实体一起使用时,因为这样做有助于自动联接、决定数据库方言等。

方法链

每行使用 query. 开头显得冗长且可读性比较差。几乎所有的 SqlQuery 方法是链式的,并把查询本身作为结果返回。

我们可以像下面这样重写该查询:

void Main()
{
    var query = new SqlQuery()
        .From("People")
        .Select("Firstname")
        .Select("Surname")
        .OrderBy("Age");

    Console.WriteLine(query.ToString());
}

该功能类似于 jQuery 和 LINQ 可枚举方法链。

我们甚至可以去掉查询变量:

void Main()
{
    Console.WriteLine(
        new SqlQuery()
            .From("People")
            .Select("Firstname")
            .Select("Surname")
            .OrderBy("Age")
            .ToString());
}

强烈建议把每个方法放在它自己的行中,且为了可读性和一致性,请使用合适的缩进。

Select Method

public SqlQuery Select(string expression)

在前面的示例中,我们使用上述的 Select 重载方法(它大约有 11 个重载方法)。

Expression 参数可以是简单的字段名称或像 "FirstName + ' ' + LastName" 这样的表达式。

每当调用此方法,设置的表达式以逗号间隔添加到 SELECT 语句的查询结果。

还有一个 SelectMany 方法,可以在一个调用中选择多个字段:

public SqlQuery SelectMany(params string[] expressions)

例如:

void Main()
{
    var query = new SqlQuery()
        .From("People")
        .SelectMany("Firstname", "Surname", "Age", "Gender")
        .ToString();

    Console.WriteLine(query.ToString());
}
SELECT 
Firstname,
Surname,
Age,
Gender 
FROM People

我个人更喜欢通过多次调用 Select 方法来实现该目的。

你可能会想:为什么 SelectMany 并不是 Select 的另一重载?这是因为 Select 有一个更为常用的重载,可以选择含别名的列:

public SqlQuery Select(string expression, string alias)
void Main()
{
    var query = new SqlQuery()
        .Select("(Firstname + ' ' + Surname)", "Fullname")
        .From("People")
        .ToString();

    Console.WriteLine(query.ToString());
}
SELECT 
(Firstname + ' ' + Surname) AS [Fullname] 
FROM People

From 方法

public SqlQuery From(string table)

SqlQuery.From 方法至少应被调用一次(通常一次)。

建议在查询中首先调用该方法。

当第二次调用该方法,表名称将以逗号间隔添加到 FROM 声明。因此,它将变为 CROSS JOIN:

void Main()
{
    var query = new SqlQuery()
        .From("People")
        .From("City")
        .From("Country")
        .Select("Firstname")
        .Select("Surname")
        .OrderBy("Age");

    Console.WriteLine(query.ToString());
}
SELECT 
Firstname,
Surname 
FROM People, City, Country 
ORDER BY Age

在 SqlQuery 中使用别名对象

当引用表的数量增加时,通常使用表别名,此时我们的查询变得更长:

void Main()
{
    var query = new SqlQuery()
        .From("Person p")
        .From("City c")
        .From("Country o")
        .Select("p.Firstname")
        .Select("p.Surname")
        .Select("c.Name", "CityName")
        .Select("o.Name", "CountryName")
        .OrderBy("p.Age")
        .ToString();

    Console.WriteLine(query.ToString());
}
SELECT 
p.Firstname,
p.Surname,
c.Name AS [CityName],
o.Name AS [CountryName] 
FROM Person p, City c, Country o 
ORDER BY p.Age

虽然它可以这样工作,但可以更好地把 pco 定义为 Alias 对象。

var p = new Alias("Person", "p");

Alias 对象为表指定简称。它有索引和操作运算符的重载来生成访问 SQL 成员的表达式,如 p.Surname

void Main()
{
    var p = new Alias("Person", "p");
    Console.WriteLine(p + "Surname"); // + operator overload
    Console.WriteLine(p["Firstname"]); // through indexer
}
p.Surname
p.Firstname

不幸的是,不能重载 C# 的成员访问运算符 (.),因此,我们不得不使用 (+) 。一种替代方法是使用 dynamic,但是它的表现并不佳。

让我们使用 Alias 对象修改查询:

void Main()
{
    var p = new Alias("Person", "p");
    var c = new Alias("City", "c");
    var o = new Alias("Country", "o");

    var query = new SqlQuery()
        .From(p)
        .From(c)
        .From(o)
        .Select(p + "Firstname")
        .Select(p + "Surname")
        .Select(c + "Name", "CityName")
        .Select(o + "Name", "CountryName")
        .OrderBy(p + "Age")
        .ToString();

    Console.WriteLine(query.ToString());
}
SELECT 
p.Firstname,
p.Surname,
c.Name AS [CityName],
o.Name AS [CountryName] 
FROM Person p, City c, Country o 
ORDER BY p.Age

如上所示,结果是相同的,但代码有点长。那么使用别名有什么优点呢?

如果我们有一个含字段名称的常量列表:

void Main()
{
    const string Firstname = "Firstname";
    const string Surname = "Surname";
    const string Name = "Name";
    const string Age = "Age";

    var p = new Alias("Person", "p");
    var c = new Alias("City", "c");
    var o = new Alias("Country", "o");
    var query = new SqlQuery()
        .From(p)
        .From(c)
        .From(o)
        .Select(p + Firstname)
        .Select(p + Surname)
        .Select(c + Name, "CityName")
        .Select(o + Name, "CountryName")
        .OrderBy(p + Age)
        .ToString();

    Console.WriteLine(query.ToString());
}

我们就可以利用智能感知功能和编译时检查。

显然,它不是很符合逻辑并且难以定义每个查询的字段名称。应该在一个集中的位置或实体声明中定义别名。

让我们使用 Alias 创建一个人员的简单 ORM:

public class PeopleAlias : Alias
{
    public PeopleAlias(string alias) 
        : base("People", alias) { }

    public string ID { get { return this["ID"]; } }
    public string Firstname { get { return this["Firstname"]; } }
    public string Surname { get { return this["Surname"]; } }
    public string Age { get { return this["Age"]; } }
}

public class CityAlias : Alias
{
  public CityAlias(string alias)
      : base("City", alias) { }

  public string ID { get { return this["ID"]; } }
  public string CountryID { get { return this["CountryID"]; } }
  public new string Name { get { return this["Name"]; } }
}

public class CountryAlias : Alias
{
  public CountryAlias(string alias)
      : base("Country", alias) { }

  public string ID { get { return this["ID"]; } }
  public new string Name { get { return this["Name"]; } }
}

void Main()
{
    var p = new PeopleAlias("p");
    var c = new CityAlias("c");
    var o = new CountryAlias("o");
    var query = new SqlQuery()
        .From(p)
        .From(c)
        .From(o)
        .Select(p.Firstname)
        .Select(p.Surname)
        .Select(c.Name, "CityName")
        .Select(o.Name, "CountryName")
        .OrderBy(p.Age)
        .ToString();

    Console.WriteLine(query.ToString());
}

现在我们有一组含字段名称和可以在所有查询中重用的表别名类。

这只是一个解释别名的示例,我并不推荐写这样的类。实体(Entities) 提供了更多的功能。

在上面的示例,我们使用包含 Alias 参数的 SqlQuery.From 重载:

public SqlQuery From(Alias alias)

当调用此方法时,表名称和它的别名被添加到查询的 FROM 子句。

OrderBy 方法

public SqlQuery OrderBy(string expression, bool desc = false)

OrderBy 也可以在调用时含字段名称或表达式(如,Select)。

如果你指定可选参数 desc 为 true,将在字段名称或表达式附加关键词 DESC

默认情况下,OrderBy 附加指定的表达式到 ORDER BY 语句末尾。但有时你可能想在起始处插入表达式/字段。

例如,有一些预定义顺序的查询,但如果用户在网格中对列进行排序,列名称应被插入到索引为 0 的位置。

public SqlQuery OrderByFirst(string expression, bool desc = false)
void Main()
{
    var query = new SqlQuery()
        .Select("Firstname")
        .Select("Surname")
        .From("Person")
        .OrderBy("PersonID");

    query.OrderByFirst("Age");

    Console.WriteLine(query.ToString());
}
SELECT 
Firstname,
Surname 
FROM Person 
ORDER BY Age, PersonID

Distinct 方法

public SqlQuery Distinct(bool distinct)

使用此方法在 SELECT 语句预置 DISTINCT 关键字。

void Main()
{
    var query = new SqlQuery()
        .Select("Firstname")
        .Select("Surname")
        .From("Person")
        .OrderBy("PersonID")
        .Distinct(true);

    Console.WriteLine(query.ToString());
}
SELECT DISTINCT 
Firstname,
Surname 
FROM Person 
ORDER BY PersonID

GroupBy 方法

public SqlQuery GroupBy(string expression)

GroupBy 的行为类似于 OrderBy,但没有 GroupByFirst 变体。

SELECT 
Firstname,
Lastname,
Count(*) 
FROM Person 
GROUP BY Firstname, LastName
SELECT 
Firstname,
Lastname,
Count(*) 
FROM Person 
GROUP BY Firstname, LastName

Having 方法

public SqlQuery Having(string expression)

Having 可以和 GroupBy (尽管它并不检查 GroupBy)一起使用,并且在表达式末尾追加 HAVING 语句。

void Main()
{
    var query = new SqlQuery()
        .From("Person")
        .Select("Firstname")
        .Select("Lastname")
        .Select("Count(*)")
        .GroupBy("Firstname")
        .GroupBy("LastName")
        .Having("Count(*) > 5");

    Console.WriteLine(query.ToString());
}
SELECT 
Firstname,
Lastname,
Count(*) 
FROM Person 
GROUP BY Firstname, LastName 
HAVING Count(*) > 5

分页操作(SKIP / TAKE / TOP / LIMIT)

public SqlQuery Skip(int skipRows)

public SqlQuery Take(int rowCount)

SqlQuery 有类似于 LINQ 的 Take 和 Skip 的分页方法。

数据库类型决定映射的 SQL 关键字。

由于 SqlServer 2012 之前的版本没有等效的 SKIP 方法,若要使用 SKIP 方法,你的查询应该至少有一个 ORDER BY 语句,因为需要使用 ROW_NUMBER() 。如果你使用 SqlServer 2012+ 的方言,就没有该要求。

void Main()
{
    var query = new SqlQuery()
        .From("Person")
        .Select("Firstname")
        .Select("Lastname")
        .Select("Count(*)")
        .OrderBy("PersonId")
        .Skip(100)
        .Take(50);

    Console.WriteLine(query.ToString());
}
SELECT 
Firstname,
Lastname,
Count(*) 
FROM Person 
ORDER BY PersonId OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY

在该示例中,默认使用 SQLServer2012 方言。

支持数据库方言

在我们的分页示例中,SqlQuery 使用与 Sql Server 2012 兼容的语法。

通过 Dialect 方法,可以更改 SqlQuery 的目标服务器类型:

public SqlQuery Dialect(ISqlDialect dialect)

这些是支持的方言类型列表:

FirebirdDialect
PostgresDialect
SqliteDialect
SqlServer2000Dialect
SqlServer2005Dialect
SqlServer2012Dialect

如果我们想在 Sql Server 2005 中查询:

void Main()
{
    var query = new SqlQuery()
        .Dialect(SqlServer2005Dialect.Instance)
        .From("Person")
        .Select("Firstname")
        .Select("Lastname")
        .Select("Count(*)")
        .OrderBy("PersonId")
        .Skip(100)
        .Take(50);

    Console.WriteLine(query.ToString());
}
SELECT * FROM (
SELECT TOP 150 
Firstname,
Lastname,
Count(*), ROW_NUMBER() OVER (ORDER BY PersonId) AS __num__ 
FROM Person) __results__ WHERE __num__ > 100

若使用 SqliteDialect.Instance,将输出:

SELECT 
Firstname,
Lastname,
Count(*) 
FROM Person 
ORDER BY PersonId LIMIT 50 OFFSET 100

如果在应用程序中只使用一种类型的数据库,你可以通过设置默认方言以避免每次开始查询时都要选择方言:

SqlSettings.DefaultDialect = SqliteDialect.Instance;

在应用程序的启动方法(如 global.asax.cs)中添加上述代码。

results matching ""

    No results matching ""