How to DataAdapter CommandBuilder in Sql Server

The DataAdapter serves as an integral component within the ADO.NET Data Provider, offering robust functionality for efficient data management. Specifically, the ADO.NET DataAdapter manages four distinct Command objects, including the InsertCommand, UpdateCommand, and DeleteCommand properties, which play a pivotal role in updating the database with modifications made to a DataSet object.

To achieve this, SqlCommand objects can be manually created in code or automatically generated using the SqlCommandBuilder object. The SqlCommandBuilder facilitates the process by opening the Connection associated with the DataAdapter, executing round trips to the server when constructing the action queries. Once the task is complete, it promptly closes the Connection.

SqlDataAdapter object

To illustrate the practical implementation of the SqlDataAdapter object in updating a SQL Server database, while utilizing 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.SqlClient; 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; SqlConnection connection ; SqlDataAdapter adapter ; SqlCommandBuilder cmdBuilder ; DataSet ds = new DataSet(); string sql = null; Int32 i ; connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"; connection = new SqlConnection(connetionString); sql = "select * from Product"; try { connection.Open(); adapter = new SqlDataAdapter(sql, connection); cmdBuilder = new SqlCommandBuilder(adapter); adapter.Fill(ds); for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { ds.Tables[0].Rows[i].ItemArray[2] = Convert.ToInt32 (ds.Tables[0].Rows[i].ItemArray[2]) + 100; } adapter.Update(ds.Tables[0]); connection.Close(); MessageBox.Show ("Data updated ! "); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } }