SqlCommand VS. SqlCommandBuilder

SQLCommand

The SqlCommand object plays a key role in executing SQL statements and Stored Procedures against the designated data source. It serves as a versatile tool capable of executing various types of SQL queries, including but not limited to Insert and Update operations. With the SqlCommand, developers can interact with the data source efficiently, facilitating data manipulation and retrieval with precision and reliability.

e.g.

SqlCommand cmd = new SqlCommand("your sql statements", Connection); cmd.ExecuteNonQuery();

SqlCommandBuilder

The SqlCommandBuilder offers a convenient mechanism for constructing and executing SQL queries based on the supplied select command. It facilitates seamless reflection of changes made to a DataSet or an instance of the SQL Server data. The CommandBuilder ensures that the associated DataAdapter object is aware of these changes and takes appropriate actions.

When utilizing the SqlCommandBuilder, the Connection associated with the DataAdapter is opened, and a round trip to the server is made each time the CommandBuilder is instructed to construct action queries. This ensures that the generated SQL statements accurately reflect the modifications made to the data source, maintaining data integrity and consistency.

Additionally, the SqlCommandBuilder object functions as a listener for RowUpdating events, particularly when the DataAdapter property is set. This enables automatic generation of SQL statements for updates to a single table when the SelectCommand property of the SqlDataAdapter is configured.

e.g.

SqlDataAdapter adapter = new SqlDataAdapter("your sql statement", connection); SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter); adapter.Fill(ds); //Here you can do any modification to the selected data ds.Tables[0].Rows[i].ItemArray[2] = Convert.ToInt32 (ds.Tables[0].Rows[i].ItemArray[2]) + 100; //Here save the data to the datasource adapter.Update(ds.Tables[0]);

The above code select data from database and update it with the help of SqlCommandBuilder object and saves back to the datasource.

C# SqlCommandBuilder Sample      VB.Net SqlCommandBuilder Sample

Conclusion

By Exploring the SqlCommandBuilder, developers can streamline the process of constructing and executing SQL queries while ensuring that changes to data are accurately reflected and propagated in a controlled and efficient manner.






Related Topics
  1. ADO.Net Interview Questions (Part-1)
  2. What are the advantage of ADO.Net
  3. Differences between classic ADO and ADO.NET
  4. What are managed providers
  1. Difference between Dataset and DataReader
  2. How to ADO.Net Connection Pooling
  3. How to Transaction in ADO.NET
  4. What is ADO.Net Locking
  5. Difference between Typed DataSets and UnTyped DataSets