Bulk Operations using T-SQL Command in ADO.NET Core

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

Bulk Operations using T-SQL Command in ADO.NET Core

In this article, I will discuss Bulk operations using T-SQL Commands in ADO.NET Core with Examples. Please read our previous article discussing Bulk Operations using SqlDataAdapter in ADO.NET Core with Examples.

Bulk Operations using SqlCommand with SQL MERGE Statement

Using SqlCommand with a SQL MERGE statement, combined with Table-Valued Parameters (TVPs), enables us to perform multiple database operations, such as INSERT, UPDATE, and DELETE, in a single, efficient batch. The TVP acts as a structured way to pass a set of rows from your application into SQL Server, enabling bulk operations without requiring multiple individual commands or multiple round trips to the database.

This approach is ideal for scenarios where a large number of changes need to be applied simultaneously. Instead of sending separate commands for each INSERT, UPDATE, or DELETE, we send a single batch of data and let the MERGE statement handle the logic to determine which rows need to be inserted, updated, or removed.

• Table‑Valued Parameter (TVP): Allows you to pass an entire set of rows from your application up to SQL Server in one go, avoiding multiple round‑trips.
• MERGE Statement: Allows us to combine INSERT, UPDATE, and DELETE logic in a single atomic operation, based on how source rows match (or don’t match) target rows.
• Benefit: One network call, one transaction, and clear, maintainable SQL that handles all three operations together.

Example to understand Bulk Operations using SqlCommand with SQL MERGE Statement

Let us understand how to perform Bulk INSERT, UPDATE, and DELETE operations using SqlCommand with a SQL MERGE statement in a .NET Core Console application.

SQL Server Database Setup

Please execute the following in SQL Server Management Studio (SSMS) to create the database, target table, and a user-defined table type for the table-valued parameter (TVP):

-- Create ProductsDB database
CREATE DATABASE ProductsDB;
GO

-- Switch to ProductsDB database
USE ProductsDB;
GO

-- Create the target table for Products
CREATE TABLE Products (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Price DECIMAL(18,2) NOT NULL
);
GO

-- Define a table type matching the Products schema for TVP
-- User-defined table type for passing multiple rows with operation flags
CREATE TYPE ProductType AS TABLE (
    Id INT,
    Name NVARCHAR(100),
    Price DECIMAL(18,2),
    OperationFlag CHAR(1)    -- 'I' = Insert, 'U' = Update, 'D' = Delete
);
GO

-- Insert dummy rows
INSERT INTO Products (Name, Price) VALUES
    ('Product 1', 100.00),
    ('Product 2', 200.00),
    ('Product 3', 300.00),
    ('Product 4', 400.00);
GO

Why a TVP?

Passing rows from your app to SQL Server as a Table‑Valued Parameter avoids multiple round‑trips and plays nicely with a single MERGE statement.

• Single round‑trip: Instead of looping client‑side over inserts/updates/deletes, we send all rows at once.
• Set-based performance: SQL Server optimizes the MERGE internally for large batches.
• Clarity: Your C# code marks operations explicitly instead of maintaining separate lists or loops.

.NET Core Console Application Example:

The following example demonstrates how to efficiently synchronize a list of products from a .NET Core console application into a SQL Server table, performing inserts, updates, and deletes in a single operation by passing the data as a table-valued parameter (TVP) and implementing the logic with a single MERGE statement. The example code is self-explained, so please read the comments lines for a better understanding.

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

namespace BulkMergeConsoleApp
{
    public class Program
    {
        // Define the connection string to your SQL Server ProductsDB database.
        private const string ConnectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=ProductsDB;Trusted_Connection=True;TrustServerCertificate=True;";

        static async Task Main(string[] args)
        {
            try
            {
                DataTable productTable = CreateProductDataTable();

                // INSERT candidates
                productTable.Rows.Add(DBNull.Value, "New Product A", 10.00m, 'I');
                productTable.Rows.Add(DBNull.Value, "New Product B", 20.00m, 'I');
                productTable.Rows.Add(DBNull.Value, "New Product C", 30.00m, 'I');
                productTable.Rows.Add(DBNull.Value, "New Product D", 40.00m, 'I');

                // UPDATE candidate
                productTable.Rows.Add(1, "Updated Product 1", 15.00m, 'U');
                productTable.Rows.Add(3, "Updated Product 3", 15.00m, 'U');

                // DELETE candidates
                productTable.Rows.Add(2, DBNull.Value, DBNull.Value, 'D');
                productTable.Rows.Add(4, DBNull.Value, DBNull.Value, 'D');

                Console.WriteLine("Executing MERGE with Operation Flags...");
                int affected = await MergeProductsAsync(productTable);

                Console.WriteLine($"MERGE completed. Total rows affected: {affected}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
                Console.WriteLine(ex.Message);
            }
        }

        // Creates a DataTable matching the dbo.ProductType definition.
        static DataTable CreateProductDataTable()
        {
            var table = new DataTable();
            table.Columns.Add("Id", typeof(int));
            table.Columns.Add("Name", typeof(string));
            table.Columns.Add("Price", typeof(decimal));
            table.Columns.Add("OperationFlag", typeof(char));
            return table;
        }

        // Asynchronously executes a SQL MERGE statement using OperationFlag to control INSERT, UPDATE, DELETE.
        static async Task<int> MergeProductsAsync(DataTable products)
        {
            //Preparing the Raw SQL Satements with SQL MERGE for Bulk Insert, Update, and Delete Operations
            const string mergeSql = @"
                MERGE INTO Products AS Target
                USING @ProductSource AS Source
                    ON Target.Id = Source.Id
                WHEN MATCHED AND Source.OperationFlag = 'U' THEN
                    UPDATE SET
                        Target.Name = Source.Name,
                        Target.Price = Source.Price
                WHEN NOT MATCHED BY TARGET AND Source.OperationFlag = 'I' THEN
                    INSERT (Name, Price)
                    VALUES (Source.Name, Source.Price)
                WHEN MATCHED AND Source.OperationFlag = 'D' THEN
                    DELETE;
            ";

            int rows = 0;
            using (var connection = new SqlConnection(ConnectionString))
            {
                using(var command = new SqlCommand(mergeSql, connection))
                {
                    // Add structured parameter (TVP)
                    var tvpParam = command.Parameters.AddWithValue("@ProductSource", products);
                    tvpParam.SqlDbType = SqlDbType.Structured;
                    tvpParam.TypeName = "ProductType";

                    await connection.OpenAsync();                // Open connection asynchronously
                    rows = await command.ExecuteNonQueryAsync(); // Execute command asynchronously
                }
            }

            return rows;
        }
    }
}

Code Explanations:

• Prepares a DataTable containing rows to insert, update, or delete.
• Sends this data as a TVP to the database.
• Executes a SQL MERGE statement to apply all changes in a single operation.

Output:

When to Use This Manual Approach

This approach is well-suited for:

• Bulk data updates: When dealing with a batch of changes that include inserts, updates, and deletes.
• Data integration scenarios: Combining data from external sources into your database, where the incoming data needs to be reconciled with existing rows.
• Performance-critical operations: Reducing the overhead of multiple database round-trips by handling all operations in one network call.
• Complex reconciliation logic: The MERGE statement simplifies logic by handling different conditions (MATCHED, NOT MATCHED) directly in the SQL layer.

By using a structured approach with TVPs and the MERGE statement, we achieve efficient bulk operations, maintain cleaner code, and minimize database calls. The example demonstrates how to set up the database, prepare the data, and execute the merge logic, making it a valuable approach for real-world applications that require efficient, large data manipulation.

In the next article, I will discuss Pagination using ADO.NET Core and Stored Procedures with Examples. In this article, I explain Bulk operations using T-SQL Commands in ADO.NET Core with Examples. I would appreciate your feedback. Please post your feedback, questions, or comments about this Bulk operations using T-SQL Commands in ADO.NET Core article.