Dataadapter with CommandBuilder - Sql Server

The DataAdapter is an essential component of the ADO.NET Data Provider. Its primary purpose is to facilitate the management of four distinct Command objects. Specifically, the SqlDataAdapter object possesses three crucial properties: InsertCommand, UpdateCommand, and DeleteCommand. These properties enable the synchronization of data modifications performed on a DataSet object with the corresponding changes in the database.

SqlCommandBuilder object

To accomplish this synchronization, SqlCommand objects are assigned to the aforementioned properties. These SqlCommand objects can be manually created in code or automatically generated using the SqlCommandBuilder object. The flexibility of choosing between manual creation and automatic generation allows developers to tailor their approach based on the specific requirements and preferences of the application.

When utilizing the SqlCommandBuilder, it is important to note that it initiates a connection to the associated DataAdapter and performs a round trip to the server each time it is requested to construct the action queries. After completing its tasks, it closes the connection. This process ensures that the data modifications made on a DataSet object can be accurately and efficiently updated within a SQL Server database.

To illustrate this functionality, the following source code demonstrates how to effectively employ the SqlDataAdapter object in conjunction with the SqlCommandBuilder. This enables seamless updates to a SQL Server database based on data modifications performed on a populated DataSet object, which retrieves data from a specific table within the database.

Full Source VB.NET
Imports System.Data.SqlClient 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 SqlConnection Dim adapter As SqlDataAdapter Dim cmdBuilder As SqlCommandBuilder Dim ds As New DataSet Dim sql As String Dim i As Int32 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 To ds.Tables(0).Rows.Count - 1 ds.Tables(0).Rows(i).Item(2) = ds.Tables(0).Rows(i).Item(2) + 100 Next adapter.Update(ds.Tables(0)) connection.Close() MsgBox("Data updated ! ") Catch ex As Exception MsgBox(ex.ToString) End Try End Sub End Class

Conclusion

By utilizing the DataAdapter and its associated properties, developers can seamlessly manage data modifications and efficiently update the underlying database to reflect the changes made within the DataSet object. This enables a streamlined and robust data management process within the ADO.NET framework.






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