Saving Data in ADO.Net

ADO.Net is designed to operate as a "disconnected recordset" (using the older ADO terminology). That means that ADO.Net does not hold a connection to the database and does not automatically send updates back to the database.

A "disconnected" database model is one where your application makes a connection to the database long enough to take a snapshot of the data and then disconnects... you then add/delete/modify the data that is cached locally on your PC... and when you're ready, you reconnect to the database and merge your locally cached changes back to the original database. If you're shaking your head in disbelief about now, then join the crowd... we all looked at this at one time and wondered.

This might be good time to review Beginning ADO.Net

ADO.Net Object Model

If you recall, you use the DataAdapter's Fill() method to read the contents of a table in a database and fill our locally-cached DataTable object. So after you've added, deleted, and modified the data in the DataTable, it's time to write those changes back... to do this, you use the DataAdapter's Update() method.

The Update() method actually scans the locally-cached DataTable to pick out those rows that have been modified. It then issues SQL commands to either INSERT, DELETE, or UPDATE each row as appropriate. At first, this seems like a lot of work... until you realize that the Update() method performs all of this for you. There is even a short-cut method of creating those 3 SQL commands called the CommandBuilder. After you give the CommandBuilder a copy of the SELECT command you used originally, it will generate the 3 SQL commands for INSERT, DELETE, and UPDATE.

Warning: The Update() method *requires* that the underlying database table have a primary key! This was never a requirement for "classic ADO"... so I expect you'll get stumped by this at least once.

Summary

There are 3 steps to saving data in ADO.Net:

Get a locally cached copy of the data with the Fill() method
Write code (or use a control) that makes changes to the locally cached copy
Save the changes back to the underlying database with the Update() method

The Example Code

Let's do a simple example:

Dim con As New OleDbConnection()
Dim cmd As New OleDbCommand()
Dim da As New OleDbDataAdapter()
Dim cb As OleDbCommandBuilder
Dim dr As DataRow
Dim dt As New DataTable()

' a Connection object to locate our database
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\test.mdb"

' a Command object to get the data
cmd.Connection = con
cmd.CommandText = "Select * from Table1"

' a DataAdpter object to fill the data
da.SelectCommand = cmd
da.Fill(dt)

' let's add a record
dr = dt.NewRow()
dr("LName") = "Doe"
dr("FName") = "John"
dr("IDNo") = 120
dt.Rows.Add(dr)

' let's delete a record
dr = dt.Rows(2)
dr.Delete()

' let's edit the contents of a record
dr = dt.Rows(1)
dr("ID") = -120

' Create the SQL insert/delete/update commands that will be used by the data adapter's
' update method below. This is optional... you *could* populate the 3 properties of the
' data adapter yourself (but why?)
cb = New OleDbCommandBuilder(da)
Try
    ' Now let's write the changes back to the database
     da.Update(dt)
Catch ex As Exception
     MsgBox("Yikes, Can't update the database" & vbCr & ex.Message, _
      MsgBoxStyle.Critical, "Error!")
     Exit Sub
End Try

If you've got a sharp eye, you'll notice that we create a new OleDbCommandBuilder object, and yet never make any references to it! The mystery is solved when you figure out that the DataAdapter object is passed "by reference" and the changes are made to the DataAdapter directly.

Consider this scenario... let's say you've got a DataGrid on a window form and have bound a DataTable to it. The users have now used the DataGrid to make changes. You put a Button on the form that says "Save Change". The problem is, you're not actually sure anyone made any changes... so how do you know?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
 Handles Button1.Click
    Dim dt_changes As DataTable
    Dim cb As OleDbCommandBuilder
    Dim rows As Integer

    ' Let's see if anybody made any changes to the table via the datagrid
    dt_changes = dt.Changes()

    If Not IsNothing(dt_changes) Then
        Try
           cb = New OleDbCommandBuilder(da)
           ' Update the changes to the database. 
           rows = da.Update(dt_changes)
           MsgBox(rows & " updated")
        Catch ex As Exception
           MsgBox("Yikes, Can't save the changes!" & vbCrLf & ex.Message, _
            MsgBoxStyle.Exclamation)
        End Try
    Else
        MsgBox("Hey, there's nothing to update")
    End If

    ' No need to close any database connections... it's already closed
End Sub

BTW: This example assumes that both the DataAdapter (da) and DataTable (dt) are declared elsewhere

Data Synchronization

After the Update() the copy of the local-cached DataTable isn't exactly synchronized with the underlying source database. That's because somebody else might have added a few rows of data after you cached the data to your PC. To solve this, you just issue another Fill() command to instruct the DataAdapter to go back and pick up whatever is missing. The FIll() method is smart enough to just read those rows that you need, without wasting time on rows that it knows you already have.

Downloads/Links

Download the VB.Net Source code example used in this article: ADONet_Test.zip