Updating a DataSet

Updating a DataSet

You are welcome to write SQL from scratch (or use stored procedures), or you can use a command builder, introduced in the preceding section, to write SQL for you. We will use generated SQL to perform an update.

NOTE

All providers are not created equal. I have experienced some difficulty getting the command builder to generate SQL for IBM's UDB database, and there are articles on the Web about other data providers. In one particular case the problem is probably related to the data itself. The database is a legacy database that isn't keyed properly, and the absence of primary keys seems to be the culprit. You always have the option of writing SQL from scratch or using stored procedures.

In the disconnected ADO.NET model, the DataSet keeps track of changes to the data. When we are ready to post changes back to the provider, we use a connection, an adapter, and a command builder. The command builder will generate SQL for us, and the adapter will fill in the parameterized arguments for the changed data. Listing 11.10 demonstrates updating a DataSet with an OleDbCommandBuilder object.

Listing 11.10 Updating a DataSet with SQL Generated by OleDbCommandBuilder

 1:  Imports System.Data 2:  Imports System.Data.OleDb 3: 4:  Public Class Form1 5:      Inherits System.Windows.Forms.Form 6: 7:  [ Windows Form Designer generated code ] 8: 9:    Private Connection As OleDbConnection 10:   Private Adapter As OleDbDataAdapter 11:   Private Builder As OleDbCommandBuilder 12:   Private Customers As DataSet 13: 14:   Private Sub Form1_Load(ByVal sender As System.Object, _ 15:     ByVal e As System.EventArgs) Handles MyBase.Load 16: 17:     Connection = New OleDbConnection(Database.ConnectionString) 18:     Adapter = New OleDbDataAdapter("SELECT * FROM CUSTOMERS", _ 19:       Connection) 20: 21:     Customers = New DataSet("Customers") 22:     Adapter.Fill(Customers) 23: 24:     Builder = New OleDbCommandBuilder(Adapter) 25:     TextBox1.Text = Builder.GetUpdateCommand().CommandText 26: 27:     DataGrid1.DataSource = Customers.Tables(0) 28: 29:   End Sub 30: 31:   Private Sub ButtonUpdate_Click(ByVal sender As System.Object, _ 32:     ByVal e As System.EventArgs) Handles ButtonUpdate.Click 33: 34:     Adapter.Update(Customers) 35:     Customers.AcceptChanges() 36:   End Sub 37: 38:   Private Sub ButtonCancel_Click(ByVal sender As System.Object, _ 39:     ByVal e As System.EventArgs) Handles ButtonCancel.Click 40: 41:     Customers.RejectChanges() 42: 43:   End Sub 44: End Class 

The Windows Forms sample application, CustomBuilderDemo.sln, contains a DataGrid object, two buttons , and a TextBox control. The TextBox is used to show the generated update command. (You can download the code and experiment with the sample.)

NOTE

Notice that Database.ConnectionString was used from earlier examples. To use the Data.vb module, add it to your project. You will also need to add an App.config file to your project. To do so, you can use the Application Configuration applet in the FileAdd New Item dialog. If you use the Application Configuration template, the .config file will be generated for your assembly correctly, and you can copy and paste the <appSettings> configuration information from Listing 11.1.

Four private fields in lines 9 through 12 are used to support the form's behavior. We have used these four players before: OleDbConnection , OleDbDataAdapter , OleDbCommandBuilder , and DataSet . The basic behavior is that the four fields are initialized and the DataSet field is used to get data into DataGrid1 . This happens in the Form1_Load event in lines 14 through 29. The use of OleDbCommandBuilder in line 24 generates the UPDATE SQL statement, as well as INSERT and DELETE statements.

The first button is the update button, and the second button is a cancel button. The update button is associated with the event handler in lines 31 through 36, and the cancel button is associated with the event handler in lines 38 through 43. Because we generated the UPDATE SQL, actually updating the database is a relatively simple process.

Keep in mind that the DataSet is disconnected. This means that changes you made in the DataGrid are reflected in the DataSet but not the database. We need to invoke the OleDbDataAdapter.Update command on the DataSet to write the changes back to the database. The database update occurs in line 34. If the Update command does not raise an exception, Customers.AcceptChanges() in line 35 will run. AcceptChanges indicates that we want to keep the changes made to the DataSet too. This might seem backward, but it prevents you from needing to rehit the database if the database update fails. All you need to do on an exception is roll back the changes in the disconnected database. This is done by calling DataSet.RejectChanges . Calling RejectChanges is exactly how we implemented the cancel behavior (line 41).

It is worth mentioning that invoking Update on a DataSet saves all changes to the DataSet . Calling AcceptChanges or RejectChanges on the DataSet invokes the respective method on each DataTable in the DataSet . If you want to write changes only to a single table or row, pass that DataTable or DataRow (as the case may be) to the OleDbDataAdapter.Update command.