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

Bulk Operations using ADO.NET Core SqlDataAdapter

In ADO.NET Core, the SqlDataAdapter serves as a bridge between an in‑memory DataTable (or DataSet) and a SQL Server database. It encapsulates four commands, Select, Insert, Update, and Delete, and uses the RowState of each DataRow to determine which command to execute when you call the adapter.Update(dataTable). This allows you to batch together thousands of changes (additions, modifications, deletions) in one go, greatly simplifying bulk data synchronization without requiring manual loops over individual records.

Bulk Operations using SqlDataAdapter

When you need to push thousands of records to SQL Server in a single operation from .NET Core applications, the ADO.NET Core SqlDataAdapter class provides an elegant, fully managed approach. By combining it with a DataTable and enabling batching via UpdateBatchSize, you can perform high‑performance Bulk INSERT, UPDATE, and DELETE operations without writing low‑level T‑SQL loops or row‑by‑row commands.

Setting up the SQL Server Database

First, we need a SQL Server database with a table to work with. For demonstration, let’s create a database called ProductsDB, a table named Products, and stored procedures to perform Insert, Update, and Delete operations.

-- Create ProductsDB database
CREATE DATABASE ProductsDB;
GO

-- Switch to ProductsDB Database
USE ProductsDB;
GO

-- Create the Products table
CREATE TABLE Products
(
    ProductID		INT			IDENTITY(1,1) PRIMARY KEY,
    ProductName	NVARCHAR(100)	NOT NULL,
    Price			DECIMAL(10,2)		NOT NULL,
    Stock		INT			NOT NULL
);
GO

-- Select Stored Procedure
CREATE PROCEDURE sp_SelectProduct
AS
BEGIN
    SELECT ProductId, ProductName, Price, Stock
    FROM Products;
END;
GO

-- Insert Stored Procedure
CREATE PROCEDURE sp_InsertProduct
    @ProductName NVARCHAR(100),
    @Price DECIMAL(10, 2),
    @Stock INT
AS
BEGIN
    INSERT INTO Products (ProductName, Price, Stock)
    VALUES (@ProductName, @Price, @Stock);
END;
GO

-- Update Stored Procedure
CREATE PROCEDURE sp_UpdateProduct
    @ProductID INT,
    @ProductName NVARCHAR(100),
    @Price DECIMAL(10, 2),
    @Stock INT
AS
BEGIN
    UPDATE Products
    SET ProductName = @ProductName,
        Price = @Price,
        Stock = @Stock
    WHERE ProductID = @ProductID;
END;
GO

-- Delete Stored Procedure
CREATE PROCEDURE sp_DeleteProduct
    @ProductID INT
AS
BEGIN
    DELETE FROM Products
    WHERE ProductID = @ProductID;
END;
GO

How to specify the Batch Size and Command Timeout?

We can specify Batch Size and Command Timeout directly using properties of the SqlDataAdapter commands.

Specify Batch Size:

Batch size determines the number of commands sent to the database in a single batch. This significantly enhances performance when performing bulk operations. We need to set the UpdateBatchSize property on our SqlDataAdapter. The default is 1 (one row per batch). Set to 0 to send all rows in a single batch. The syntax is given below:

adapter.UpdateBatchSize = 100; // Executes in batches of 100 rows

Specify Command Timeout:

The Command Timeout controls how long the command waits for the operation to complete before throwing a timeout exception. We must explicitly set the timeout for each command (InsertCommand, UpdateCommand, DeleteCommand, and SelectCommand):

Set the timeout in seconds

adapter.SelectCommand.CommandTimeout = 120;
adapter.InsertCommand.CommandTimeout = 120;
adapter.UpdateCommand.CommandTimeout = 120;
adapter.DeleteCommand.CommandTimeout = 120;

If you are using a SqlCommandBuilder, you should generate commands first, then set their timeout explicitly:

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);

Generate commands

adapter.InsertCommand = commandBuilder.GetInsertCommand();
adapter.UpdateCommand = commandBuilder.GetUpdateCommand();
adapter.DeleteCommand = commandBuilder.GetDeleteCommand();

Set a timeout explicitly after generating commands

adapter.InsertCommand.CommandTimeout = 120;
adapter.UpdateCommand.CommandTimeout = 120;
adapter.DeleteCommand.CommandTimeout = 120;
adapter.SelectCommand.CommandTimeout = 120;

Bulk Operations using SqlDataAdapter and SqlCommandBuilder

The following example demonstrates how to perform bulk insert, update, and delete Operations using ADO.NET Core SqlDataAdapter. Here, we are using SqlCommandBuilder to auto-generate the INSERT, UPDATE, and DELETE Commands.

Use this when you want ADO.NET to infer simple CRUD commands automatically from a basic SELECT. Ideal for rapid development or simple schemas. 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 BulkOperationsDemo
{
    public class Program
    {
        static void Main()
        {
            // Define the connection string to your SQL Server ProductsDB database.
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=ProductsDB;Trusted_Connection=True;TrustServerCertificate=True;";

            try
            {
                Console.WriteLine("Bulk Operations using SqlDataAdapter with SqlCommandBuilder");

                // Open the connection within a using block to ensure disposal.
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    // Create the adapter with a simple SELECT; this populates the DataTable.
                    SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Products", connection);

                    // Enable batch updates of up to 100 rows per round‑trip.
                    adapter.UpdateBatchSize = 100;

                    // Instantiate the SqlCommandBuilder to auto‑generate Insert/Update/Delete commands.
                    SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);

                    // Retrieve the auto‑generated commands (with proper quoting).
                    adapter.InsertCommand = commandBuilder.GetInsertCommand();
                    adapter.UpdateCommand = commandBuilder.GetUpdateCommand();
                    adapter.DeleteCommand = commandBuilder.GetDeleteCommand();

                    // Ensure each command can run for up to 120 seconds.
                    adapter.SelectCommand.CommandTimeout = 120;
                    adapter.InsertCommand.CommandTimeout = 120;
                    adapter.UpdateCommand.CommandTimeout = 120;
                    adapter.DeleteCommand.CommandTimeout = 120;

                    // Fill an in‑memory DataTable with the current product data.
                    DataTable productTable = new DataTable();
                    adapter.Fill(productTable);

                    // Bulk Insert: create 5 new rows with dummy data.
                    for (int i = 0; i < 5; i++)
                    {
                        DataRow newRow = productTable.NewRow();
                        newRow["ProductName"] = "Product " + (i + 1);
                        newRow["Price"] = 1 + (i * 5000);
                        newRow["Stock"] = 10 + (i * 2);
                        productTable.Rows.Add(newRow);
                    }

                    // Push all pending inserts to the database.
                    adapter.Update(productTable);

                    // Clear and refill to pick up newly generated identity keys.
                    productTable.Clear();
                    adapter.Fill(productTable);
                    Console.WriteLine("Bulk Insert completed.");

                    // Bulk Update: update price and stock on every row.
                    foreach (DataRow row in productTable.Rows)
                    {
                        row["Price"] = Convert.ToDecimal(row["Price"]) + 10000;
                        row["Stock"] = Convert.ToInt32(row["Stock"]) + 10;
                    }
                    adapter.Update(productTable);
                    Console.WriteLine("Bulk Update completed.");

                    // Bulk Delete: mark every row for deletion.
                    foreach (DataRow row in productTable.Rows)
                    {
                        row.Delete();
                    }
                    adapter.Update(productTable);
                    Console.WriteLine("Bulk Delete completed.");
                }

                Console.WriteLine("Bulk Operations Completed");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}

Output:

Auto-generated Commands (using SqlCommandBuilder):

• When to Use: Useful for scenarios where simplicity and rapid development are more important than full control.
• Advantages: Requires minimal setup. Automatically creates InsertCommand, UpdateCommand, and DeleteCommand based on the SelectCommand. Suitable for simple tables and standard CRUD operations.
• Limitations: Doesn’t handle complex joins, custom logic, or additional validation steps. The generated SQL may not be optimal for performance or adhere to your database standards.

Using T-SQL Commands to Perform Bulk Insert, Update, and Delete Operations

Now, instead of using SqlCommandBuilder to auto-generate the Insert, Update, and Delete Commands, we use manual T-SQL commands to perform Insert, Update, and Delete operations using SqlDataAdapter. Use this when you need complete control over the SQL. In this case, we need to specify the T-SQL commands using the respective command properties. For a better understanding, please modify the Program class code as follows.

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

namespace BulkOperationsDemo
{
    public class Program
    {
        static void Main()
        {
            // Connection string for ProductsDB.
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=ProductsDB;Trusted_Connection=True;TrustServerCertificate=True;";

            try
            {
                Console.WriteLine("Bulk Operations using SqlDataAdapter and T-SQL Statements");

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    // Adapter configured with SELECT Command and batch size.
                    SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Products", connection)
                    {
                        UpdateBatchSize = 100
                    };

                    // Define the INSERT command text and parameter mappings.
                    adapter.InsertCommand = new SqlCommand(
                        "INSERT INTO Products (ProductName, Price, Stock) VALUES (@ProductName, @Price, @Stock);",
                        connection);
                    adapter.InsertCommand.Parameters.Add("@ProductName", SqlDbType.NVarChar, 100, "ProductName");
                    var priceParam = adapter.InsertCommand.Parameters.Add("@Price", SqlDbType.Decimal, 0, "Price");
                    priceParam.Precision = 10; priceParam.Scale = 2;
                    adapter.InsertCommand.Parameters.Add("@Stock", SqlDbType.Int, 0, "Stock");
                    adapter.InsertCommand.CommandTimeout = 120;
                    // Avoid trying to refresh output parameters.
                    adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

                    // Define the UPDATE command text and parameters.
                    adapter.UpdateCommand = new SqlCommand(
                        "UPDATE Products SET ProductName = @ProductName, Price = @Price, Stock = @Stock " +
                        "WHERE ProductID = @ProductID;", connection);
                    adapter.UpdateCommand.Parameters.Add("@ProductName", SqlDbType.NVarChar, 100, "ProductName");
                    var updPrice = adapter.UpdateCommand.Parameters.Add("@Price", SqlDbType.Decimal, 0, "Price");
                    updPrice.Precision = 10; updPrice.Scale = 2;
                    adapter.UpdateCommand.Parameters.Add("@Stock", SqlDbType.Int, 0, "Stock");
                    adapter.UpdateCommand.Parameters.Add("@ProductID", SqlDbType.Int, 0, "ProductID");
                    adapter.UpdateCommand.CommandTimeout = 120;
                    adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

                    // Define the DELETE command text and parameter.
                    adapter.DeleteCommand = new SqlCommand(
                        "DELETE FROM Products WHERE ProductID = @ProductID;", connection);
                    adapter.DeleteCommand.Parameters.Add("@ProductID", SqlDbType.Int, 0, "ProductID");
                    adapter.DeleteCommand.CommandTimeout = 120;
                    adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

                    // Load existing data into an in‑memory table.
                    DataTable productTable = new DataTable();
                    adapter.Fill(productTable);

                    // Bulk Insert: add five new products.
                    for (int i = 0; i < 5; i++)
                    {
                        DataRow newRow = productTable.NewRow();
                        newRow["ProductName"] = "Product " + (i + 1);
                        newRow["Price"] = 100 + (i * 50);
                        newRow["Stock"] = 10 + (i * 5);
                        productTable.Rows.Add(newRow);
                    }
                    adapter.Update(productTable);

                    // Refresh the table to retrieve identity values.
                    productTable.Clear();
                    adapter.Fill(productTable);
                    Console.WriteLine("Bulk Insert completed.");

                    // Bulk Update: increase price & stock.
                    foreach (DataRow row in productTable.Rows)
                    {
                        row["Price"] = Convert.ToDecimal(row["Price"]) + 1000;
                        row["Stock"] = Convert.ToInt32(row["Stock"]) + 20;
                    }
                    adapter.Update(productTable);
                    Console.WriteLine("Bulk Update completed.");

                    // Bulk Delete: delete all rows.
                    foreach (DataRow row in productTable.Rows)
                    {
                        row.Delete();
                    }
                    adapter.Update(productTable);
                    Console.WriteLine("Bulk Delete completed.");
                }

                Console.WriteLine("Bulk Operations Completed");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}

Output:

Manual T-SQL Commands:

• When to Use: Suitable when you need more control over the SQL being executed, such as adding custom WHERE clauses, implementing conditional logic, or ensuring compliance with database policies.
• Advantages: Full control over SQL syntax and query optimization. Allows advanced tuning and custom logic.
• Limitations: Requires more effort to write and maintain the commands. If the table structure changes, you must manually update the SQL statements.

What is UpdatedRowSource?

UpdatedRowSource is a property of the DbCommand object (like SqlCommand) that tells ADO.NET Core how the results from the command execution are mapped back to the DataRow after DataAdapter.Update() is called.

UpdateRowSource.None: No values returned from the command (e.g., output parameters, return values, or resultsets) will be applied to the corresponding DataRow.

Why You Might Use UpdateRowSource.None

• Skips reading output values like auto-incremented identity values or timestamps. Reduces overhead when you know the command doesn’t return anything useful for the client.
• When your stored procedures or commands don’t return any output or result set, for example, DELETE FROM Customers WHERE CustomerId = @Id — no return value expected.
• When you’re handling return values manually or don’t want ADO.NET to auto-apply any result back to your DataTable.

Using Stored Procedures to Perform Bulk Insert, Update, and Delete Operations

The following uses Stored Procedures instead of T-SQL Statements to perform bulk insert, Update, and delete operations using a SqlDataAdapter. Here, we specify the command text as the stored procedure name and the command type as stored procedure. Use this for enterprise scenarios with existing Stored Procedures, or when you require centralized Database-Side logic, auditing, or security encapsulation. The example code is self-explained, so please read the comment lines for a better understanding.

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

namespace BulkOperationsDemo
{
    public class Program
    {
        static async Task Main()
        {
            // Connection string pointing to ProductsDB.
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=ProductsDB;Trusted_Connection=True;TrustServerCertificate=True;";

            try
            {
                Console.WriteLine("Bulk Operations using SqlDataAdapter with Stored Procedures");

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    // Configure adapter to call the sp_SelectProduct stored procedure.
                    SqlDataAdapter adapter = new SqlDataAdapter("sp_SelectProduct", connection)
                    {
                        UpdateBatchSize = 100
                    };
                    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                    adapter.SelectCommand.CommandTimeout = 120;

                    // Map Insert to sp_InsertProduct.
                    adapter.InsertCommand = new SqlCommand("sp_InsertProduct", connection)
                    {
                        CommandType = CommandType.StoredProcedure,
                        CommandTimeout = 120,
                        UpdatedRowSource = UpdateRowSource.None
                    };
                    adapter.InsertCommand.Parameters.Add("@ProductName", SqlDbType.NVarChar, 100, "ProductName");
                    adapter.InsertCommand.Parameters.Add("@Price", SqlDbType.Decimal, 0, "Price");
                    adapter.InsertCommand.Parameters.Add("@Stock", SqlDbType.Int, 0, "Stock");

                    // Map Update to sp_UpdateProduct.
                    adapter.UpdateCommand = new SqlCommand("sp_UpdateProduct", connection)
                    {
                        CommandType = CommandType.StoredProcedure,
                        CommandTimeout = 120,
                        UpdatedRowSource = UpdateRowSource.None
                    };
                    adapter.UpdateCommand.Parameters.Add("@ProductID", SqlDbType.Int, 0, "ProductID");
                    adapter.UpdateCommand.Parameters.Add("@ProductName", SqlDbType.NVarChar, 100, "ProductName");
                    adapter.UpdateCommand.Parameters.Add("@Price", SqlDbType.Decimal, 0, "Price");
                    adapter.UpdateCommand.Parameters.Add("@Stock", SqlDbType.Int, 0, "Stock");

                    // Map Delete to sp_DeleteProduct.
                    adapter.DeleteCommand = new SqlCommand("sp_DeleteProduct", connection)
                    {
                        CommandType = CommandType.StoredProcedure,
                        CommandTimeout = 120,
                        UpdatedRowSource = UpdateRowSource.None
                    };
                    adapter.DeleteCommand.Parameters.Add("@ProductID", SqlDbType.Int, 0, "ProductID");

                    // Fill the DataTable asynchronously.
                    DataTable productTable = new DataTable();
                    await Task.Run(() => adapter.Fill(productTable));

                    // Bulk Insert via stored proc.
                    for (int i = 0; i < 5; i++)
                    {
                        DataRow newRow = productTable.NewRow();
                        newRow["ProductName"] = "Product " + (i + 1);
                        newRow["Price"] = 100 + (i * 50);
                        newRow["Stock"] = 10 + (i * 5);
                        productTable.Rows.Add(newRow);
                    }
                    adapter.Update(productTable);

                    // Refresh to retrieve IDs.
                    productTable.Clear();
                    adapter.Fill(productTable);
                    Console.WriteLine("Bulk Insert completed.");

                    // Bulk Update via stored proc.
                    foreach (DataRow row in productTable.Rows)
                    {
                        row["Price"] = Convert.ToDecimal(row["Price"]) + 1000;
                        row["Stock"] = Convert.ToInt32(row["Stock"]) + 20;
                    }
                    await Task.Run(() => adapter.Update(productTable));
                    Console.WriteLine("Bulk Update completed.");

                    // Bulk Delete via stored proc.
                    foreach (DataRow row in productTable.Rows)
                    {
                        row.Delete();
                    }
                    await Task.Run(() => adapter.Update(productTable));
                    Console.WriteLine("Bulk Delete completed.");
                }

                Console.WriteLine("Bulk Operations Completed.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}

Output:

Stored Procedures:

• When to Use: Ideal for environments where security, performance, and maintainability are paramount, and also preferred when you want the logic to reside on the database side, thereby reducing the need for complex application-side logic.
• Advantages: Encapsulates logic within the database, making it easier to reuse and update without requiring modifications to the application code. Often more performant for repeated operations and supports advanced features like output parameters, transactions, and error handling.
• Limitations: This approach introduces a dependency on database code.

Important Notes:

• Always clear and refill the DataTable after an Insert to sync with database-generated IDs.
• Use UpdateRowSource = None for performance during batch operations.
• For asynchronous execution, wrap the adapter.Fill() or adapter.Update() inside Task.Run().

Which Approach to use:

For large datasets or frequent bulk operations, stored procedures generally outperform manual or auto-generated commands due to pre-compilation and server-side execution.

• Use the auto‑generated approach for rapid development on simple tables.
• Choose manual T‑SQL when you need more control over the SQL.
• Opt for stored procedures in enterprise settings where DB‑side logic, security, or auditing must be centralized.

In ADO.NET Core, the SqlDataAdapter acts as a bridge between an in‑memory DataTable (or DataSet) and a SQL Server database. By configuring its SelectCommand, InsertCommand, UpdateCommand and DeleteCommand, and then calling adapter.Update(dataTable) allows us to efficiently push batched changes, new rows, modified rows, and deleted rows from our application to the database without writing manual T-SQL loops.

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