Bulk Insert and Update using Stored Procedure in ADO.NET Core

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

Bulk Insert and Update using Stored Procedure in ADO.NET Core

In this article, I will discuss How to Implement Bulk Inserts and Updates using Stored Procedures in ADO.NET Core with Examples. Please read our previous article, which discusses implementing bulk insert, update, and delete operations using ADO.NET Core with examples.

Bulk Insert and Update using Stored Procedure in ADO.NET Core

Bulk insert and update operations are common tasks in database management, especially when dealing with large datasets. Bulk insert and update operations in a database can significantly improve the performance of your application by minimizing the number of round-trips to the database.

ADO.NET Core provides efficient ways to handle such operations, often through the use of stored procedures. A stored procedure is a precompiled collection of SQL statements stored under a name and processed as a unit.

Using Stored Procedures, we can perform bulk insert and update operations. This approach is useful when dealing with a large volume of data that needs to be inserted or updated in a database.

Using Stored Procedures for Bulk Insert and Update

To perform bulk insert and update operations using stored procedures in ADO.NET Core, you typically follow these steps:

• Create the Stored Procedure: The stored procedure should be designed to handle bulk insert or update operations. This can be achieved through various SQL techniques, such as using the MERGE statement in SQL Server to perform both insert and update operations based on a source and target table comparison.
• Connect to the Database: Use ADO.NET Core to establish a connection to your database. This involves using the SqlConnection class if you are connecting to SQL Server.
• Execute the Stored Procedure: Use the SqlCommand class to execute the stored procedure. For bulk operations, you might pass a table-valued parameter to the stored procedure, which allows you to send multiple rows of data as a single parameter.

Database, Table, Table Type Parameter, and Stored Procedure:

Here, we will create the EmployeeDB database, Employee table, Table Type Parameter, and a stored procedure that will accept Table Type Parameter. The SQL Server Table Type Parameter allows you to pass multiple records using a DataTable to the Stored Procedure. Then, using the Table Type Parameter, we will perform the Bulk Insert and Update Operations within the stored procedure.

Create a Dummy Database and Table:

We’ll create an SQL Server database named EmployeeDB and a table named Employee and insert some dummy data. So, please execute the below SQL Script to create the same:

CREATE DATABASE EmployeeDB
GO

USE EmployeeDB
GO

CREATE TABLE Employee (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100),
    Department NVARCHAR(100),
    Salary DECIMAL(18, 2),
    JoinDate DATETIME
)
GO

INSERT INTO Employee (Name, Department, Salary, JoinDate)
VALUES 
('Pranaya Rout', 'IT', 70000, '2020-01-08'),
('Priyanka Dewangan', 'HR', 65000, '2020-02-15'),
('Hina Sharma', 'Finance', 75000, '2021-03-01'),
('Anurag Mohanty', 'Marketing', 72000, '2021-04-12'),
('Preety Tiwari', 'IT', 68000, '2022-05-25');
GO

The Employee table is designed to hold information about employees. Here is a breakdown of its structure:

• EmployeeID: The EmployeeID is the identity column. This means SQL Server automatically generates a unique EmployeeID for each new record, starting at 1 and incrementing by 1 for each subsequent record. The identity property simplifies the process of adding new employees because you don’t need to assign unique identifiers manually.
• Name: A string (NVARCHAR) that stores the employee’s name.
• Department: A string (NVARCHAR) indicates the department where the employee works.
• Salary: A decimal value representing the employee’s salary.
• JoinDate: A datetime value indicating when the employee joined the company.

Create Table Type:

This will be used as a parameter for the stored procedure to allow bulk operations. So, please execute the below script to create the user-defined table type.

CREATE TYPE EmployeeType AS TABLE (
    EmployeeID INT NULL,
    Name NVARCHAR(100),
    Department NVARCHAR(100),
    Salary DECIMAL(18, 2),
    JoinDate DATETIME
)
GO

The EmployeeType is a user-defined table type that mirrors the structure of the Employee table. It is used as a parameter type for the stored procedure, allowing for bulk operations. This means you can pass a collection of employee records to the stored procedure in a single parameter, facilitating bulk insert or update actions.

• Columns: EmployeeID, Name, Department, Salary, JoinDate
• This table type does not store data permanently; it’s used to pass data through parameters in stored procedures or functions.
• Here, the EmployeeID can be NULL. New employee records won’t have an EmployeeID when performing bulk inserts, as the database generates it. For updates, the EmployeeID will be specified to identify the record that needs to be updated.

Stored Procedure for Bulk Insert and Update:

We will write a stored procedure that accepts the custom table type as a parameter and performs insert or update operations based on the data provided. So, please execute the below script to create the user-defined stored procedure. This stored procedure will accept a parameter of EmployeeType and perform insert or update operations accordingly.

CREATE PROCEDURE spBulkInsertUpdateEmployee
    @EmployeeData EmployeeType READONLY
AS
BEGIN
    SET NOCOUNT ON;

    -- Temporarily hold new employees without EmployeeID
    DECLARE @NewEmployees EmployeeType
    INSERT INTO @NewEmployees (Name, Department, Salary, JoinDate)
    SELECT Name, Department, Salary, JoinDate FROM @EmployeeData WHERE EmployeeID IS NULL

    -- Insert new records
    INSERT INTO Employee (Name, Department, Salary, JoinDate)
    SELECT Name, Department, Salary, JoinDate FROM @NewEmployees

    -- Update existing records
    UPDATE E
    SET E.Name = ED.Name,
        E.Department = ED.Department,
        E.Salary = ED.Salary,
        E.JoinDate = ED.JoinDate
    FROM Employee E
    INNER JOIN @EmployeeData ED ON E.EmployeeID = ED.EmployeeID
    WHERE ED.EmployeeID IS NOT NULL
END
GO

The spBulkInsertUpdateEmployee stored procedure is designed to perform bulk insert and update operations on the Employee table using the data passed as an EmployeeType table type parameter.

• Parameter Declaration: The procedure accepts one parameter, @EmployeeData, which is of the EmployeeType table type and is read-only.
• Update Existing Records: Updates Existing Records: For records in @EmployeeData with a non-null EmployeeID, the procedure updates the corresponding records in the Employee table based on this identifier.
• Insert New Records: For records in @EmployeeData with EmployeeID as NULL, indicating they are new employees, it inserts them into the Employee table. SQL Server automatically assigns a unique EmployeeID to each new record.

This stored procedure efficiently combines update and insert operations into a single batch process, significantly improving performance when dealing large data sets.

Console Application Using ADO.NET Core:

A simple console application to call the stored procedure and pass a data table as a parameter for bulk operations. When integrating with ADO.NET Core:

• For new records, you’ll populate the DataTable without setting EmployeeID, allowing SQL Server to generate it upon insertion.
• For updates, include the EmployeeID of the records you intend to update to ensure the correct records are modified.

Let us understand how to call the stored procedure using the data table as a parameter. As the stored procedure accepts one Table Type Parameter from our C# code, we can create a data table, and we can set that data table as an input parameter to the stored procedure. The following example code is self-explained, so please go through the comment lines.

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
            try
            {
                // Initialize a DataTable instance with the structure matching the EmployeeType table type
                DataTable employeeTable = new DataTable();
                employeeTable.Columns.Add("EmployeeID", typeof(int)); // For updates. Leave null for inserts.
                employeeTable.Columns.Add("Name", typeof(string));
                employeeTable.Columns.Add("Department", typeof(string));
                employeeTable.Columns.Add("Salary", typeof(decimal));
                employeeTable.Columns.Add("JoinDate", typeof(DateTime));

                // Example data for new employee (Insert)
                employeeTable.Rows.Add(DBNull.Value, "New Employee1", "IT", 60000, DateTime.Now);
                employeeTable.Rows.Add(DBNull.Value, "New Employee2", "HR", 70000, DateTime.Now);
                employeeTable.Rows.Add(DBNull.Value, "New Employee3", "IT", 80000, DateTime.Now);

                // Example data for updating existing employees 
                employeeTable.Rows.Add(1, "Updated Name", "HR", 65000, DateTime.Now);
                employeeTable.Rows.Add(2, "Updated Name", "IT", 85000, DateTime.Now);
                employeeTable.Rows.Add(3, "Updated Name", "HR", 95000, DateTime.Now);

                using (var connection = new SqlConnection(connectionString))
                {
                    var command = new SqlCommand("spBulkInsertUpdateEmployee", connection)
                    {
                        //Set the command type as Stored Procedure
                        CommandType = CommandType.StoredProcedure
                    };

                    //Set the Parameter as the Data table
                    var parameter = command.Parameters.AddWithValue("@EmployeeData", employeeTable);
                    parameter.TypeName = "dbo.EmployeeType"; // The qualified name of the table type

                    await connection.OpenAsync();
                    await command.ExecuteNonQueryAsync();
                    Console.WriteLine("Operation Completed Successfully.");
                    await connection.CloseAsync();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
            }
        }
    }
}

Now, run the application, which should perform the expected database operation. Now, if you verify the database, then you should see the updated and new data as expected, as shown in the below image:

Stored Procedure with MERGE Statement

Let us rewrite the spBulkInsertUpdateEmployee Stored Procedure with the MERGE Statement. The MERGE statement is powerful for performing insert, update, or delete operations in a single statement based on certain conditions. This approach can simplify the logic for handling inserts and updates based on whether an employee record already exists. Here’s how you can modify the stored procedure to use the MERGE statement:

CREATE OR ALTER PROCEDURE spBulkInsertUpdateEmployee
    @EmployeeData EmployeeType READONLY
AS
BEGIN
    SET NOCOUNT ON;

    MERGE INTO Employee AS target
    USING (SELECT * FROM @EmployeeData) AS source (EmployeeID, Name, Department, Salary, JoinDate)
    ON target.EmployeeID = source.EmployeeID
    WHEN MATCHED THEN
        UPDATE SET target.Name = source.Name,
                   target.Department = source.Department,
                   target.Salary = source.Salary,
                   target.JoinDate = source.JoinDate
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (Name, Department, Salary, JoinDate)
        VALUES (source.Name, source.Department, source.Salary, source.JoinDate);
END;
GO

Explanation

• MERGE INTO: Specifies the target table (Employee) that the merge operation will affect.
• USING: Defines the source of data for the merge. In this case, a select statement retrieves all rows from the @EmployeeData table-valued parameter.
• ON: The condition that matches rows in the target table with rows in the source. This example uses EmployeeID to identify matches.
• WHEN MATCHED THEN: Specifies the action for rows matching the ON condition. Here, it updates the matching rows in the target table with the corresponding values from the source.
• WHEN NOT MATCHED BY TARGET THEN: Specifies the action to take for rows in the source that do not have matching rows in the target. It inserts new rows into the target table with the values from the source.

We do not need to make any changes in the C# code. With this stored procedure in place, our application will work as expected. In this example, we have created the EmployeeId as the Identity column. Now, let us proceed and see how to perform the BULK INSERT and UPDATE operations in a table without an Identity Column.

BULK Operation without Identity Column using Stored Procedure with ADO.NET Core:

First, we create the database, table with some dummy data, table type parameter, and stored procedure to perform BULK Operation using MERGE Statement.

Create the Database and Table

First, let’s create the EmployeeDB database and an Employee table without an identity column.

CREATE DATABASE EmployeeDB;
GO

USE EmployeeDB;
GO

CREATE TABLE Employee (
    EmployeeID INT NOT NULL,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    PRIMARY KEY (EmployeeID)
);

The Employee table is designed to store employee data. It has four columns:

• EmployeeID: A unique identifier for each employee. This is the table’s primary key, meaning each value must be unique and not null.
• FirstName: The first name of the employee.
• LastName: The last name of the employee.
• Department: The department in which the employee works.

Insert Dummy Data into the Table

Here’s how to insert some dummy data into the Employee table.

INSERT INTO Employee (EmployeeID, FirstName, LastName, Department) VALUES
(1, 'John', 'Doe', 'HR'),
(2, 'Jane', 'Doe', 'IT'),
(3, 'Jim', 'Beam', 'Finance');

Create a Table Type

Passing data as a table-type parameter to the stored procedure is efficient for bulk operations.

CREATE TYPE EmployeeType AS TABLE (
    EmployeeID INT NOT NULL,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

The EmployeeType table type is a user-defined table type (UDTT) that mirrors the structure of the Employee table. It’s used to pass tabular data to stored procedures and functions. This is particularly useful for bulk operations, allowing you to pass a collection of rows to a stored procedure in a single parameter.

When using this Table Type as a parameter in a stored procedure, you can pass a data table to the procedure, simultaneously enabling operations on multiple rows.

Create the Stored Procedure with the MERGE Statement

This stored procedure will perform bulk insert or update operations based on the EmployeeID.

CREATE PROCEDURE spMergeEmployee
    @EmployeeData EmployeeType READONLY
AS
BEGIN
    MERGE INTO Employee AS Target
    USING @EmployeeData AS Source ON Target.EmployeeID = Source.EmployeeID
    WHEN MATCHED THEN
        UPDATE SET Target.FirstName = Source.FirstName,
                   Target.LastName = Source.LastName,
                   Target.Department = Source.Department
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (EmployeeID, FirstName, LastName, Department)
        VALUES (Source.EmployeeID, Source.FirstName, Source.LastName, Source.Department);
END;

The spMergeEmployee stored procedure is designed to perform bulk insert and update operations on the Employee table. It uses the MERGE SQL statement, which combines insert, update, and delete operations into a single statement based on a set of conditions. Here’s a breakdown of its functionality:

• @EmployeeData: This parameter is EmployeeType, allowing you to pass an employee data table to the procedure.
• MERGE INTO Employee AS Target: This specifies the Employee table as the target for the merge operation.
• USING @EmployeeData AS Source: This uses the passed-in table as the source for the merge. Each row in @EmployeeData will be compared to the rows in Employee.
• ON Target.EmployeeID = Source.EmployeeID: This condition matches rows in the target table with rows in the source table based on EmployeeID.
• WHEN MATCHED THEN UPDATE: If an EmployeeID in the source table matches one in the target table, the corresponding row in the target table is updated with the values from the source table.
• WHEN NOT MATCHED BY TARGET THEN INSERT: If an EmployeeID in the source table does not match any EmployeeID in the target table, a new row is inserted into the target table with the values from the source table.

Create a .NET Core Console Application.

Now, let’s create a .NET Core Console Application to call this stored procedure. Replace the Program.cs file content with the following code. This code assumes you have a connection string to your EmployeeDB database.

using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    using (var command = new SqlCommand("spMergeEmployee", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;

                        // Create a DataTable with the same structure as your EmployeeType
                        var table = new DataTable();
                        table.Columns.Add("EmployeeID", typeof(int));
                        table.Columns.Add("FirstName", typeof(string));
                        table.Columns.Add("LastName", typeof(string));
                        table.Columns.Add("Department", typeof(string));

                        // Add data to your table
                        table.Rows.Add(4, "Ram", "Sham", "HR"); 
                        table.Rows.Add(5, "Pam", "Jam", "IT");
                        table.Rows.Add(6, "Tom", "Hery", "HR");

                        //Update data
                        table.Rows.Add(1, "Pranaya", "Rout", "IT"); 
                        table.Rows.Add(2, "Hina", "Sharma", "HR");

                        var parameter = command.Parameters.AddWithValue("@EmployeeData", table);
                        parameter.TypeName = "EmployeeType";

                        command.ExecuteNonQuery();
                    }
                }
                Console.WriteLine("Operation Completed.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
            }
        }
    }
}

Now, run the application, which should perform the expected database operation. Now, if you verify the database, then you should see the updated and new data as expected, as shown in the below image:

In the next article, I will discuss How to Implement Pagination using ADO.NET Core and Stored Procedure with Examples. In this article, I explain How to Implement Bulk Inserts and Updates using Stored Procedures using ADO.NET Core with Examples. I would like to have your feedback. Please post feedback, questions, or comments about this Bulk Insert and Update using Stored Procedure using the ADO.NET Core with Examples article.