在C#中建立復(fù)雜的、靈活的SQL查詢(xún)/命令_.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 ";
}

　　我相信上面的代碼對(duì)你來(lái)說(shuō)是非常熟悉的，你可能在過(guò)去的十多年一直是這樣使用的，或者你曾經(jīng)編碼過(guò)數(shù)據(jù)庫(kù)驅(qū)動(dòng)的搜索功能。讓我告訴你這種思想:這種查詢(xún)你的數(shù)據(jù)庫(kù)的方法不能再使用了，它是難看的也是不安全的。

　　SelectQueryBuilder方式的代碼

　　同樣的查詢(xún)能夠使用SelectQueryBuilder類(lèi)建立。

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