ADO.NET SqlCommandBuilder in C#
Posted by Superadmin on November 15 2023 11:59:31

ADO.NET SqlCommandBuilder in C#

Back to: ADO.NET Tutorial For Beginners and Professionals

 

ADO.NET SqlCommandBuilder in C# with Examples

In this article, I am going to discuss ADO.NET SqlCommandBuilder in C# with Examples using ADO.NET with Examples. Please read our previous article where we discussed SQL Injection and Prevention in C# with Examples.

 
ADO.NET SqlCommandBuilder in C#:

According to MSDN, SqlCommandBuilder Object automatically generates single-table commands that are used to reconcile changes made to a DataSet with the associated SQL Server database. This class cannot be inherited as it is a sealed class.

 

The ADO.NET SqlCommandBuilder object automatically generates INSERT, UPDATE, and DELETE SQL statements based on the SELECT statement for a single table. In order to automatically generate the SQL Statements using ADO.NET SqlCommandBuilder, we need to follow two steps. They are as follows:

  

Step 1: First create an instance of the ADO.NET SqlDataAdapter class and then we need to set the SelectCommand property of the SqlDataAdapter object to one select query as follows:
SqlDataAdapter dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = new SqlCommand(“SELECT * FROM Employee”, connection);

Step 2: In the next step, we need to create an instance of SqlCommandBuilder class and we also need to store the SqlDataAdapter object created in step1 with the DataAdapter property of the SqlCommandBuilder object as follows:
SqlCommandBuilder commandBuilder = new SqlCommandBuilder();
commandBuilder.DataAdapter = dataAdapter;

   

We can also combine the above two statements into a single statement. Here, we need to pass the SqlDataAdapter object as an argument to the SqlCommandBuilder class constructor as follows:
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

Example to Understand ADO.NET SqlCommandBuilder in C#:

Let us Understand ADO.NET SqlCommandBuilder in C# with an Example. We are going to use the following Employee table to understand this concept.

 

Example to Understand ADO.NET SqlCommandBuilder in C#

 

Please use the following SQL script to create the EmployeeDB and Employee Database table and populate the Employee table with the required sample data in SQL Server Database.

CREATE DATABASE EmployeeDB
 GO 
 
Use EmployeeDB
 GO 
 
 CREATE TABLE Employee
(
ID INT IDENTITY PRIMARY KEY,
Name VARCHAR(50),
Gender VARCHAR(20),
Salary INT,
Department VARCHAR(50)
)
 GO 
 
 INSERT INTO Employee VALUES('Pranaya Kumar','Male', 9000, 'IT')
 INSERT INTO Employee VALUES('Priyanka Dewangan','Female', 7600, 'HR')
 INSERT INTO Employee VALUES('Anurag Mohanty','Male', 9800, 'IT')
 INSERT INTO Employee VALUES('Rakesh Kumar','Male', 9900, 'IT')
 INSERT INTO Employee VALUES('Tarun Mallick','Male', 4400, 'HR')
 INSERT INTO Employee VALUES('Santosh Jena','Male', 3200, 'HR')
 INSERT INTO Employee VALUES('Bikash Rout','Male', 9830, 'HR')
 INSERT INTO Employee VALUES('Rohit Sharma','Male', 7200, 'IT')
 INSERT INTO Employee VALUES('Preety Tiwari','Female', 8700, 'IT')
 INSERT INTO Employee VALUES('Hina Sharma','Female', 6800, 'HR')
 GO

The following Example shows how to use the ADO.NET SqlCommandBuilder object in C# to automatically generate the UPDATE and DELETE Command. The following example code is self-explained, so please go through the comment lines.

   
using System; 
 using System.Data; 
 using System.Data.SqlClient; 
 
 namespace DataViewClassDemo
 { 
class Program
{ 
static void Main(string[] args) 
{ 
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConnectionString)) 
{ 
//Create the SqlDataAdapter instance by specifying the command text and connection object 
SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT * FROM Employee WHERE Gender='Male'", connection);
//SqlDataAdapter dataAdapter = new SqlDataAdapter();
//dataAdapter.SelectCommand = new SqlCommand("SELECT * FROM Employee WHERE Gender='Male'", connection);
// Associate SqlDataAdapter object with SqlCommandBuilder.
// At this point SqlCommandBuilder should generate T-SQL statements automatically
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
//SqlCommandBuilder commandBuilder = new SqlCommandBuilder();
//commandBuilder.DataAdapter = dataAdapter;
//Creating DataSet Object
DataSet dataSet = new DataSet();
//Filling the DataSet using the Fill Method of SqlDataAdapter object
dataAdapter.Fill(dataSet);
//Iterating through the DataSet
foreach (DataRow row in dataSet.Tables[0].Rows)
{
//Accessing the Data using the string column name as key
Console.WriteLine($"Id: {row["Id"]}, Name: {row[1]}, Salary: {row[2]}, Gender: {row["Gender"]}, Department: {row["Department"]}");
}
//Now Update First Row i.e. Index Position 0
DataRow dataRow = dataSet.Tables[0].Rows[0];
dataRow["Name"] = "Name Updated";
dataRow["Gender"] = "Female";
dataRow["Salary"] = 50000;
dataRow["Department"] = "Payroll";
//Provide the DataSet and the DataTable name to the Update method
//Here, SqlCommandBuilder will automatically generate the UPDATE SQL Statement
int rowsUpdated = dataAdapter.Update(dataSet, dataSet.Tables[0].TableName);
//The GetUpdateCommand() method will return the auto generated UPDATE Command
Console.WriteLine($"\nUPDATE Command: {commandBuilder.GetUpdateCommand().CommandText}");
if (rowsUpdated == 0)
{
Console.WriteLine("\nNo Rows Updated");
}
else
{
Console.WriteLine($"\n{rowsUpdated} Row(s) Updated");
}
//First fetch the DataTable
DataTable EmployeeTable = dataSet.Tables[0];
//Create a new Row
DataRow newRow = EmployeeTable.NewRow();
newRow["Name"] = "Pranaya Rout";
newRow["Gender"] = "Male";
newRow["Salary"] = 450000;
newRow["Department"] = "Payroll";
EmployeeTable.Rows.Add(newRow);
//Provide the DataSet and the DataTable name to the Update method
//Here, SqlCommandBuilder will automatically generate the INSERT SQL Statement
int rowsInserted = dataAdapter.Update(dataSet, dataSet.Tables[0].TableName);
//The GetInsertCommand() method will return the auto generated INSERT Command
Console.WriteLine($"\nINSERT Command: {commandBuilder.GetInsertCommand().CommandText}");
if (rowsInserted == 0)
{
Console.WriteLine("\nNo Rows Inserted");
}
else
{
Console.WriteLine($"\n{rowsUpdated} Row(s) Inserted");
}
//Now Delete 3nd Row i.e. Index Position 2
dataSet.Tables[0].Rows[2].Delete();
//Provide the DataSet and the DataTable name to the Update method
//Here, SqlCommandBuilder will automatically generate the DELETE SQL Statement
int rowsDeleted = dataAdapter.Update(dataSet, dataSet.Tables[0].TableName);
//The GetDeleteCommand() method will return the auto generated DELETE Command
Console.WriteLine($"\nDELETE Command: {commandBuilder.GetDeleteCommand().CommandText}");
if (rowsDeleted == 0)
{
Console.WriteLine("\nNo Rows Deleted");
}
else
{
Console.WriteLine($"\n{rowsUpdated} Row(s) Deleted");
}
}
Console.ReadKey();
}
}
}
Output:

ADO.NET SqlCommandBuilder in C# with Examples

 

Now, you can verify the database and you should see the UPDATE, INSERT, and DELETED as expected as shown in the below image.

ADO.NET Sqlcommandbuilder Update Not Working in C#

ADO.NET Sqlcommandbuilder Update Not Working in C#:

The following are the two common reasons why ADO.NET SqlDataAdapter.Update Method does not work.

1. If you are not associating the SqlCommandBuilder object with the SqlDataAdapter object. Without this association, the SqlCommandBuilder object does not know how to generate INSERT, UPDATE and DELETE statements.

SqlCommandBuilder commandBuilder= new SqlCommandBuilder(dataAdapter);

If the above statement is not present in your code, then SqlDataAdapter.Update() method will throw an exception – Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

2. The SelectCommand that is associated with the SqlDataAdapter object does not return at least one primary key or unique column. If this is the case you will get an exception – Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

Difference Between SqlCommand and SqlCommandBuilder in C#:

SQLCommand: The ADO.NET SQLCommand Object is used to execute SQL statements and Stored Procedures against the data source. It executes all kinds of SQL queries like INSERT, UPDATE, DELETE, etc. For example,
SqlCommand command = new SqlCommand(“T-SQL Statements or Stored Procedure”, Connection);
cmd.ExecuteNonQuery();
cmd.ExecuteReader();
cmd.ExecuteScaler();

SqlCommandBuilder: The ADO.NET SqlCommandBuilder object automatically generates INSERT, UPDATE and DELETE SQL statements based on the SELECT statement for a single table. To use the SqlCommandBuilder object, you need to associate the DataAdapter instance to the DataAdapter property of the SqlCommandBuilder object as follows:
SqlCommandBuilder commandBuilder = new SqlCommandBuilder();
commandBuilder.DataAdapter = dataAdapter;

In the next article, I am going to discuss How to Return More than one Result Set using ADO.NET SqlDataReader in C# with Examples. Here, in this article, I try to explain ADO.NET SqlCommandBuilder in C# with Examples. I hope you enjoy this ADO.NET SqlCommandBuilder in C# article.