Command Builder

In the previous section, you painstakingly created the Update, Insert, and Delete commands. You first created stored procedures, and then you created Command objects for each procedure, passing in the necessary parameters. The .NET Framework does a lot of this work for you, if the update, insert, and delete commands are simple enough.

The framework provides a Command Builder (SqlCommandBuilder and OleDbCommandBuilder) to generate the necessary Delete, Update, and Insert commands without your writing stored procedures. To take advantage of these objects, the following conditions must be met:

• The rows in the table you generate must come from a single table (or view) in the database.
• The table must have a primary key or a field with values guaranteed to be unique.
• The unique value column must be returned by the query used to fill the DataSet (the select command).
• The name of the table must not have spaces, periods, quotation marks, or other special characters.

To see how using the Command Builder classes simplifies the task when these conditions are met, you'll modify the program to build the DataSet only from the Bugs table. Your user interface will be much simpler because you'll use a simple Select statement: "Select * from Bugs".

Create a new application called UpdatingDataSetsCommandBuilder with a form like that shown in Figure 20-10. Add three buttons and a DataGrid, as described in Table 20-3.

Figure 20-10. Updating DataSets Command Builder form

Table 20-3. Controls for UpdatingDataSetsCommandBuilder

Control	ID	Text
Button	btnUpdateDS	Update DataSet
Button	btnRefreshDS	Refresh DataSet
Button	btnUpdateDB	Update Database
DataGrid	dgBugs

Example 20-17 shows the complete listing in C# and Example 20-18 shows the complete listing in VB.NET. A detailed analysis follows.

Example 20-17. Using Command Builder (C#)

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
 
namespace UpdatingDataSetsCommandBuilderCS
{
 public class Form1 : System.Windows.Forms.Form
 {
 private System.Windows.Forms.DataGrid dgBugs;
 private System.Windows.Forms.Button btnUpdateDS;
 private System.Windows.Forms.Button btnRefreshDS;
 private System.Windows.Forms.Button btnUpdateDB;
 private DataSet bugDS;
 
 private System.ComponentModel.Container components = null;
 
 public Form1( )
 {
 InitializeComponent( );
 RefreshDataSet( );
 }
 
 protected override void Dispose( bool disposing )
 {
 if( disposing )
 {
 if (components != null) 
 {
 components.Dispose( );
 }
 }
 base.Dispose( disposing );
 }
 
 #region Windows Form Designer generated code
 #endregion
 
 [STAThread]
 static void Main( ) 
 {
 Application.Run(new Form1( ));
 }
 
 private DataSet CreateBugDataSet( )
 {
 // connection string to connect to the Bugs Database
 string connectionString = 
 "server=YourServer; uid=sa; pwd=YourPW; " +
 "database=WindForm_Bugs";
 
 // Create connection object, initialize with 
 // connection string. Open it.
 System.Data.SqlClient.SqlConnection connection = 
 new System.Data.SqlClient.SqlConnection(connectionString);
 
 
 // Create a SqlCommand object and assign the connection
 System.Data.SqlClient.SqlCommand command = 
 new System.Data.SqlClient.SqlCommand( );
 command.Connection=connection;
 command.CommandText="select * from bugs";
 command.CommandType=CommandType.Text;
 
 // create a data adapter and assign the command object
 // and add the table mapping for bugs
 SqlDataAdapter dataAdapter = new SqlDataAdapter( );
 dataAdapter.SelectCommand=command;
 dataAdapter.TableMappings.Add("Table","BugInfo");
 
 // Create the data set and use the data adapter to fill it
 DataSet dataSet = new DataSet( );
 dataAdapter.Fill(dataSet);
 return dataSet;
 } // close CreateBugDataSet
 
 // Update the dataset with bogus data
 private void btnUpdateDS_Click(object sender, System.EventArgs e)
 {
 DataTable bugTable = bugDS.Tables["BugInfo"];
 bugTable.Rows[0]["Description"] = "This is a test";
 DataRow newRow = bugTable.NewRow( );
 newRow["Description"] = "New bug test";
 newRow["Product"] = 2; 
 newRow["Version"] = "0.01";
 newRow["Reporter"] = 3;
 bugTable.Rows.Add(newRow);
 }
 
 private void btnRefreshDS_Click(object sender, System.EventArgs e)
 {
 RefreshDataSet( );
 }
 
 private void RefreshDataSet( )
 {
 bugDS = CreateBugDataSet( );
 dgBugs.DataSource = bugDS.Tables[0];
 }
 
 private void btnUpdateDB_Click(object sender, System.EventArgs e)
 {
 string connectionString = 
 "server=YourServer; uid=sa; pwd=YourPW; " +
 "database=WindForm_Bugs";
 
 
 // Create connection object, initialize with 
 // connection string. Open it.
 System.Data.SqlClient.SqlConnection connection = 
 new System.Data.SqlClient.SqlConnection(connectionString);
 
 connection.Open( );
 
 SqlDataAdapter dataAdapter = 
 new SqlDataAdapter("select * from Bugs", connection);
 SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);
 dataAdapter.DeleteCommand = bldr.GetDeleteCommand( );
 dataAdapter.UpdateCommand = bldr.GetUpdateCommand( );
 dataAdapter.InsertCommand = bldr.GetInsertCommand( );
 
 SqlTransaction transaction;
 transaction = connection.BeginTransaction( );
 dataAdapter.DeleteCommand.Transaction = transaction;
 dataAdapter.UpdateCommand.Transaction = transaction;
 dataAdapter.InsertCommand.Transaction = transaction;
 
 
 // try to update, if all succeed commit
 // otherwise roll back
 try
 {
 int rowsUpdated = dataAdapter.Update(bugDS,"BugInfo");
 transaction.Commit( );
 MessageBox.Show(rowsUpdated.ToString( ) + " rows Updated.");
 RefreshDataSet( );
 }
 catch (Exception ex)
 {
 MessageBox.Show("Unable to update: " + ex.Message);
 transaction.Rollback( );
 }
 
 // rebind the grid to show the results
 // grid should be unchanged
 dgBugs.DataSource = bugDS.Tables["BugInfo"]; 
 } // close btnUpdateDB_Click
 }
}

Example 20-18. Using Command Builder (VB.NET)

Imports System.Data.SqlClient
Public Class Form1
 Inherits System.Windows.Forms.Form
 
 Dim bugDS As DataSet
 
 
 
#Region " Windows Form Designer generated code "
 
 Public Sub New( )
 MyBase.New( )
 
 'This call is required by the Windows Form Designer.
 InitializeComponent( )
 RefreshDataSet( )
 
#End Region
 
 Private Function CreateBugDataSet( ) As DataSet
 ' myConnection string to connect to the Bugs Database
 Dim connectionString As String = _
 "server=YourServer; uid=sa; pwd=YourPW; " + _
 "database=WindForm_Bugs"
 
 ' Create myConnection object, initialize with 
 ' myConnection string. Open it.
 Dim myConnection As New _
 System.Data.SqlClient.SqlConnection(connectionString)
 myConnection.Open( )
 
 
 ' Create a SqlCommand object and assign the myConnection
 Dim command As New System.Data.SqlClient.SqlCommand( )
 command.Connection = myConnection
 command.CommandText = "Select * from Bugs"
 command.CommandType = CommandType.Text
 
 ' create a data adapter and assign the command object
 ' and add the table mapping for bugs
 Dim myDataAdapter As New SqlDataAdapter( )
 myDataAdapter.SelectCommand = command
 myDataAdapter.TableMappings.Add("Table", "BugInfo")
 
 ' Create the data set and use the data adapter to fill it
 Dim myDataSet As New DataSet( )
 myDataAdapter.Fill(myDataSet)
 Return myDataSet
 End Function ' close CreateBugDataSet
 
 Private Sub RefreshDataSet( )
 bugDS = CreateBugDataSet( )
 dgBugs.DataSource = bugDS.Tables(0)
 End Sub
 
 ' Update the dataset with bogus data
 Private Sub btnUpdateDS_Click(ByVal sender As System.Object, 
 ByVal e As System.EventArgs) 
 Handles btnUpdateDS.Click
 Dim bugTable As DataTable = bugDS.Tables("BugInfo")
 bugTable.Rows(0)("Description") = "This is a test"
 
 Dim newRow As DataRow = bugTable.NewRow( )
 newRow("Description") = "New bug test"
 newRow("Product") = 2
 newRow("Version") = "0.01"
 newRow("Reporter") = 3
 bugTable.Rows.Add(newRow)
 
 End Sub
 
 Private Sub btnRefreshDS_Click(ByVal sender As System.Object, 
 ByVal e As System.EventArgs) 
 Handles btnRefreshDS.Click
 RefreshDataSet( )
 End Sub
 
 Private Sub btnUpdateDB_Click(ByVal sender As System.Object, 
 ByVal e As System.EventArgs) 
 Handles btnUpdateDB.Click
 
 Dim connectionString As String = "server=YourServer; uid=sa; " + _
 "pwd=YourPW; database=WindForm_Bugs"
 
 ' Create myConnection object, initialize with 
 ' myConnection string. Open it.
 Dim myConnection As New SqlConnection(connectionString)
 
 Dim transaction As SqlTransaction
 
 myConnection.Open( )
 
 Dim myDataAdapter As New SqlDataAdapter( _
 "Select * from Bugs", myConnection)
 Dim bldr As New SqlCommandBuilder(myDataAdapter)
 myDataAdapter.DeleteCommand = bldr.GetDeleteCommand( )
 myDataAdapter.UpdateCommand = bldr.GetUpdateCommand( )
 myDataAdapter.InsertCommand = bldr.GetInsertCommand( )
 
 transaction = myConnection.BeginTransaction( )
 myDataAdapter.DeleteCommand.Transaction = transaction
 myDataAdapter.UpdateCommand.Transaction = transaction
 myDataAdapter.InsertCommand.Transaction = transaction
 
 
 ' try to update, if all succeed commit
 ' otherwise roll back
 Try
 Dim rowsUpdated As Int16 = myDataAdapter.Update(bugDS, "BugInfo")
 transaction.Commit( )
 MessageBox.Show(rowsUpdated.ToString( ) + " rows Updated.")
 RefreshDataSet( )
 Catch ex As Exception
 MessageBox.Show("Unable to update db!" + ex.Message)
 transaction.Rollback( )
 End Try
 
 
 ' rebind the grid to show the results
 ' grid should be unchanged
 dgBugs.DataSource = bugDS.Tables("BugInfo")
 End Sub ' close btnUpdateDB_Click
End Class

Because you can use the Command Builder only with very simple queries, toss out all the code that deals with the BugHistory table. Modify btnUpdateDS_Click so that you update and add fields only in Bugs:

DataRow newRow = bugTable.NewRow( );
newRow["Product"] = 2; 
newRow["Version"] = "0.01";
newRow["Description"] = "New bug test";
newRow["Reporter"] = 3;
bugTable.Rows.Add(newRow);

Dim newRow As DataRow = bugTable.NewRow( )
newRow("Description") = "New bug test"
newRow("Product") = 2
newRow("Version") = "0.01"
newRow("Reporter") = 3
bugTable.Rows.Add(newRow)

Notice that you no longer delete a row. Because of referential integrity, you cannot delete the row without deleting from BugHistory, and because the Command Builder can work only with a single table at a time, you cannot delete from BugHistory. To keep this example simple, you'll just update and insert.

The important change is in btnUpdateDB_Click, which is now far easier. Simply retrieve the DataSet and set up the Connection object, just as you did previously:

string connectionString =
 "server=YourServer; uid=sa; 
 pwd=YourPassword; database=ProgASPDotNetBugs";
 
System.Data.SqlClient.SqlConnection connection = 
 new System.Data.SqlClient.SqlConnection(connectionString);
 
connection.Open( );

Dim connectionString As String = "server=YourServer; uid=sa; " + _
 "pwd=YourPW; database=WindForm_Bugs"
 
Dim myConnection As New SqlConnection(connectionString)
myConnection.Open( )

Then create a DataAdapter and a SqlCommandBuilder:

SqlDataAdapter dataAdapter =
 new SqlDataAdapter("select * from Bugs", connection);
SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);

Dim myDataAdapter As New SqlDataAdapter( _
 "Select * from Bugs", myConnection)
Dim bldr As New SqlCommandBuilder(myDataAdapter)

Use the Command Builder to build the DeleteCommand, UpdateCommand, and InsertCommand objects required by the DataAdapter, which you previously built by hand:

dataAdapter.DeleteCommand = bldr.GetDeleteCommand( );
dataAdapter.UpdateCommand = bldr.GetUpdateCommand( );
dataAdapter.InsertCommand = bldr.GetInsertCommand( );

myDataAdapter.DeleteCommand = bldr.GetDeleteCommand( )
myDataAdapter.UpdateCommand = bldr.GetUpdateCommand( )
myDataAdapter.InsertCommand = bldr.GetInsertCommand( )

That's it! You are ready to enlist the commands in the transaction:

SqlTransaction transaction;
transaction = connection.BeginTransaction( );
dataAdapter.DeleteCommand.Transaction = transaction;
dataAdapter.UpdateCommand.Transaction = transaction;
dataAdapter.InsertCommand.Transaction = transaction;

Dim transaction As SqlTransaction
transaction = myConnection.BeginTransaction( )
myDataAdapter.DeleteCommand.Transaction = transaction
myDataAdapter.UpdateCommand.Transaction = transaction
myDataAdapter.InsertCommand.Transaction = transaction

With that done, you are ready to call Update on the DataAdapter, as you did previously:

int rowsUpdated = dataAdapter.Update(ds,"Bugs");

Dim rowsUpdated As Int16 = myDataAdapter.Update(bugDS, "BugInfo")

The CommandBuilder object has created the necessary commands on your behalf.

That's all there is to it; the Command Builder does all the work of updating the database and ensuring concurrency integrity. You can see what the Command builder has done by adding this code to the try block:

MessageBox.Show(dataAdapter.InsertCommand.CommandText);
MessageBox.Show(dataAdapter.UpdateCommand.CommandText);

The result is that the Update command is displayed in a message box, as shown in Figures Figure 20-11 and Figure 20-12.

Figure 20-11. Command Builder insert command

Figure 20-12. Command Builder update command

Pay particular attention to the Update command; you can see that the Where clause is similar to the one you built earlier (this more complex command considers null fields).