The way the architecture works with the DataAdapter is that you can make any changes you want to the filled DataSet, and it won't affect the database until you call the Update method. When Update is called, the DataAdapter will attempt to execute each query (UPDATE, INSERT, DELETE) on every row of the DataSet that has been updated, inserted, and deleted. For example, if you call Delete on a row on the DataSet, then when Update is called on the DataAdapter, the DeleteCommand of the DataAdapter will be called using the particular row in the DataSet.
Note: Keep in mind that this Update is different than a SQL UPDATE statement.
To insert, update, and delete data using a DataAdapter, you create an OleDbCommand object with INSERT, UPDATE, and DELETE statements and set the InsertCommand, UpdateCommand, and DeleteCommand properties of the data adapter to OleDbCommand. You can avoid building Command objects manually by using CommandBuilder.
As you saw in the DataTable, DataRow, and DataColumn sections at the beginning of this article, you can use the Delete method of a DataRow to delete a row. The AddNew method of the DataTable adds a row to a DataTable. To change an existing row's data, you can directly assign a row's value. To save data to the data source, you need to call the DataAdapter's Update method.
Note: The Tables member of DataSet represents all DataTable objects attached to a DataSet.
Listing 5-46 Creates a new DataRow, sets the data of DataRow members, and adds rows to the table using the Add method of DataTable.Rows.
Listing 5-46. Adding a DataRow to a DataTable
Dim row As DataRow = ds.Tables("Customers").NewRow()
row("CustomerId") = "TstID "
row("ContactName ") = "Lana Jackob "
row("CompanyName") = "Mindcracker Inc. "
ds.Tables("Customers").Rows.Add(row)
Listing 5-47 deletes data by using the DataRow's Delete method.
Listing 5-47. Deleting a Data Row From a Data Table
Dim row1 As DataRow = ds.Tables("Customers").Rows.Find("TstID")
row1.Delete()
In this example, I'll show you how to add edit, and remove, data from the Northwind database. I'll display the result of the Customers table in a DataGrid after adding, updating, and deleting data from the table. To test this source code create a Windows application, drop a DataGrid, and three buttons- InsertCommand, UpdateCommand, and DeleteCommand to the form. Listing 5-48 show the code on the InsertCommand button-click event. As you can see from listing 5-48, I created a filled DataSet and created a new DataRow using the DataTable.NewRow method. After creating a DataRow, I set its column values and called the Data Adapter's Update method and displayed data in the DataGrid. I added new rows to the Customers table with CustomerId, CustomerName, and CompanyName (named TstID, Lana Jackob, and Mindcracker Inc., respectively).
Listing 5-48. Adding data using a Data Adapter's Update method
Private Sub InsertCommand_click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data source = C:/Northwind.mdb"
Dim conn As New OleDbConnection(ConnectionString)
Dim row As DataRow
Dim ds As New DataSet()
Try
conn.Open()
Dim adapter As New OleDbDataAdapter("SELECT * FROM Customers", conn)
Dim cmdBuilder As New OleDbCommandBuilder(adapter)
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
adapter.Fill(ds, "Customers")
row = ds.Tables("Customers").NewRow()
row("CustomersId") = "TstID"
row("ContactName") = "Lana Jackob"
row("CompanyName") = "Mindcracker Inc. "
ds.Tables("customers").Rows.Add(row)
adapter.Update(ds, "Customers")
dataGrid1.DataSource = ds.DefaultViewManager
Catch exp As OleDbException
MessageBox.Show(exp.Message.ToString())
End Try
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Sub
Listing 5-49 shows an example that edits row data. The Find method returns the row with CustomerId = TstID. After that I assign values of the row and call the DataAdapter's Update method.
Listing 5-49. Updating data using the DataAdapter's Update method
Private Sub UpdateCommand_click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data source =C:/Northwind.mdb"
Dim conn As New OleDbConnection(ConnectionString)
Dim ds As New DataSet()
Try
conn.Open()
Dim adapter As New OleDbDataAdapter("SELECT * FROM Customers", conn)
Dim cmdBuilder As New OleDbCommandBuilder(adapter)
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
adapter.Fill(ds, "Customers")
Dim row1 As DataRow = ds.Tables("Customers").Rows.Find("TstID")
row1("ContactName ") = "Stacy Monter "
row1("CompanyName") = "Monter Inc. "
adapter.Update(ds, "Customers")
dataGrid1.DataSource = ds.DefaultViewManager
Catch exp As OleDbException
MessageBox.Show(exp.Message.ToString())
End Try
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Sub
Deleting a row is simple. Listing 5-50 deletes the row with the value TstID. As you can see from the code, I called the Find method to find the row and called the Delete method of the DataRow. After that I called the DataAdapter's Update method to save the changes to the database table.
Listing 5-50. Deleting data using the DataAdapter's Update method
Private Sub DeleteCommand_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim ConnectionString As String = "provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\ Northwind.mdb"
Dim conn As New OleDbConnection(ConnectionString)
Dim ds As New DataSet()
Try
conn.Open()
Dim adapter As New OleDbDataAdapter("SELECT * FROM Customers", conn)
Dim cmdBuilder As New OleDbCommandBuilder(adapter)
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
adapter.Fill(ds, "Customers")
Dim row1 As DataRow = ds.Tables("Customers").Rows.Find("TstID")
row1.Delete()
adapter.Update(ds, "Customers")
dataGrid1.DataSource = ds.DefaultViewManager
Catch exp As OleDbException
MessageBox.Show(exp.Message.ToString())
End Try
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Sub
Conclusion
Hope this article would have helped you in understanding Updating the Database Using the Update Method in ADO.NET. See my other articles on the website on ADO.NET.