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