Back to: ADO.NET Core Tutorial For Beginners and Professionals

ADO.NET Core SqlCommandBuilder Class with Examples

In this article, I will explain how to use the ADO.NET Core SqlCommandBuilder class with Examples. Please read our previous article discussing the ADO.NET Core SQLDataAdapter Class.  ADO.NET Core provides classes to interact with relational databases, such as SQL Server, Oracle, and MySQL. It efficiently handles connections, commands, and data retrieval and manipulation. One key component is the DataAdapter, which fills a DataSet or DataTable with records from a database by executing a SELECT Statement.

When we use a SqlDataAdapter to fetch data into a DataSet or DataTable, modifying the data requires commands to persist those changes back to the database. SqlCommandBuilder handles this requirement by generating appropriate commands based on your SELECT statement.

What is the ADO.NET Core SqlCommandBuilder Class?

The SqlCommandBuilder class in ADO.NET Core is a utility class that plays an important role when working with a SqlDataAdapter. This class automatically generates SQL statements (INSERT, UPDATE, and DELETE) based on the SELECT command provided to a SqlDataAdapter when interacting with a single table.

Instead of manually writing SQL commands for updating a database (such as INSERT, UPDATE, and DELETE commands), SqlCommandBuilder can automatically generate these commands based on the SelectCommand that fills the DataSet or DataTable. This significantly simplifies database operations by reducing manual SQL coding, especially for simple CRUD operations.

How SqlCommandBuilder Works in ADO.NET Core?

The SqlCommandBuilder in ADO.NET Core works as follows:

• You need to create a SqlDataAdapter object with a SELECT command (e.g., SELECT * FROM Employees).
• You need to instantiate a SqlCommandBuilder object, passing the adapter into its constructor or setting the DataAdapter property.
• The SqlCommandBuilder then reads the DataAdapter.SelectCommand property to automatically build the InsertCommand, UpdateCommand, and DeleteCommand.
• Using the SqlDataAdapter.The Update() method automatically saves all modified rows in the DataTable back to the database using the commands generated by SqlCommandBuilder.

Database Setup

Please execute the following SQL Script using SSMS to create the EmployeesDB database and the Employees table in SQL Server. We have also included the SQL Script to insert dummy data.

-- Step 1: Create the Database
CREATE DATABASE EmployeesDB;
GO

-- Switch to the new database.
USE EmployeesDB;
GO

-- Step 2: Create the Employees Table
CREATE TABLE Employees
(
    EmployeeId INT IDENTITY(1,1) PRIMARY KEY,  -- Auto-incremented primary key.
    FirstName NVARCHAR(50) NOT NULL,           -- First name of the employee.
    LastName NVARCHAR(50) NOT NULL             -- Last name of the employee.
);
GO

-- Step 3: Insert Dummy Data into Employees Table
INSERT INTO Employees (FirstName, LastName)
VALUES 
('John', 'Doe'),
('Jane', 'Smith'),
('Robert', 'Johnson'),
('Emily', 'Davis'),
('Michael', 'Brown');

-- Optionally, you can check the inserted records.
SELECT * FROM Employees;
GO

ADO.NET Core SqlCommandBuilder Example:

In the example below, we will connect to a SQL Server database using ADO.NET Core, retrieve data from the “Employees” table, modify the data within a DataTable, and then use a SqlCommandBuilder to update the database with the changes. The following example code is self-explained, so please read the comment lines for a better understanding.

using System.Data;
using Microsoft.Data.SqlClient; 

namespace ADO_NET_SqlCommandBuilder_Example
{
    public class Program
    {
        static void Main(string[] args)
        {
            // Connection string to the SQL Server EmployeesDB database.
            // Adjust the Server, Database, and authentication details as per your environment.
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeesDB;Trusted_Connection=True;TrustServerCertificate=True;";

            // Define the SQL SELECT query to retrieve data from the Employees table.
            // Note: The query must return the primary key column (EmployeeId) for automatic command generation.
            string selectQuery = "SELECT EmployeeId, FirstName, LastName FROM Employees";

            // DataTable to hold the data fetched from the database.
            DataTable employeesTable = new DataTable();

            // The using statement helps ensure that the connection is properly closed and disposed.
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    // Open the connection to the database.
                    connection.Open();

                    // Create a SqlDataAdapter with the SELECT query and connection.
                    using (SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection))
                    {
                        // Create a SqlCommandBuilder to automatically generate the necessary SQL commands.
                        using (SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter))
                        {
                            // Fill the DataTable with data from the Employees table.
                            adapter.Fill(employeesTable);

                            // Display initial data in the console.
                            Console.WriteLine("Before any modifications:");
                            foreach (DataRow row in employeesTable.Rows)
                            {
                                Console.WriteLine($"ID: {row["EmployeeId"]}, First Name: {row["FirstName"]}, Last Name: {row["LastName"]}");
                            }

                            // ***********************************************
                            // Step 1: Perform an Update Operation
                            // ***********************************************
                            // Update the first employee's first name if at least one row exists.
                            if (employeesTable.Rows.Count > 0)
                            {
                                // Modify the first row: Change the 'FirstName' value.
                                employeesTable.Rows[0]["FirstName"] = "UpdatedName";
                            }

                            // ***********************************************
                            // Step 2: Perform an Insert Operation
                            // ***********************************************
                            // Insert a new employee record into the DataTable.
                            DataRow newRow = employeesTable.NewRow();
                            // Do not set the EmployeeId here because it's auto-generated by the database.
                            newRow["FirstName"] = "NewFirstName";
                            newRow["LastName"] = "NewLastName";
                            // Add the new row to the DataTable.
                            employeesTable.Rows.Add(newRow);

                            // ***********************************************
                            // Step 3: Perform a Delete Operation
                            // ***********************************************
                            // Delete an employee record from the DataTable.
                            // Here, we check if there are more than one rows and delete the second row as an example.
                            if (employeesTable.Rows.Count > 1)
                            {
                                // The Delete() method marks the DataRow for deletion.
                                employeesTable.Rows[1].Delete();
                            }

                            // ***********************************************
                            // Step 4: Update the Database
                            // ***********************************************
                            // The commandBuilder automatically generated the InsertCommand, UpdateCommand, and DeleteCommand
                            // based on the initial SELECT statement.
                            // Apply all modifications (update, insert, delete) back to the database.
                            int rowsAffected = adapter.Update(employeesTable);

                            Console.WriteLine($"\nTotal rows affected (committed to the database): {rowsAffected}");

                            // Optionally, re-fetch the data from the database to display the current state.
                            employeesTable.Clear();
                            adapter.Fill(employeesTable);

                            Console.WriteLine("\nAfter modifications:");
                            foreach (DataRow row in employeesTable.Rows)
                            {
                                Console.WriteLine($"ID: {row["EmployeeId"]}, First Name: {row["FirstName"]}, Last Name: {row["LastName"]}");
                            }
                        }
                    }
                }
                catch (SqlException sqlEx)
                {
                    // Handle and log SQL-specific exceptions.
                    Console.WriteLine("SQL error: " + sqlEx.Message);
                }
                catch (Exception ex)
                {
                    // Handle any other potential errors.
                    Console.WriteLine("Error: " + ex.Message);
                }
                finally
                {
                    // Ensure the connection is closed before exiting.
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
            }

            Console.ReadKey();
        }
    }
}

Code Explanation:

• Update Operation: This operation checks if there is at least one record and updates the FirstName field of the first record to “UpdatedName.”
• Insert Operation: Creates a new row using employeesTable.NewRow(). Assigns values to FirstName and LastName. Adds the new row to the DataTable. The EmployeeId is excluded because it is generated automatically by the database.
• Delete Operation: If more than one record exists, the code marks the second row (index 1) for deletion using Delete().
• Database Update: The adapter.Update(employeesTable) statement applies all DataTable changes (update, insert, delete) to the database. The SqlCommandBuilder automatically generates the necessary commands (UpdateCommand, InsertCommand, and DeleteCommand) based on the original SELECT query. After updating, the DataTable is cleared and refilled to display the updated state.

Output:

ADO.NET Core SqlCommandBuilder Key Properties and Methods

The following are the key methods and properties of the SqlCommandBuilder class:

• DataAdapter: This property associates the command builder with a specific SqlDataAdapter
• GetInsertCommand(): Returns the auto-generated INSERT command
• GetUpdateCommand(): Returns the auto-generated UPDATE command
• GetDeleteCommand(): Returns the auto-generated DELETE command

Let us understand the above methods and properties with an example. The following example prints the generated SQL command texts to the console, allowing you to verify the commands that have been created. The example code is self-explained, so please read the comment lines for a better understanding.

using System.Data;
using Microsoft.Data.SqlClient;

namespace ADO_NET_SqlCommandBuilder_Example
{
    public class Program
    {
        public static void Main(string[] args)
        {
            // Define the connection string to your SQL Server database.
            // Update the connection string parameters to match your SQL Server instance.
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeesDB;Trusted_Connection=True;TrustServerCertificate=True;";

            // Define a SELECT query to retrieve data from the Employees table.
            // The table must include a primary key (EmployeeId) for the command builder to generate commands.
            string selectQuery = "SELECT EmployeeId, FirstName, LastName FROM Employees";

            // Create a DataTable to hold the data retrieved from the database.
            DataTable employeesTable = new DataTable();

            // Create and open the SQL connection.
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    // Create a SqlDataAdapter using the SELECT query and connection.
                    using (SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection))
                    {
                        // Create a SqlCommandBuilder and associate it with the adapter.
                        // This is done via the constructor, which automatically sets the DataAdapter property.
                        using (SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter))
                        {
                            // Display the associated adapter using the DataAdapter property.
                            // It shows that the command builder is linked to our specified adapter.
                            Console.WriteLine("SqlCommandBuilder's associated DataAdapter:");
                            Console.WriteLine(commandBuilder.DataAdapter != null ? "DataAdapter is assigned." : "DataAdapter is NOT assigned.");

                            // Fill the DataTable with data from the Employees table.
                            adapter.Fill(employeesTable);

                            // Display the current rows in the Employees table.
                            Console.WriteLine("\nCurrent data in Employees table:");
                            foreach (DataRow row in employeesTable.Rows)
                            {
                                Console.WriteLine($"ID: {row["EmployeeId"]}, First Name: {row["FirstName"]}, Last Name: {row["LastName"]}");
                            }

                            // Retrieve and display the auto-generated INSERT command.
                            SqlCommand insertCommand = commandBuilder.GetInsertCommand();
                            Console.WriteLine("\nGenerated INSERT Command:");
                            Console.WriteLine(insertCommand.CommandText);

                            // Retrieve and display the auto-generated UPDATE command.
                            SqlCommand updateCommand = commandBuilder.GetUpdateCommand();
                            Console.WriteLine("\nGenerated UPDATE Command:");
                            Console.WriteLine(updateCommand.CommandText);

                            // Retrieve and display the auto-generated DELETE command.
                            SqlCommand deleteCommand = commandBuilder.GetDeleteCommand();
                            Console.WriteLine("\nGenerated DELETE Command:");
                            Console.WriteLine(deleteCommand.CommandText);
                        }
                    }
                }
                catch (SqlException sqlEx)
                {
                    Console.WriteLine("SQL error: " + sqlEx.Message);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error: " + ex.Message);
                }
                finally
                {
                    // Ensure the connection is closed.
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
            }
        }
    }
}

Code Explanation:

• DataAdapter Association: The SqlCommandBuilder is instantiated using: using (SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter)); Internally, the DataAdapter property of the command builder gets assigned to adapter, confirming the association. This ensures that any command generation will act upon this adapter.
• GetInsertCommand(): By calling commandBuilder.GetInsertCommand() generates and retrieves the auto-generated SQL INSERT statement based on the schema of the original SELECT query. The command is printed to show its CommandText.
• GetUpdateCommand(): Similar to the insert command, calling the commandBuilder.GetUpdateCommand() auto-generates the UPDATE SQL command. You can use this command to see which fields and parameters will be updated in the database.
• GetDeleteCommand(): Calling commandBuilder.GetDeleteCommand() returns the SQL DELETE statement to remove records. The command text is displayed so that you can review its structure.

Output:

SqlCommandBuilder Key Points:

• Dynamic Command Generation: When we set a SelectCommand for a SqlDataAdapter, SqlCommandBuilder can automatically generate the corresponding INSERT, UPDATE, and DELETE statements. These statements are generated from the SelectCommand’s schema.
• DataAdapter Integration: The SqlCommandBuilder works closely with the SqlDataAdapter to ensure that database updates are handled consistently and efficiently. Once the SqlCommandBuilder is associated with a SqlDataAdapter, you don’t need to set the InsertCommand, UpdateCommand, or DeleteCommand explicitly, and it takes care of that.
• Schema Dependency: The automatically generated commands are based on the metadata of the result set retrieved by the SelectCommand. This means that if your query returns a complex join or a result set without a primary key, SqlCommandBuilder may not be able to generate the commands. Therefore, the SelectCommand must reference a single table with a primary key.
• Code Maintenance and Rapid Development: Using SqlCommandBuilder, we can rapidly develop CRUD (Create, read, update, delete) operations during a project’s initial phases. Later, if needed, you can replace the auto-generated commands with more fine-tuned, performance-optimized SQL.

Can I use SqlCommandBuilder with Joins?

Generally, no. SqlCommandBuilder is designed to generate INSERT, UPDATE, and DELETE statements based on the schema of a single table. If your SELECT statement includes joins, SqlCommandBuilder cannot automatically determine which table to update or how to handle the relationships between tables. It expects a straightforward query that retrieves rows from only one table with a well-defined primary key. When using joins, you must specify the INSERT, UPDATE, and DELETE commands, as the builder cannot infer the necessary logic from a multi-table query.

Can I use SqlCommandBuilder with a DataSet?

Yes, you can use SqlCommandBuilder with a DataSet. The process is similar to using it with a DataTable since a DataSet essentially contains one or more DataTable objects. You would associate the SqlCommandBuilder with a SqlDataAdapter that fills a specific DataTable within the DataSet. The SqlCommandBuilder will generate the commands for that particular DataTable based on the SelectCommand you set on the SqlDataAdapter. This allows you to work with multiple tables in a DataSet while benefiting from the automatic command generation for each table’s data adapter.

The SqlCommandBuilder is a useful class in ADO.NET Core for quickly generating the SQL commands needed to update a SQL Server database based on changes made to a DataSet. While it simplifies CRUD operations for single-table scenarios, remember its limitations when working with more complex data manipulation.

In the next article, I will discuss ADO.NET Core Using Stored Procedures with Examples. In this article, I explain the ADO.NET Core SqlCommandBuilder Class with Examples. I want your feedback. Please post your feedback, questions, or comments about this ADO.NET Core SqlCommandBuilder article.