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.
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
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.
Download the VB.Net Source code example used in this article: ADONet_Test.zip