在下面创建的程序中,我们可以查看到CommandBuilder生成的SQL命令:
using System;
using System.Data;
using System.Data.OleDb;
class ShowSQLExample
{
public static void Main()
{
OleDbConnection thisConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB4.0;Data Source=NWIND.MDB");
thisConnection.Open();
OleDbDataAdapter thisAdapter = new OleDbDataAdapter("SELECT CustomerID FROM Customers",thisConnection);
OleDbCommandBuilder thisBuilder = new OleDbCommandBuilder(thisAdapter);
Console.WriteLine("SQL SELECT Command is:/n{0}/n",thisAdapter.SelectCommand.CommandText);
OleDbCommand updateCommand = thisBuilder.GetUpdateCommand();
Console.WriteLine("SQL UPDATE Command is:/n{0}/n",thisAdapter.updateCommand.CommandText);
OleDbCommand insertCommand = thisBuilder.GetInsertCommand();
Console.WriteLine("SQL INSERT Command is:/n{0}/n",thisAdapter.insertCommand.CommandText);
OleDbCommand deleteCommand = thisBuilder.GetDeleteCommand();
Console.WriteLine("SQL DELETE Command is:/n{0}/n",thisAdapter.deleteCommand.CommandText);
thisConnection.Close();
}
}
上面的代码会生成4条语句,在UPDATE和DELETE命令中CommandBuilder对象生成的Where子句。其中包含(?)的问好是参数的标记,当ADO.NET运行时代替实际的值进入命令中。
为了输出SELECT命令,我们可以使用SelectCommand属性直接从DataAdapter中获取命令。DataAdapter还具有UpdateCommand、InsertCommand和DeleteCommand属性,可以在更新时直接获取或设置SQL命令。这样开发人员可以优化这些命令,这些命令要比由CommandBuilder自动生成的命令更易于执行,尤其是当将所有的命令包含在SQL SELECT语句中时更是如此。
<注:>本文参考文献
《C#入门经典》清华大学出版社