在C#中建立复杂的、灵活的SQL查询/命令_.Net教程

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();