Automatically Generating SQL Statements

As you've seen in the previous sections, supplying your own INSERT, UPDATE, and DELETE statements or stored procedures to push changes from your DataSet to the database means you have to write a lot of code. You can avoid writing this code by using a CommandBuilder object, which can automatically generate single-table INSERT, UPDATE, and DELETE commands that push the changes you make to a DataSet object to the database. These commands are then set in the InsertCommand, UpdateCommand, and DeleteCommand properties of your DataAdapter object. When you then make changes to your DataSet and call the Update() method of your DataAdapter, the automatically generated command is run to push the changes to the database.

Although you can save writing some code using a CommandBuilder, you must remember the following limitations when using a CommandBuilder:

  • The SelectCommand property of your DataAdapter can retrieve rows from only a single table.

  • The database table used in your SelectCommand must contain a primary key.

  • The primary key of the table must be included in your SelectCommand.

  • The CommandBuilder takes a certain amount of time to generate the commands because it has to examine the database.

Warning 

Because a CommandBuilder lowers the performance of your program, you should try to avoid using them. They are intended for use by developers who aren't familiar with SQL or stored procedures. For best performance, use stored procedures.

There are three CommandBuilder managed provider classes: SqlCommandBuilder, OleDbCommandBuilder, and OdbcCommandBuilder. You'll see the use of a SqlCommandBuilder object in this section that works with a SQL Server database. The other types of objects work in the same way.

First, you need to set the SelectCommand property of a SqlDataAdapter object. The SELECT statement used in this command can retrieve rows from only a single table, and in the following example the Customers table is used:

 SqlCommand mySelectCommand = mySqlConnection.CreateCommand(); mySelectCommand.CommandText =   "SELECT CustomerID, CompanyName, Address " +   "FROM Customers " +   "ORDER BY CustomerID"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySelectCommand; 

Next, the following example creates a SqlCommandBuilder object, passing mySqlDataAdapter to the constructor:

 SqlCommandBuilder mySqlCommandBuilder =   new SqlCommandBuilder(mySqlDataAdapter); 

The SqlCommandBuilder will generate the commands containing INSERT, UPDATE, and DELETE statements based on the SELECT statement previously set in the mySqlDataAdapter object's SelectCommand property.

You can obtain the generated commands using the GetInsertCommand(), GetUpdateCommand(), and GetDeleteCommand() methods of mySqlCommandBuilder. For example:

 Console.WriteLine(   "mySqlCommandBuilder.GetInsertCommand().CommandText =\n" +   mySqlCommandBuilder.GetInsertCommand().CommandText ); Console.WriteLine(   "mySqlCommandBuilder.GetUpdateCommand().CommandText =\n" +   mySqlCommandBuilder.GetUpdateCommand().CommandText ); Console.WriteLine(   "mySqlCommandBuilder.GetDeleteCommand().CommandText =\n" +   mySqlCommandBuilder.GetDeleteCommand().CommandText ); 

This code displays the following output (I've added some white space to make it easier to read):

 mySqlCommandBuilder.GetInsertCommand().CommandText =   INSERT INTO Customers(CustomerID , CompanyName , Address)   VALUES (@p1 , @p2 , @p3) mySqlCommandBuilder.GetUpdateCommand().CommandText =   UPDATE Customers   SET CustomerID = @p1 , CompanyName = @p2 , Address = @p3   WHERE (CustomerID = @p4 AND CompanyName = @p5 AND Address = @p6) mySqlCommandBuilder.GetDeleteCommand().CommandText =   DELETE FROM Customers   WHERE (CustomerID = @p1 AND CompanyName = @p2 AND Address = @p3) 

As you can see, these commands are similar to those shown earlier in the section "Modifying Rows in a DataTable." The SQL statements used in these commands use optimistic concurrency.

You can now populate and make changes to a DataTable containing rows from the Customers table, and then push those changes to the database using the Update() method. You can use the same AddDataRow(), ModifyDataRow(), and RemoveDataRow() methods as shown in the earlier section, "Modifying Rows in a DataTable."

Note 

You'll find a complete program named UsingCommandBuilder.cs in the ch11 directory that illustrates the use of the CommandBuilder object shown in this section. This listing is omitted from this book for brevity.




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net