Dataadapter with CommandBuilder - OLEDB

The OleDbDataAdapter, a component of the ADO.NET Data Provider, plays a vital role in managing data modifications through its four distinct Command objects. These Command objects include the InsertCommand, UpdateCommand, and DeleteCommand properties, which enable updates to the database based on modifications made to a DataSet object.

To facilitate this process, OleDbCommand objects can be manually created in code or automatically generated using the OleDbCommandBuilder object. When utilizing the OleDbCommandBuilder, it interacts with the associated OleDbDataAdapter by opening the Connection to the database. Each time the OleDbCommandBuilder constructs the action queries, it performs a round trip to the server. Upon completion, it closes the Connection.

The following source code exemplifies how to employ the OleDbDataAdapter object to update an OLEDB Data Source. This entails executing data modifications on a DataSet object that has been populated with data from a specific table in the database. The OleDbCommandBuilder object is utilized to facilitate this process.

Full Source VB.NET
Imports System.Data.OleDb Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim connetionString As String Dim connection As OleDbConnection Dim oledbAdapter As OleDbDataAdapter Dim oledbCmdBuilder As OleDbCommandBuilder Dim ds As New DataSet Dim i As Integer Dim sql As String connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your mdb filename;" connection = New OleDbConnection(connetionString) sql = "select * from tblUsers" Try connection.Open() oledbAdapter = New OleDbDataAdapter(sql, connection) oledbCmdBuilder = New OleDbCommandBuilder(oledbAdapter) oledbAdapter.Fill(ds) For i = 0 To ds.Tables(0).Rows.Count - 1 ds.Tables(0).Rows(i).Item(2) = "neweamil@email.com" Next oledbAdapter.Update(ds.Tables(0)) connection.Close() MsgBox("Email address updates !") Catch ex As Exception MsgBox(ex.ToString) End Try End Sub End Class

In the above code, we first establish a connection to the database using the supplied connection string. Next, we create an OleDbDataAdapter and specify the SQL SELECT statement to retrieve data from the desired table.

An OleDbCommandBuilder is instantiated and associated with the OleDbDataAdapter. This enables the automatic generation of the InsertCommand, UpdateCommand, and DeleteCommand objects required for updating the database based on the modifications made to the DataSet.

A DataSet is created to store the retrieved data. The Fill method of the OleDbDataAdapter is used to populate the DataSet with data from the specified table in the database.

Subsequently, modifications can be made to the data within the DataSet as needed.

Finally, to persist the modifications to the database, the Update method of the OleDbDataAdapter is called, passing in the DataSet and the name of the table. This triggers the execution of the appropriate action queries generated by the OleDbCommandBuilder, resulting in the updates being applied to the OLEDB Data Source.






Related Topics
  1. Dataadapter with dataset - sql sever
  2. Dataadapter with dataset - OLEDB Data Source
  3. Dataadapter SelectCommand - Sql Server
  4. Dataadapter SelectCommand - OLEDB Data Source
  1. Dataadapter InsertCommand - Sql Server
  2. Dataadapter InsertCommand - OLEDB Data Source
  3. Dataadapter UpdateCommand - Sql Server
  4. Dataadapter UpdateCommand - OLEDB Data Source
  5. Dataadapter DeleteCommand - Sql Server
  6. Dataadapter DeleteCommand - OLEDB Data Source
  7. Dataadapter with CommandBuilder - Sql Server
  8. Dataadapter with DataGridView - Sql Server
  9. Dataadapter with DataGridView - OLEDB

Related Links
  1. VB.NET DataAdapter.Fill
  2. Display Selected range of records in VB.NET