How to DataAdapter CommandBuilder in OLEDB

The OleDbDataAdapter is an integral component of the ADO.NET Data Provider, offering robust functionality for efficient data management. It plays a vital role in managing four distinct Command objects within its scope. Notably, the InsertCommand, UpdateCommand, and DeleteCommand properties of the OleDbDataAdapter object are responsible for seamlessly updating the database with data modifications executed on a DataSet object.


To accomplish this, OleDbCommand objects can be manually created in code or automatically generated using the OleDbCommandBuilder object. The OleDbCommandBuilder facilitates this process by opening the Connection associated with the OleDbDataAdapter and performing round trips to the server when constructing the action queries. Once the task is completed, it promptly closes the Connection.

To exemplify the practical implementation of the OleDbDataAdapter object in updating an OLEDB Data Source with data modifications executed on a DataSet object populated with data from a database table, the following C# Source Code demonstrates the necessary steps for achieving this functionality.

Full Source C#
using System; using System.Data; using System.Data.OleDb; using System.Windows.Forms; namespace WindowsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connetionString = null; OleDbConnection connection ; OleDbDataAdapter oledbAdapter ; OleDbCommandBuilder oledbCmdBuilder ; DataSet ds = new DataSet(); int i = 0; string sql = null; 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; i <= ds.Tables[0].Rows.Count - 1; i++) { ds.Tables[0].Rows[i].ItemArray[2] = ""; } oledbAdapter.Update(ds.Tables[0]); connection.Close(); MessageBox.Show ("Email address updates !"); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } }