Users Online

· Guests Online: 146

· Members Online: 0

· Total Members: 188
· Newest Member: meenachowdary055

Forum Threads

Newest Threads
No Threads created
Hottest Threads
No Threads created

Latest Articles

· Do while loop
· The for loop in C
· C while loop
· Pointers in C
· Functions in C

Articles Hierarchy

Articles Home » DOTNET » Bulk INSERT and UPDATE in C# and ADO.NET using Stored Procedure

Bulk INSERT and UPDATE in C# and ADO.NET using Stored Procedure

Bulk INSERT and UPDATE in C# and ADO.NET using Stored Procedure

In this article, I am going to discuss How to Perform BULK INSERT and UPDATE in C# and ADO.NET using SQL Server Stored Procedure with Examples. Please read our previous article where we discussed ADO.NET Architecture. Here, in this article, I will explain to you how to perform the Bulk Insert and Update using the Stored Procedure with Examples, and in the next article, I am going to discuss how to Perform Batch Operations Using SqlBulkCopy and DataAdapters.

 

Bulk Insert and Update using Stored Procedure and C# ADO.NET:

Let us understand how to perform Bulk Insert and Update using C# and ADO.NET. So, basically what we will do is, we will check if the record does not exist in the database, then we will insert that record and if the record exists in the database, then we will update that record and we are going to perform this as bulk. That means we are having a data table and that data table contains many records (new records as well as existing records). The new records need to be inserted into the database while the existing records need to be updated in the database.

 

Here, we will create a stored procedure that will accept Table Type Parameter. The SQL Server Table Valued Parameter allows us to pass multiple records using a DataTable to the Stored Procedure and then within the stored procedure using the Table Type Parameter we are going to perform the Bulk Insert and Update Operations. Let us understand this step by step.

  

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

We are going to use the following Employee table.

Bulk Insert Update in C# and ADO.NET using Stored Procedure

 

Please use the below SQL Script to Create the EmployeeDB database and Employee table and Populate the Employee table with the required sample data.

CREATE DATABASE EmployeeDB;
 GO 
 
 USE EmployeeDB;
 GO 
 
 CREATE TABLE Employee(
Id INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(50),
Mobile VARCHAR(50),
)
 GO 
 
 INSERT INTO Employee VALUES (100, 'Anurag','Anurag@dotnettutorial.net','1234567890')
 INSERT INTO Employee VALUES (101, 'Priyanka','Priyanka@dotnettutorial.net','2233445566')
 INSERT INTO Employee VALUES (102, 'Preety','Preety@dotnettutorial.net','6655443322')
 INSERT INTO Employee VALUES (103, 'Sambit','Sambit@dotnettutorial.net','9876543210')
 GO

As we are going to perform the BULK Insert and Update operation using a stored procedure, so we need a Table Type Parameter.

  

What is the Table Type Parameter?

A Table-Type Parameter is a database object in SQL Server and it provides an easy way to pass multiple rows of data from a client application to an SQL Server Database without multiple round trips. That means we can use a Table-Type Parameter to encapsulate rows of data in a client application and send we can send those rows of data to the SQL Server Database in a single request. Once the SQL Server received the data, then the SQL Server process the data.

 

Let us create a Table Type Parameter within the SQL Server Employee database to handle multiple rows of Employee data. Please use the below SQL Script to create the EmployeeType Table Type Parameter.

 

CREATE TYPE EmployeeType AS TABLE(
Id INT NULL,
Name VARCHAR(100) NULL,
Email VARCHAR(50) NULL,
Mobile VARCHAR(50) NULL 
)
 GO

Once you created the User Defined Table Type Parameter, then it will be visible in the Object Explorer as shown in the below image.

How to Perform BULK INSERT and UPDATE in C# and ADO.NET using SQL Server Stored Procedure with Examples

 

Now, we need to create a stored procedure that will accept our user-defined table type as a parameter and using that user-defined table type parameter we are going to perform the Bulk Insert and Update operations. If the record exists, it is going to update the record based on the Id and if the record not exists then it is going to insert the record into the database.

Again, we can implement the logic in two ways. If you are using SQL Server 2008 or a Higher version, then you can use the MERGE statement to implement the logic and if you are using SQL Server 2005 or any lower version, then you cannot use the MERGE statement. MERGE statement is going to give you better performance. Let us see how we can implement the logic using MERGE and without using the MERGE function.

  

Stored Procedure using MERGE statement to perform Bulk Insert and Update Operations:

SQL Server 2008 came up with a new function called MERGE, which allows performing INSERT operation when records are not present and UPDATE operation when records are present in the table. In the below Stored Procedure, we are using the MERGE function to perform the BULK INSERT and UPDATE operations.

 

CREATE PROCEDURE SP_Bulk_Insert_Update_Employees
@Employees EmployeeType READONLY 
 AS 
 BEGIN 
SET NOCOUNT ON;
MERGE INTO Employee E1
USING @Employees E2
ON E1.Id=E2.Id
WHEN MATCHED THEN 
UPDATE SET
E1.Name = E2.Name,
E1.Email = E2.Email,
E1.Mobile = E2.Mobile
WHEN NOT MATCHED THEN 
INSERT VALUES(E2.Id, E2.Name, E2.Email, E2.Mobile);
 END

Note: When you use SET NOCOUNT ON, the message that indicates the number of rows that are affected by the T-SQL statement is not returned as part of the results. When you use SET NOCOUNT OFF; the count is returned. Using SET NOCOUNT ON can improve performance because network traffic can be reduced.

  

Stored Procedure without using MERGE statement to perform Bulk Insert and Update Operations:

If you are stuck with SQL Server 2005 or any lower version, then you cannot use the MERGE function. In the below-stored procedure, it first performs the UPDATE operations using INNER JOIN and then performs the INSERT operation for those records which are not present inside the table.

  

CREATE PROCEDURE SP_Bulk_Insert_Update_Employees_Without_MERGE
@Employees EmployeeType READONLY 
AS
BEGIN
SET NOCOUNT ON;
--UPDATE EXISTING RECORDS
UPDATE Employee
SET
Name = E2.Name,
Email = E2.Email,
Mobile = E2.Mobile
FROM Employee E1
INNER JOIN @Employees E2
ON E1.Id=E2.Id
--INSERT NON-EXISTING RECORDS
INSERT INTO Employee
SELECT Id, Name, Email, Mobile
FROM @Employees
WHERE Id NOT IN(SELECT Id FROM Employee)
END

So, our database part is over. We have created our database, and tables, and populated the table with some sample data. We have also created the user-defined Table Type and user-defined stored procedure where we have implemented the logic to perform the BULK INSERT and UPDATE Operations. Now, let us proceed and try to understand how we can call that stored procedure from our C# application to perform the Bulk INSERT and UPDATE operation.

Example to understand how to Perform Bulk Insert and Update using C# ADO.NET:

Let us understand how to call the stored procedure using the data table as a parameter. As the stored procedure excepts one Table Type Parameter, so from our C# code, we can create a data table and we can 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 System;
using System.Data;
using System.Data.SqlClient;
namespace BulkInsertUpdateUsingADO
{
class Program
{
static void Main(string[] args)
{
try
{
//Storing the connection string in a variable
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
//Creating Data Table
DataTable EmployeeDataTable = new DataTable("Employees");
//Add Columns to the Data Table as per the columns defined in the Table Type Parameter
DataColumn Id = new DataColumn("Id");
EmployeeDataTable.Columns.Add(Id);
DataColumn Name = new DataColumn("Name");
EmployeeDataTable.Columns.Add(Name);
DataColumn Email = new DataColumn("Email");
EmployeeDataTable.Columns.Add(Email);
DataColumn Mobile = new DataColumn("Mobile");
EmployeeDataTable.Columns.Add(Mobile);
//Adding Multiple Rows into the DataTable
//Follwoing Rows are going to be updated
EmployeeDataTable.Rows.Add(101, "ABC", "ABC@dotnettutorials.net", "12345");
EmployeeDataTable.Rows.Add(102, "PQR", "PQR@dotnettutorials.net", "11223");
EmployeeDataTable.Rows.Add(103, "XYZ", "XYZ@dotnettutorials.net", "23432");
//Following Rows are going to be Inserted
EmployeeDataTable.Rows.Add(106, "A", "A@dotnettutorials.net", "12345");
EmployeeDataTable.Rows.Add(107, "B", "B@dotnettutorials.net", "23456");
EmployeeDataTable.Rows.Add(108, "C", "C@dotnettutorials.net", "34567");
EmployeeDataTable.Rows.Add(109, "D", "D@dotnettutorials.net", "45678");
EmployeeDataTable.Rows.Add(110, "E", "E@dotnettutorials.net", "56789");
EmployeeDataTable.Rows.Add(111, "F", "F@dotnettutorials.net", "67890");
//Creating the connection object
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
//You can pass any stored procedure
//As I am using Higher version of SQL Server, so, I am using the Stored Procedure which uses MERGE Function
using (SqlCommand cmd = new SqlCommand("SP_Bulk_Insert_Update_Employees", connection))
{
//Set the command type as StoredProcedure
cmd.CommandType = CommandType.StoredProcedure;
//Add the input parameter required by the stored procedure
cmd.Parameters.AddWithValue("@Employees", EmployeeDataTable);
//Open the connection
connection.Open();
//Execute the command
cmd.ExecuteNonQuery();
}
}
Console.WriteLine("BULK INSERT UPDATE Successful");
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}

Once you execute the above code, then check the Employee table in the database and now the Employee should have the updated as well as employee data as shown in the below image.

Example to understand how to Perform Bulk Insert and Update using C# ADO.NET

Note: In the above example, we have hard-coded the data table data, in our upcoming article, I will show you how to fill the data table data from an Excel file.

In the next article, I am going to discuss How to Perform Bulk INSERT using SqlBulkCopy Object in C# ADO.NET with Examples. Here, in this article, I try to explain how to perform Bulk INSERT and UPDATE using SQL Server Stored Procedure and DataTables in C#. I hope you enjoy this Bulk Insert Update in C# and ADO.NET using Stored Procedure with Examples article.

· Posted by Superadmin on November 15 2023 · 0 Comments · 59 Reads · Print

Comments

No Comments have been Posted.

Post Comment

Please Login to Post a Comment.

Ratings

Rating is available to Members only.

Please login or register to vote.
No Ratings have been Posted.
Render time: 1.05 seconds
10,811,843 unique visits