在c#中建立复杂的、灵活的sql查询/命令_c#应用

原文地址：http://www.codeproject.com/cs/database/SelectQueryBuilder.asp

SelectQueryBuilder类允许在你的代码中建立复杂的SQL语句和命令。它也能帮助于避免SQL注入式攻击。

介绍
       承认，并且我们都这样作过，也认为下面的方式是最好的和唯一的方式。就是我们建立大量的字符串包含所有的Where子句，然后提交到数据库去执行它。来断的加语句到我们的SQL字符串，极有可能会带来Bugs和SQL注入式攻击的危险。并且也使得我们的代码更难看也不易于管理。

       这种情况必须停止，但如何停止？有人说使用存储过程。但它并没有真正的解决这个问题。你还得动态建立你的SQL语句，只不过有问题移到数据库层面上了，依然有SQL注入的危险。除了这个“解决方案”外，可能还有非常多的选择供你考虑，但它们都会带来一个基本的挑战：让SQL语句工作的更好、更安全。

       当我从我的在线DAL（数据访问层）生成工具http://www.code-engine.com/建立C#模板时，我想提供一个易于使用的方法来定制查询数据。我不再想使用“字符串查询”（我以前开发的模板）来查询数据。我厌烦这种凌乱的方式来得到数据。我想用一种清晰的、直觉的、灵活的、简单的方式从表中选择数据，联接一些别的语句，使用大量的Where子句，用一些列来分组数据，返回前X个记录。

       我开始开发所想的有这种严密功能的SelectQueryBuilder类。它暴露了许多属性和方法，你能很容易地在Select语句中使用它们。一旦调用BuildQuery()和BuildCommand()方法，它能提供一种更好的旧的“字符串查询“或可以使用命令参数的DbCommand对象来查询数据。

使用代码
旧的方式的代码
       下面的代码阐明了以前建立SELECT语句的方法，它使用许多类变量来说明应该使用那种连接操作（WHERE，或者OR）,同时也给你的数据库带来了可能的SQL注入式攻击。

string statement = “SELECT TOP ” + maxRecords + ” * FROM Customers “;

string whereConcatenator = “WHERE “;

if (companyNameTextBox.Text.Length > 0)

{

    statement += whereConcatenator;

    statement += “CompanyName like ” + companyNameTextBox.Text + “% “;

    whereConcatenator = “AND “;

}

if (cityTextBox.Text.Length > 0)

{

    statement += whereConcatenator;

    statement += “City like ” + cityTextBox.Text + “% “;

    whereConcatenator = “AND “;

}

if (countryComboBox.SelectedItem != null)

{

    statement += whereConcatenator;

    statement += “Country = ” + countryComboBox.SelectedItem + ” “;

    whereConcatenator = “AND “;

}

       我相信上面的代码对你来说是非常熟悉的，你可能在过去的十多年一直是这样使用的，或者你曾经编码过数据库驱动的搜索功能。让我告诉你这种思想：这种查询你的数据库的方法不能再使用了，它是难看的也是不安全的。

SelectQueryBuilder方式的代码
       同样的查询能够使用SelectQueryBuilder类建立。

SelectQueryBuilder query = new SelectQueryBuilder();

query.SelectFromTable(“Customers”);

query.SelectAllColumns();

query.TopRecords = maxRecords;

if (companyNameTextBox.Text.Length > 0)

    query.AddWhere(“CompanyName”, Comparison.Like,

                    companyNameTextBox.Text + “%”);

if (cityTextBox.Text.Length > 0)

    query.AddWhere(“City”, Comparison.Like,

                   cityTextBox.Text + “%”);

if (countryComboBox.SelectedItem != null)

    query.AddWhere(“Country”, Comparison.Equals,

                  countryComboBox.SelectedItem);

string statement = query.BuildQuery();

// or, have a DbCommand object built

// for even more safety against SQL Injection attacks:

query.SetDbProviderFactory(

      DbProviderFactories.GetFactory(

      “System.Data.SqlClient”));

DbCommand command = query.BuildCommand();

       你能看到，这种方式比直接使用连接字符串更直观。考虑到第一个例子SQL注入的危险，通过SelectQueryBuilder建立的SELECT查询是非常安全的，并不用担心使用的TextBoxs中的内容。事实上它也非常简单！

使用SQL函数
       如果你想在你的查询中使用SQL函数，你能使用SqlLiteral类来打包函数的调用。说明这个类能作什么的最好方式就是给你显示一小段代码例子：

SelectQueryBuilder query = new SelectQueryBuilder();

query.SelectFromTable(“Orders”);

query.AddWhere(“OrderDate”, Comparison.LessOrEquals,

                       new SqlLiteral(“getDate()”));

       如果我们没有打包getDate()函数调用到SqlLiteral类中，建立的查询就会产生WHERE子句:OrderDate<=’getDate()’。当然，我们希望在语句中的这个函数没有被单引号包围。这时SqlLiteral就可以派上用场了：它直接拷贝字符串到输出，并没有把它格式化成字符串。现在的输出WHERE子句应当是OrderDate<=getDate()！

查询中使用JOINs
       要创建到其它表的JOINs，你能使用AddJoin方法。下面的代码显示了如何创建一个从Ordres表到Customers表的INNER JOIN。

SelectQueryBuilder query = new SelectQueryBuilder();

query.SelectFromTable(“Orders”);

query.AddJoin(JoinType.InnerJoin,

              “Customers”, “CustomerID”,

              Comparison.Equals,

              “Orders”, “CustomerID”);

query.AddWhere(“Customers.City”,

      Comparison.Equals, “London”);

       这段代码选择所有居住在London的客户的订单。一旦调用了BuildQuery方法，就会产生下面的SQL语句：

SELECT Orders.*

FROM Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID

WHERE (Customers.City = London)

       注意到缺省的查询只会建立所选择的表的selects * 语句（这个例子中的Orders.*）。如果你也想选择连接表的列的话，你必须得显式地选择它们。你能通用调用query.SelectColumns(“Orders.*”,”Customers.*”)。

建立计算查询
       如果你想对你的数据库执行一个计算查询。你能使用SelectCount方法如同下面显示的：

Query.SelectCount()；

       在更加复杂的计算查询中，你可能想使用GROUP BY语句。看一下下面的例子，它显示了如何使用GroupBy和AddHaving方法。

SelectQueryBuilder query = new SelectQueryBuilder();

query.SelectColumns(“count(*) AS Count”, “ShipCity”);

query.SelectFromTable(“Orders”);

query.GroupBy(“ShipCity”);

query.AddHaving(“ShipCity”, Comparison.NotEquals, “Amsterdam”);

query.AddOrderBy(“count(*)”, Sorting.Descending);

       上面的代码选择了每个城市的订单数，并用订单数目排序，不考虑来自制Amsterdam的订单，BuildQuery方法的输出结果应当是：

SELECT count(*) AS Count, ShipCity

FROM Orders

GROUP BY ShipCity

HAVING (ShipCity <> Amsterdam)   

ORDER BY count(*) DESC

复杂的Where语句
       如果你曾经用过微软的Access或SQL Server的内置的查询生成器的话，是否惊讶你能建立和代码一样的包含多层ANDs和Ors，并没有关心（）符号的位置的查询？是的？我也能！

       你能使用SelectQueryBuilder类实现！你能加多层的WHERE语句到你的查询。缺省，所有对query.AddWhere的调用被放在查询的第一层上。你可以把它比作SQL Server查询生成器的’Criteria’列；第二、三、四层等相应地对应于’Or…’列。

       看一下下面的SQL Server查询生成器的快照，通过它我能快速地把简单的假的SELECT语句放在一起来：

       如你看到的，我创建一个查询，它选择所有在1-1-2005日期之前的客户’VINET’的订单，和所有30-6-2004日期之前或1-1-2006日期之后的客户’TOMSP’的订单（请不要问为什么有人想查询某个人的订单，这仅仅是一个 例子）。这个查询能够建立：

SelectQueryBuilder query = new SelectQueryBuilder();

query.SelectFromTable(“Orders”);

// Add Criteria column to level 1

query.AddWhere(“CustomerID”, Comparison.Equals,

                                   “VINET”, 1);

query.AddWhere(“OrderDate”, Comparison.LessThan,

                     new DateTime(2005,1,1), 1);

// Add first Or… column to level 2

query.AddWhere(“CustomerID”, Comparison.Equals, “TOMSP”, 2);

query.AddWhere(“OrderDate”, Comparison.LessThan,

                    new DateTime(2004,6,30), 2);

// Add second Or… column to level 3

query.AddWhere(“CustomerID”, Comparison.Equals,

                                   “TOMSP”, 3);

query.AddWhere(“OrderDate”, Comparison.GreaterThan,

                        new DateTime(2006,1,1), 3);

       当调用 BuildQuery时，所有定义的层将被OR到一起，几乎和SQL Server生成的一样。

       如果你到所产生的语句接近一样时，想让查询更复杂，你可能会说“我的放两个随后的语句一起放在一个语句中，在两个日期间使用OR”。你能够这样作。在SQL Server查询生成器中，这个查询看起来像：

       同样，它也可能使用SelectQueryBuilder通过创建’嵌套的WHERE子句’来实现。

SelectQueryBuilder query = new SelectQueryBuilder();

query.SelectFromTable(“Orders”);

// Add Criteria column to level 1

query.AddWhere(“CustomerID”, Comparison.Equals, “VINET”, 1);

query.AddWhere(“OrderDate”, Comparison.LessThan,

                     new DateTime(2005,1,1), 1);

// Add Or… column to level 2

query.AddWhere(“CustomerID”,

                Comparison.Equals, “TOMSP”, 2);

// Add the date selection clause

WhereClause clause =

    query.AddWhere(“OrderDate”, Comparison.LessThan,

                        new DateTime(2004,6,30), 2);

// Add a nested clause to the captured clause

clause.AddClause(LogicOperator.Or,

       Comparison.GreaterThan, new DateTime(2006,1,1));

       注意到我用了一个WhereClause对象，它由AddWhere调用返回。接着调用clause.AddClause创建一个嵌套的子句柄，并且选择通过指定LogicOperator.Or来把它OR到第一个子句上。所产生的语句如下：

SELECT Orders.*

FROM Orders

WHERE

(

    (CustomerID = VINET)

    AND (OrderDate < 2005/01/01 12:00:00)

)

OR

(

    (CustomerID = TOMSP)

    AND (OrderDate < 2004/06/30 12:00:00 OR

         OrderDate > 2006/01/01 12:00:00)

)

       请注意这个例子中日期包含’12:00:00’，这是因为我在DateTime构造体中忽略了时间。但这只要由于我的习惯。如果我使用new DateTime(2006,1,1,0,0,0)，日期字符串将包含’00:00:00’。

结论
       在介绍中我就提到，SelectQueryBuilder是CodeEngine框架的一部分。这个框架 也包含了DeleteQueryBuilder，UpdateQueryBuilder，InsertQueryBuilder。我在通过我的C#DAL产生器生成的代码中使用这些生成器。你能从www.code-engine.com上下载一份 框架DLL的拷贝。在这期间我也将发布其它的查询生成器的源代码。同时如果你有什么问题，评价或建议，请及时与我联系。

http://www.cnblogs.com/yxy21969/archive/2006/09/20/509154.html