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.

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.)

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.