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 key 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()); } } } }





Related Topics
  1. How to DataAdapter in Sql Server
  2. How to DataAdapter in OLEDB
  3. How to DataAdapter Select Command - Sql Server
  4. How to DataAdapter Select Command - OLEDB
  1. How to DataAdapter Insert Command - Sql Server
  2. How to DataAdapter Insert Command - OLEDB
  3. How to DataAdapter Update Command - Sql Server
  4. How to DataAdapter Update Command - OLEDB
  5. How to DataAdapter Delete Command - SQL SERVER
  6. How to DataAdapter Delete Command - OLEDB
  7. How to DataAdapter CommandBuilder in OLEDB
  8. How to DataAdapter DataGridView - Sql Server
  9. How to DataAdapter DataGridView - OLEDB

Related Links
  1. C# DataAdapter.Fill
  2. Selected range of records from database | C#