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 OrdersINNER JOIN Customers ON Orders.CustomerID = Customers.CustomerIDWHERE (Customers.City = 'London')
注意到缺省的查询只会建立所选择的表的selects * 语句(这个例子中的Orders.*)。如果你也想选择连接表的列的话,你必须得显式地选择它们。你能通用调用query.SelectColumns(Orders.*,Customers.*)。
建立计算查询
如果你想对你的数据库执行一个计算查询。你能使用SelectCount方法如同下面显示的:
Query.SelectCount();
在更加复杂的计算查询中,你可能想使用GROUP BY语句。看一下下面的例子,它显示了如何使用GroupBy和AddHaving方法。
(本文来源于图老师网站,更多请访问http://m.tulaoshi.com/bianchengyuyan/)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, ShipCityFROM OrdersGROUP BY ShipCityHAVING (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 1query.AddWhere("CustomerID", Comparison.Equals,"VINET", 1);query.AddWhere("OrderDate", Comparison.LessThan,new DateTime(2005,1,1), 1);// Add first 'Or...' column to level 2query.AddWhere("CustomerID", Comparison.Equals, "TOMSP", 2);query.AddWhere("OrderDate", Comparison.LessThan,new DateTime(2004,6,30), 2);// Add second 'Or...' column to level 3query.AddWhere("CustomerID", Comparison.Equals,"TOMSP", 3);query.AddWhere("OrderDate", Comparison.GreaterThan,new DateTime(2006,1,1), 3);
当调用 BuildQuery时,所有定义的层将被OR到一起,几乎和SQL Server生成的一样。
(本文来源于图老师网站,更多请访问http://m.tulaoshi.com/bianchengyuyan/)如果你到所产生的语句接近一样时,想让查询更复杂,你可能会说我的放两个随后的语句一起放在一个语句中,在两个日期间使用OR。你能够这样作。在SQL Server查询生成器中,这个查询看起来像: