Updating the Data Source

Normally, after making all the desired changes to the tables in the DataSet, you will want to save those changes back to the data source. To do so, call the Update method of the DataAdapter. When the Update method is called, the DataAdapter analyzes the changes made to the specified table in the DataSet (or all the tables if no table name is specified). For each row that has been changed, the appropriate command (Insert, Update, or Delete) is executed against the data source to update it to the current data values. These commands are specified by the InsertCommand, UpdateCommand, and DeleteCommand properties.

Note

The ability to easily specify custom SQL statements or stored procedures to be automatically used when a data source is being updated from a DataSet is a major improvement over what was available with ADO 2.X. With ADO.NET not only can you modify how updates are performed when updating a batch of changed rows, but the ability to use stored procedures for this task offers improved performance and customized (business) logic, in addition to the ability to specify the SQL statements for the update operations. We present an example of this approach shortly. Moreover, the batch update mechanism works with even non-SQL data sources, unlike ADO 2.X, where the batch update worked only with an SQL-based data source.

Each changed row is updated individually and not as part of a transaction or batch operation. In addition, the order in which the rows are processed is determined by their order in the DataTable.

To control explicitly the order of the operations for a specific table, we can use either the GetChanges method or the Select method. Both methods are available at either the DataSet or the DataTable level. We use these methods to return separate sets of rows, with each set matching a different row state.

Let's say that we want to use the daDepartments to update the Novelty database from the ds-EmployeeInfo DataSet. Based on our requirements, we first need to do all the inserts, then all the updates, and only then all the deletes. We could do so by calling the GetChanges method three times, specifying a different row state each time. After each call to GetChanges, we call the DataAdapter's Update method, passing it the DataTable returned by the GetChanges method.

 dt = dsEmployeeInfo.Tables("Departments")  ' Get each type of change and update accordingly dtChanged = dt.GetChanges(DataRowState.Added) daDepartments.Update(dtChanged) dtChanged = dt.GetChanges(DataRowState.Modified) daDepartments.Update(dtChanged) dtChanged = dt.GetChanges(DataRowState.Deleted) daDepartments.Update(dtChanged) 

We can write this code more compactly as:

 dt = dsEmployeeInfo.Tables("Departments")  ' Get each type of change and update accordingly daDepartments.Update (dt.GetChanges(DataRowState.Added)) daDepartments.Update (dt.GetChanges(DataRowState.Modified)) daDepartments.Update (dt.GetChanges(DataRowState.Deleted)) 

We could also achieve the same results by using the Select method:

 dt = dsEmployeeInfo.Tables("Departments")  ' Get each type of change and update accordingly da.Update (dt.Select(Nothing, Nothing, _   DataViewRowState.Added)) da.Update (dt.Select(Nothing, Nothing, _     DataViewRowState.ModifiedCurrent)) da.Update (dt.Select(Nothing, Nothing, DataViewRowState.Deleted)) 

The advantage of using the Select method rather than the GetChanges method is that it allows for additional filtering and sorting (if desired).

This is a good time to remind you of the difference between removing a row and deleting a row from a DataTable, as discussed in Chapter 5. When you Remove a row, it is actually removed from the collection and no longer exists. When you Delete a row, it isn't actually removed, but is marked for deletion. Therefore, when using a DataTable together with a DataAdapter for data source updates, you should always use the Delete method rather than the Remove method to remove a row. When the DataAdapter encounters a row that has been marked as deleted, it knows to execute the DeleteCommand against the database to synchronize it with the DataTable. However, if the Remove method is used, the DataAdapter will never see the removed row when Update is called, and the row won't be deleted from the data source.

Setting the Update Commands

The DataAdapter doesn't automatically create the Insert, Update, and Delete SQL statements needed to update the data source with the changes made to the data in the DataSet. If the Insert, Update, or Delete command hasn't been set when the Update method needs to call them, an exception is thrown. You can specify these commands in several ways.

• Use the CommandBuilder object to generate automatically commands at run-time.

• Explicitly program the commands in code.

• Use the DataAdapter Design-Time Component and the DataAdapter Configuration Wizard.

Using the CommandBuilder Object

This approach is the simplest, but it's somewhat limited. It's similar to the BatchUpdate of ADO 2.X. By linking a CommandBuilder object to a specific DataAdapter object, the CommandBuilder will automatically generate the InsertCommand, UpdateCommand, and DeleteCommand properties needed by that DataAdapter. If any of these properties are not null references (Nothing in Visual Basic), a command object already exists for that property. The CommandBuilder won't override an existing command.

Note

As you might expect, a DataAdapter object must be specific to the .NET Data Provider that it is working with. Therefore in our code we must use a specific derived object, such as SqlDataAdapter, OledbDataAdapter, or OdbcDataAdapter.

For automatic command generation to work, the DataAdapter's SelectCommand must be set. The CommandBuilder uses the table schema obtained by the SelectCommand's Select statement to generate the corresponding Insert, Update, and Delete commands. Note that the columns returned by the SelectCommand must include at least one primary key or unique column.

Modifying the CommandText of the Select statement after the update commands have been automatically generated could cause exceptions to occur when one of the update commands is actually executed. If the original Select statement that the command generation was based on contained columns that don't exist in the modified statement, execution of one of the update commands by the DataAdapter's Update method may try to access these nonexisting columns and cause an exception to be thrown. To avoid this problem you should call the RefreshSchema method of the CommandBuilder after modifying the SelectCommand property of the DataAdapter or after modifying the CommandText of that command object.

Note

Even after the CommandBuilder has generated Insert, Update, and/or Delete commands, the corresponding properties of the DataAdapter are not modified. The CommandBuilder maintains the generated command objects internally. You can obtain references to these objects by calling the CommandBuilder's GetInsertCommand, GetUpdateCommand, or GetDeleteCommand methods.

Although the CommandBuilder is simple and easy to use, understanding its limitations is important. The main limitation is that you have no control over what it does it isn't configurable. It simply generates the update commands based on the provided Select statement, and there are no options. It is designed to generate commands for a single, independent database table. In other words, DataTables filled with the result of an SQL Join operation cannot be used with a CommandBuilder. Moreover, the commands are generated without considering that the table may be related to other database tables, which may result in foreign key violations when one of the database update operations is performed.

Another limitation of the CommandBuilder is that it won't generate the update commands if any table or column name includes special characters such as a space, period, or other nonalphanumeric characters even if the name is delimited in brackets. However, fully qualified table names (such as database.owner.table) are supported.

To retrieve and save data to the database with the DataAdapter, add another form to the DataSetCode project.

1. First, add a button below the btnDataAdapterFill button. Set the Name property of the new button to btnDataAdapterUpdates and set the Text property to DataAdapter Updates.

2. Add a new form, frmUpdates, to the DataSetCode project.

3. In the Properties window for frmUpdates, set its Text property to DataAdapter Updates.

4. Enlarge the size of frmUpdates.

5. From the Windows Forms tab of the Toolbox, add a DataGrid to frmUpdates and place it on the right side of the form.

6. In the Properties window, set the Name property of the DataGrid to grdDataGrid.

7. Enlarge the DataGrid so that it covers about 80 percent of the area of the form.

8. Add a button from the Windows Forms tab of the Toolbox in the upper-left corner of frmUpdates.

9. In the Properties window, set the Name property of the new button to btnLoad and set the Text property to Load.

10. Add a button below the Load button.

11. In the Properties window, set the Name property of the new button to btnUpdate, set the Text property to Update, and set the Enabled property to False.

12. Open the frmUpdates form in Code View and add the following lines to the top of the file:

     Imports System  Imports System.Data Imports System.Data.SqlClient 

13. Add the code shown in Listing 6.3 to frmUpdates.

Listing 6.3 Using the SqlCommandBuilder to generate automatically update commands to be used by the SqlDataAdapter

 Private dsEmployeeInfo As DataSet Private daEmployees As SqlDataAdapter Private conn As New SqlConnection( _     "server=localhost;uid=sa;pwd=;database=novelty") ' Use SqlCommandBuilder to automatically generate the update ' commands Private cbEmployees As SqlCommandBuilder Private Sub btnLoad_Click(ByVal sender As System.Object, _        ByVal e As System.EventArgs) Handles btnLoad.Click     dsEmployeeInfo = New DataSet ()     LoadCommandBuilder ()     'Config grid     Me.grdDataGrid.PreferredColumnWidth = 110     Me.grdDataGrid.AllowSorting = True     'Fill Data Set     daEmployees.Fill(dsEmployeeInfo, "Employees")     'Assign DataSet to DataGrid     Me.grdDataGrid.DataSource = _       dsEmployeeInfo.Tables("Employees")     Me.btnUpdate.Enabled = True End Sub Private Sub btnUpdate_Click(ByVal sender As System.Object, _        ByVal e As System.EventArgs)Handles btnUpdate.Click        daEmployees.Update(dsEmployeeInfo, "Employees") End Sub Private Sub LoadCommandBuilder ()     Dim param As SqlParameter     If conn.State = ConnectionState.Closed Then       conn.Open ()     End If     'Create New DataAdapter Object     Dim SQL As String     SQL = "Select FirstName, LastName, DepartmentID, Salary, ID from tblEmployee"     daEmployees = New SqlDataAdapter(SQL, conn)     'Use SqlCommandBuilder to automatically     'generate the update commands     cbEmployees = New SqlCommandBuilder(daEmployees) End Sub 

The main routine, LoadCommandBuilder, is called when the Load button is clicked on. This routine shows how to open a connection object explicitly (and how to avoid an exception when clicking on the Load button subsequent times) and how to set up the SqlDataAdapter (daEmployees) and SqlCommandBuilder (cbEmployees) objects. These two objects are created and initialized with forms of their constructors that accept the essential settings as parameters. The daEmployees constructor receives the Select string and the connection object, whereas the cbEmployees receives the SqlDataAdapter object.

Note

Only one DataAdapter or CommandBuilder object can be linked with each other at any given time.

All that remains to be done is to configure the grid, set the grid's DataSource property to the Employees table in the DataSet, and call the Fill method to load the DataSet and have the grid automatically display the data.

The Click event handler for the Update button contains a single line of code, which simply calls the Update method of the daEmployees DataAdapter.

Run the DataSetCode project and then click on the DataAdapter Updates button on the frmDataSets form. When the frmUpdates form is displayed, click on the Load button. That will cause the data to be read from the tblEmployee database table, loaded into the Employee Data-Table in the dsEmployeeInfo DataSet, and displayed in the grid as shown in Figure 6.2.

Figure 6.2. Data from tblEmployee displayed in a DataGrid

You can now test this form on your own. Go ahead and make whatever changes you like. Add rows by scrolling down to the last row of the grid, delete rows by selecting one or more rows and then pressing the Delete key, or change column values by editing values within the grid cells. Remember that these changes are not saved to the database until you click on the Update button. You can verify your changes by using any of your favorite tools for viewing database tables or by just clicking on the Load button to cause the fetching and reloading of the database data into the form's DataSet and grid.

Note

Although using the CommandBuilder to generate the required update commands requires a bare minimum of code, it does have a significant downside, even if its limitations don't pose a problem. The CommandBuilder must make an additional round-trip to the database server to retrieve the metadata that it needs to generate the commands. This capability is very useful and flexible when you're developing queries on the fly. However, if the queries are already known at design time, explicitly specifying the commands and their parameters in code, using either explicit update commands or the DataAdapter Configuration Wizard, will result in better performance.

Explicit Update Commands

If using the CommandBuilder is the extreme in simplicity for generating the required update commands, explicitly programming these commands in code is the extreme in flexibility but a lot more work. Each of the four commands (Select, Insert, Update, and Delete) must be designed and hand-coded. More often than not, once you've begun the effort to code them explicitly, you'll go all the way and write SQL Server stored procedures for each of the commands.

Listing 6.4 shows the SQL Server script for generating the four stored procedures. The SelectEmployee stored procedure (SP) simply selects all the columns from the tblEmployee table. The InsertEmployee SP expects four parameters one for each of the updatable columns. The ID column isn't updatable because it's an identity column. The UpdateEmployee SP expects the same four parameters for the updatable columns, plus a fifth parameter containing the original value of the ID column. This value of the ID column is used in the WHERE clause to select correctly the row to be updated (based on the primary key). The DeleteEmployee SP requires only the original value of the ID column as a parameter to select the correct row to be deleted.

Listing 6.4 SQL Server script to create stored procedures for the tblEmployee table

 IF EXISTS (SELECT * FROM sysobjects WHERE name =      'SelectEmployees' AND user_name(uid) = 'dbo')      DROP PROCEDURE [dbo].[SelectEmployees]; GO CREATE PROCEDURE [dbo].[SelectEmployees] AS   SET NOCOUNT ON; SELECT FirstName, LastName, DepartmentID, Salary, ID FROM      tblEmployee; GO IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertEmployee'     AND user_name(uid) = 'dbo')   DROP PROCEDURE [dbo].[InsertEmployee]; GO CREATE PROCEDURE [dbo].[InsertEmployee] (   @FirstName varchar (50),   @LastName varchar (70),   @DepartmentID int,   @Salary money ) AS   SET NOCOUNT OFF; INSERT INTO tblEmployee(FirstName, LastName, DepartmentID, Salary)      VALUES (@FirstName, @LastName,_@DepartmentID, @Salary); GO IF EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdateEmployee'     AND user_name(uid) = 'dbo')   DROP PROCEDURE [dbo].[UpdateEmployee]; GO CREATE PROCEDURE [dbo].[UpdateEmployee] (   @FirstName varchar (50),   @LastName varchar (70),   @DepartmentID int,   @Salary money,   @Original_ID int ) AS      SET NOCOUNT OFF; UPDATE tblEmployee SET FirstName = @FirstName, LastName = @LastName,      DepartmentID = @DepartmentID, Salary = @Salary WHERE (ID = @Original_ID);GO IF EXISTS (SELECT * FROM sysobjects WHERE name = 'DeleteEmployee'     AND user_name(uid) = 'dbo')   DROP PROCEDURE [dbo].[DeleteEmployee]; GO CREATE PROCEDURE [dbo].[DeleteEmployee] (   @Original_ID int ) AS   SET NOCOUNT OFF; DELETE FROM tblEmployee WHERE (ID = @Original_ID); GO 

Let's now return to our application code. First, we change the first line of code in the routine btnLoad_Click so that, instead of calling LoadCommandBuilder, it calls LoadExplicitCode. Also, some debugging was needed when we developed the explicit updates code, so we added a Try-Catch block to the btnUpdate_Click routine. It now looks like this:

 Private Sub btnUpdate_Click(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles btnUpdate.Click   Try        daEmployees.Update(dsEmployeeInfo, "Employees")   Catch es As SqlException        MessageBox.Show(es.Message)   End Try End Sub 

Finally, the code for setting the commands for the daEmployees DataAdapter is shown in Listing 6.5.

Listing 6.5 Routine to set up the four customs commands for the dsEmployees DataAdapter

 Private Sub LoadExplicitCode ()   Dim param As SqlParameter   If conn.State = ConnectionState.Closed Then     conn.Open ()   End If   'Create New DataAdapter Object   daEmployees = New SqlDataAdapter ()   'Set up custom Select Command (SP)   daEmployees.SelectCommand = New SqlCommand ()   With daEmployees.SelectCommand     .Connection = conn     .CommandType = CommandType.StoredProcedure     .CommandText = "SelectEmployees"   End With   'Set up custom Insert Command (SP)   daEmployees.InsertCommand = New SqlCommand ()   With daEmployees.InsertCommand     .Connection = conn     .CommandType = CommandType.StoredProcedure     .CommandText = "InsertEmployee"   End With   param = daEmployees.InsertCommand.Parameters.Add( _     New SqlParameter("@FirstName", SqlDbType.VarChar, 50))   param.Direction = ParameterDirection.Input   param.SourceColumn = "FirstName"   param.SourceVersion = DataRowVersion.Current   param = daEmployees.InsertCommand.Parameters.Add( _     New SqlParameter("@LastName", SqlDbType.VarChar, 70))   param.Direction = ParameterDirection.Input   param.SourceColumn = "LastName"   param.SourceVersion = DataRowVersion.Current   param = daEmployees.InsertCommand.Parameters.Add( _     New SqlParameter("@DepartmentID", SqlDbType.Int))   param.Direction = ParameterDirection.Input   param.SourceColumn = "DepartmentID"   param.SourceVersion = DataRowVersion.Current   param = daEmployees.InsertCommand.Parameters.Add( _     New SqlParameter("@Salary", SqlDbType.Money))   param.Direction = ParameterDirection.Input   param.SourceColumn = "Salary"   param.SourceVersion = DataRowVersion.Current   'Set up custom Update Command (SP)   daEmployees.UpdateCommand = New SqlCommand ()   With daEmployees.UpdateCommand     .Connection = conn     .CommandType = CommandType.StoredProcedure     .CommandText = "UpdateEmployee"   End With   param = daEmployees.UpdateCommand.Parameters.Add( _     New SqlParameter("@FirstName", SqlDbType.VarChar, 50))   param.Direction = ParameterDirection.Input   param.SourceColumn = "FirstName"   param.SourceVersion = DataRowVersion.Current   param = daEmployees.UpdateCommand.Parameters.Add( _     New SqlParameter("@LastName", SqlDbType.VarChar, 70))   param.Direction = ParameterDirection.Input   param.SourceColumn = "LastName"   param.SourceVersion = DataRowVersion.Current   param = daEmployees.UpdateCommand.Parameters.Add( _     New SqlParameter("@DepartmentID", SqlDbType.Int))   param.Direction = ParameterDirection.Input   param.SourceColumn = "DepartmentID"   param.SourceVersion = DataRowVersion.Current   param = daEmployees.UpdateCommand.Parameters.Add( _     New SqlParameter("@Salary", SqlDbType.Money))   param.Direction = ParameterDirection.Input   param.SourceColumn = "Salary"   param.SourceVersion = DataRowVersion.Current   param = daEmployees.UpdateCommand.Parameters.Add( _     New SqlParameter("@Original_ID", SqlDbType.Int))   param.Direction = ParameterDirection.Input   param.SourceColumn = "ID"   param.SourceVersion = DataRowVersion.Original   'Set up custom Delete Command (SP)   daEmployees.DeleteCommand = New SqlCommand ()   With daEmployees.DeleteCommand     .Connection = conn     .CommandType = CommandType.StoredProcedure     .CommandText = "DeleteEmployee"   End With   param = daEmployees.DeleteCommand.Parameters.Add( _     New SqlParameter("@Original_ID", SqlDbType.Int))   param.Direction = ParameterDirection.Input   param.SourceColumn = "ID"   param.SourceVersion = DataRowVersion.Original End Sub 

Note

The code to assign the values to each of the parameter objects could be written in a more compact way (fewer lines of code) by calling a different overloaded form of the Add method. That alternative form accepts values for all the required parameter properties in a single method call with a long list of parameters. The advantage of the approach used in the preceding code listing is that it is far more readable than the alternative.

The code in the routine LoadExplicitCode is rather long but is basically straightforward. It is easy to understand, once the interface (parameters and types) to the stored procedures have been determined. For each of the Command properties, a new instance of a SQLCommand object is created. We assign the common Connection object to it and set the CommandType and CommandText properties. We then need to create and configure all the command parameters required for each command.

Go ahead and play with the frmUpdates form, as you did before, to verify that it is working correctly. Although it should seem to be working the same as before, the difference is that by calling LoadExplicitCode it is using our custom commands to perform the database updates. This approach requires some more coding on our part, but it offers the advantages of greater flexibility, improved performance, and centralized management of database stored procedures.

Embedding Business Logic in the Update Commands

We mentioned earlier how using stored procedures as custom update commands for the DataAdapter allows us to embed some business logic in the stored procedures that are automatically invoked. The innovation here, compared to previous versions of ADO and other data access models, isn't the embedding of logic in stored procedures that has always been done. Rather, it is that these stored procedures are invoked automatically when performing a "batch" update, instead of having to be invoked explicitly from the application code.

To see how this approach works, all you need do is modify the stored procedure. Let's assume that our business logic says the following: If we are inserting a new employee record and the Salary column is either null (empty) or set to 0, we automatically set the employee's salary as a function of her department. We use some simple logic for this task. The employee's automatically assigned salary will be the employee's departmentID times $10,000. (We hope that your company uses a better algorithm for salary assignment!) The modified stored procedure now looks like this:

 CREATE PROCEDURE dbo.InsertEmployee    (     @FirstName varchar(50),     @LastName varchar(70),     @DepartmentID int,     @Salary money   )   AS     SET NOCOUNT OFF;     if (@Salary = 0 or @Salary is null)     begin        Do complicated salary calculations       set @Salary = @DepartmentID * 10000     end     INSERT INTO tblEmployee(FirstName, LastName,       DepartmentID, Salary) VALUES       (@FirstName, @LastName, @DepartmentID, @Salary) GO 

Note

Because the InsertEmployee stored procedure already exists, you need to delete (drop) the existing stored procedure or change the first line in the script to

 ALTER PROCEDURE dbo.InsertEmployee  

if you want to run the preceding script from the SQL Server Query Analyzer.

You can now run the DataSetCode project without making any changes to the application code. Add new employee records on the frmUpdates form and verify that the stored procedure is assigning the correct salaries to the automatically inserted rows.

Using the DataAdapter Design-Time Component

After dealing with all that code in the preceding section, we bet that you're probably wishing for some point-and-click code generation. Well, get ready for the DataAdapter Configuration Wizard!

The DataAdapter Configuration Wizard offers an array of options for configuring a DataAdapter object that is added to a form as a design-time component. It actually is more advanced than the design-time components that we've discussed so far. It not only provides a graphical interface to set many of the internal objects and properties of the component, but it also actually offers various options that affect the code that it automatically generates. Begin by doing the following:

1. Open the form frmUpdates in the form designer.

2. From the Data tab of the toolbox, drag a SqlDataAdapter component onto the design sur-face of frmUpdates. Because this component isn't visible at run-time, it will appear in the component tray beneath the form's design surface. The DataAdapter Configuration Wizard will automatically begin. Click on the Next button on the Welcome dialog box to proceed.

3. On the Data Connection dialog box, select a connection to the Novelty database. If none currently exists, you can create one by clicking on the New Connection button that displays the standard OLEDB Data Link properties tabbed dialog box. Once you have selected a valid connection, click on the Next button.

4. For the Query Type, you can specify either SQL statements, the creation of new procedures, or the use of existing stored procedures. Let's go with the default of using SQL statements, although in practice you may find it useful to have the wizard generate stored procedures for you. Click on the Next button.

5. The next dialog box is where you enter the Select statement to be used by the DataAdapter and as the basis for the other three update statements, if they are generated. Enter the following into the textbox:

    SELECT FirstName, LastName, DepartmentID, Salary, ID FROM tblEmployee  

   There are two additional buttons on this dialog. The Advanced Options button displays a dialog box with options that control how the commands are generated. The first checkbox specifies whether the three update commands should be generated (or whether you're just using the DataAdapter to fill a DataSet). The second checkbox specifies whether the Update and Delete commands should include a WHERE clause that detects whether the record has been modified at the database because it was originally loaded into the DataSet. The third checkbox specifies whether a Select statement should be appended to the Insert and Update statements in order to return the row filled with column values that are calculated at the server, such as Identity column values and default column values. The Query Builder button displays a standard query builder window, in order to graphically design the Select query statement instead of directly entering it into the textbox as we did in the step above.

6. Click on the Next button to see a summary of the steps taken by the wizard.

7. Click on the Finish button to apply the settings to the DataAdapter component.

Note

Once the DataAdapter component has been created, you can modify its properties and settings either through the Properties window or by running the Configuration Wizard again. The wizard can be started for an existing DataAdapter component by right-clicking on the component in the component tray and then selecting the Configure Data Adapter … menu item. You can also restart the wizard by selecting the component in the component tray and then clicking on the Configure Data Adapter … link in the Properties window (on the pane between the properties list and the Description pane).

Like the CommandBuilder, the DataAdapter wizard is designed to generate commands for a single, in dependent database table. However, this wizard offers several configuration options, such as using existing or new stored procedures, which make it very flexible and useful for writing production code.

We now need to link the DataAdapter, automatically named SqlDataAdapter1, to our existing program. For consistency, we also explicitly open the created connection, named Sql-Connection1, in our code.

The routine btnLoad_Click needs to be modified so that it calls LoadWizardCode instead of LoadExplicitCode. In addition, it needs to call the Fill method of our newly created DataAdapter. The routine btnUpdate_Click also needs to be modified so that it uses the new SqlDataAdapter1 component. Finally, we need to add the LoadWizardCode routine, whose only remaining task is to open the new connection. These three routines are shown in Listing 6.6.

Listing 6.6 Modified and added routines to use the SqlDataAdapter component in the existing application.

 Private Sub btnLoad_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles btnLoad.Click   dsEmployeeInfo = New DataSet()   'LoadCommandBuilder()   'LoadExplicitCode()   LoadWizardCode()   'Config grid   Me.grdDataGrid.PreferredColumnWidth = 110   Me.grdDataGrid.AllowSorting = True   'Fill Data Set   'daEmployees.Fill(dsEmployeeInfo, "Employees")   SqlDataAdapter1.Fill(dsEmployeeInfo, "Employees")   'Assign DataSet to DataGrid   Me.grdDataGrid.DataSource = _     dsEmployeeInfo.Tables("Employees")   Me.btnUpdate.Enabled = True   End Sub Private Sub btnUpdate_Click(ByVal sender As System.Object, _     ByVal e As System.EventArgs) Handles btnUpdate.Click   Try     'daEmployees.Update(dsEmployeeInfo, "Employees")     SqlDataAdapter1.Update(dsEmployeeInfo, "Employees")   Catch es As SqlException     MessageBox.Show(es.Message   End Try End Sub Private Sub LoadWizardCode()   If SqlConnection1.State = ConnectionState.Closed Then     SqlConnection1.Open()   End If End Sub 

In case you're wondering what code the wizard looks like, you can view it by opening frm-Updates in the Visual Studio code editor and expanding the Windows Form Designer generated code region. It is conceptually very similar to the code we wrote ourselves in Listing 6.5.

Note

We haven't changed the default object names assigned by the DataAdapter Configuration Wizard, such as SqlDataAdapter1 and SqlSelectCommand1. However, you can change the names to be more meaningful or to conform to your specific naming conventions. The design-time component names can be changed by selecting the component in the component tray beneath the form's design surface and then setting the Name property in the Properties window.

To change the names of the individual commands (such as SelectCommand and InsertCommand), continue in the Properties window for the DataAdapter component. Locate the command that you want to modify and click on the "+" to expand the desired command object. Doing so exposes all the properties of that command object, allowing you to modify the name and other properties, as desired.

As you've done with the previous versions of the DataSetCode project using the CommandBuilder and the explicit SQL commands, run the program to verify that this version also works correctly. Be sure to remember how quickly we generated this fully functional code. You'll appreciate it even more if you also have it generate stored procedures for you!

Using the DataAdapter design-time component offers an additional useful feature. It is the Preview Data feature, whereby you can see the data returned by the Select statement of the DataAdapter at design time. To use it, right-click on the DataAdapter component in the component tray and select the Preview Data item from the menu displayed. You can also display this dialog window by selecting the component in the component tray and then clicking on the Preview Data … link in the Properties window (on the pane between the properties list and the Description pane). Select the desired DataAdapter from the DataAdapter's listbox and then click on the Fill DataSet button. The results for the SqlDataAdapter that we just added to frm-Updates are shown in Figure 6.3.

Figure 6.3. Data from tblEmployee displayed in the DataAdapter Preview window

Business Case 6.1: Combining Multiple Related Tables

As we've pointed out in this chapter, none of the available techniques for specifying update commands easily lend themselves to updating multiple tables. This is particularly true if there is a Relation defined between them, as in the case where a parent-child relationship exists between the tables. Does that mean that ADO.NET can't handle such a situation? The fearless database developer at Jones Novelties, Incorporated, used the capabilities of ADO.NET to prove that such isn't the case. She developed a form that displays and allows updates to both customer and related order information. It also illustrates the use of a batch of SQL Server commands to fill both tables with only a single round-trip to the server. To build the form, she proceeded to:

1. Launch Visual Studio.NET

2. Create a new Visual Basic Windows Application project

3. Name the project BusinessCase6

4. Specify a path for where she wants the project files to be saved

5. Enlarge the size of Form1 and set its Name property to frmCustomersOrders and its Text property to Customers and Orders

6. Add a button named btnFill with Text property set to Fill, a button named btnUpdate with Text property set to Update, and a DataGrid named grdCustomersOrders

7. Arrange the controls as shown in Figure 6.4

   Figure 6.4. Arrangement of the controls on frmCustomersOrders

   

After adding

 Imports System.Data  Imports System.Data.SqlClient 

at the top of the file, she adds the code shown in Listing 6.7 within the body of the class definition for frmCustomersOrders.

Listing 6.7 Routines to load and update multiple related tables

 Private ds As DataSet   Private cn As New SqlConnection( _     "server=localhost;uid=sa;database=Novelty") Private Sub btnFill_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles btnFill.Click   Dim da As New SqlDataAdapter()   grdCustomersOrders.DataSource = Nothing   ds = New DataSet()   ' Set up batch select command   da.SelectCommand = New SqlCommand()   da.SelectCommand.Connection = cn   da.SelectCommand.CommandType = CommandType.Text   da.SelectCommand.CommandText = _     "select * from tblCustomer; select * from tblOrder"   ' Table mappings for clear names   da.TableMappings.Add("Table", "Customers")   da.TableMappings.Add("Table1", "Orders")   ' Load Data   da.Fill(ds)   ' Manually add relation ds.Relations.Add("Customer_Orders", _      ds.Tables("Customers").Columns("ID"), _      ds.Tables("Orders").Columns("CustomerID"))   ' Display the data   grdCustomersOrders.DataSource = ds End Sub Private Sub btnUpdate_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles btnUpdate.Click   ' create and config DataAdapters   Dim daCustomers As New SqlDataAdapter( _     "select * from tblCustomer", cn) Dim daOrders As New SqlDataAdapter( _   "select * from tblOrder", cn)   Dim cbCustomers As New SqlCommandBuilder(daCustomers)   Dim cbOrders As New SqlCommandBuilder(daOrders)   Try     'Now, table by table, in "correct" order     Dim ChangedTable As New DataTable()     ' Deleted rows in child table     ChangedTable = _       ds.Tables("Orders").GetChanges(DataRowState.Deleted)     If Not ChangedTable Is Nothing Then       daOrders.Update(ChangedTable)     End If     ' All changed rows in parent table     ChangedTable = ds.Tables("Customers").GetChanges     If Not ChangedTable Is Nothing Then       daCustomers.Update(ChangedTable)     End If     ' Added or modified rows in child table     ChangedTable = _     ds.Tables("Orders").GetChanges(DataRowState.Added _       Or DataRowState.Modified)     If Not ChangedTable Is Nothing Then       daOrders.Update(ChangedTable)     End If   Catch ex As Exception     MessageBox.Show(ex.Message)   End Try End Sub 

The first routine, btnFill_Click, reads both tables from the database in a single round-trip, by executing a batch of SQL Server commands. The different commands are separated by a semicolon (';') in the CommandText string.

The DataSet default table names of Table and Table1 are mapped to the more meaningful names of Customers and Orders, in the lines

 ' Table mappings for clear names  da.TableMappings.Add("Table", "Customers") da.TableMappings.Add("Table1", "Orders") 

Note

We discuss table and column mappings in detail in Chapter 7.

After the DataSet ds has been filled with the data, a DataRelation is created to link the two tables, with the Customers table being the parent table and the Orders table being the child table. The last line of code in the routine binds the DataSet to the grid to display the data.

The second routine, btnUpdate_Click, causes changes in both tables to be updated to the database. Here the data integrity of a parent-child relationship must be ensured. Unfortunately, that doesn't happen automatically. Jones's database developer needs to group types of changes and then execute them in the correct order. For two tables that have a parent-child relationship, she should execute the changes in the following order.

1. Delete rows in the child table

2. Insert, update, and delete rows in the parent table

3. Insert and update rows in the child table

To obtain the appropriate changes, the routine makes calls to the GetChanges method of the appropriate table. It specifies the desired filter on the row state, as required. Each call to GetChanges returns a DataTable containing only those rows that have been changed (subject to the row state filter). If there are no changed rows, Nothing is returned. As long as there is at least one changed row, the DataAdapter's Update method is called to actually update the database. The code in this function is wrapped inside a Try-Catch block in case any errors occur while trying to update the database.

Now it is time to check out the database developer's form. To do so, follow these steps.

1. Run the BusinessCase6 project and then click on the Fill button. Doing so causes the DataSet to be filled with the data from the Novelty database. However, as the line of code

    grdCustomersOrders.DataSource = ds  

   binds the entire DataSet, rather than a specific DataTable, to the grid, all that is displayed is the beginnings of the grid and "+", as shown in Figure 6.5, indicating that you can expand what is being displayed.

   Figure 6.5. The initial display of frmCustomersOrders, after filling the DataSet with data

   

2. Click on the "+" to expand the grid view. The grid now displays two Weblike links, one for each table in the DataSet.

3. Click on the Customers link. The grid now displays the rows of data in the Customers table. Note that each row in the Customers table has a "+" to its left, indicating that the table is related to one or more other tables. Clicking on the "+" expands the list of DataRelations for that table. In this case, only one link, for the relation Customer_Orders, was created in the routine btnFillClick, as shown in Figure 6.6.

   Figure 6.6. CustomerOrders Relation link for a row in the Customers table

   

4. Click on the Customer_Orders link for the first row. This uses the definition of the Customer_Orders DataRelation to fetch and display the rows in the Orders table that are related to the current Customers row.

   Note

   At any point while navigating through DataTables and DataRelations in the grid, you can retrace your steps by pressing the Navigate Back arrow at the top of the grid.

   No Orders rows should be displayed, because the Jones Novelties company still has its orders data in an Access database and hasn't yet moved them to the new SQL Server database. Jones wants to test the new system being developed, so he will have his developer input test data via this form. Doing so will not only create test data, but it will also verify that new rows can be inserted! The grid does present a new row to be inserted into the table. The CustomerID field is already set to the value of 1 because the grid recognized that it is the value in the related customer row. Go ahead and add values for the OrderDate and Amount fields. There is no point in adding a value for the ID field because it is an identity column and will automatically be assigned a value by the database when the record is inserted.

5. Click on the Update button to execute the routine btnUpdate_Click from Listing 6.7 and cause the database to be updated.

6. You can verify that the update (New row added) was made against the database by clicking on the Fill button to cause the data from the database to be reloaded in the DataSet and grid. Navigate to the Order table of the first Customer row, and the new row that you just inserted should be there!

Feel free to make additional changes to the database by adding, deleting, and modifying rows in both tables and verifying that the updates indeed were performed.

Note

If you're wondering why you can successfully delete a Customer row even though it still contains Orders rows, it is because the default behavior of the ForeignKeyConstraint created by the Customer_Orders Relation is to cascade deletions (and updates) made to a parent table down to the child table.