Chennai OMR Directory | Merging DataTables into SQL Server Table with Insert and Update Operations in C#

Merging DataTables into SQL Server Table with Insert and Update Operations in C#

by admin

In many enterprise applications, data integration and synchronization between in-memory data structures (such as DataTables) and relational databases are crucial. A common scenario is when data is retrieved into a DataTable, and you need to persist this data into a SQL Server table. The challenge becomes handling both inserts and updates efficiently, ensuring data integrity while avoiding conflicts or duplicate entries.

To implement the MERGE operation efficiently with a stored procedure in SQL Server, we will create a stored procedure that accepts a DataTable as a parameter and performs the insert and update operations based on whether the record exists in the database or not. This method reduces the amount of C# code needed and offloads the merging logic to the database, improving performance.

Here’s how you can do it:

Step 1: Create the Stored Procedure in SQL Server

The stored procedure will accept a table-valued parameter (TVP), which is a special type that allows passing a set of rows (similar to a DataTable in C#) to the SQL Server procedure.

Create the TVP (Table-Valued Type)

First, define a table-valued type to match the structure of the data you want to merge.

-- Step 1: Create a table-valued type  CREATE TYPE dbo.ProductType AS TABLE  (      ProductID INT,      ProductName NVARCHAR(100),      Price DECIMAL(18, 2)  );

Create the Stored Procedure

Now, create a stored procedure that will use the ProductType TVP to perform the merge.

-- Step 2: Create the stored procedure  CREATE PROCEDURE dbo.MergeProducts  (      @Products dbo.ProductType READONLY  )  AS  BEGIN      MERGE INTO Products AS target      USING @Products AS source      ON target.ProductID = source.ProductID        -- If the product exists, update it      WHEN MATCHED THEN          UPDATE SET               target.ProductName = source.ProductName,               target.Price = source.Price        -- If the product doesn't exist, insert it      WHEN NOT MATCHED BY TARGET THEN          INSERT (ProductID, ProductName, Price)          VALUES (source.ProductID, source.ProductName, source.Price);        -- Optionally, you can add additional logic for error handling or auditing  END;

This stored procedure performs the following operations:

  • If a product exists (matched by ProductID), it updates the existing record.
  • If a product does not exist (not matched by ProductID), it inserts the new record.

Step 2: Calling the Stored Procedure from C#

Now, you need to invoke this stored procedure from C# code, passing the DataTable as a parameter. C# provides SqlParameter objects that can be used to send a DataTable to SQL Server.

Here’s the C# code to call the stored procedure:

using System;  using System.Data;  using System.Data.SqlClient;    class Program  {      static string connectionString = "Server=your_server;Database=your_db;User Id=your_user;Password=your_password;";            static void Main()      {          // Create a sample DataTable          DataTable dt = CreateSampleDataTable();            // Merge the DataTable into SQL Server using the stored procedure          MergeDataTableWithSQL(dt);      }            static DataTable CreateSampleDataTable()      {          DataTable dt = new DataTable();          dt.Columns.Add("ProductID", typeof(int));          dt.Columns.Add("ProductName", typeof(string));          dt.Columns.Add("Price", typeof(decimal));            // Adding sample data for demonstration          dt.Rows.Add(1, "Product A", 10.99m);          dt.Rows.Add(2, "Product B", 20.49m);          dt.Rows.Add(3, "Product C", 15.99m);            return dt;      }        static void MergeDataTableWithSQL(DataTable dt)      {          using (SqlConnection conn = new SqlConnection(connectionString))          {              conn.Open();                // Create a parameter for the table-valued type              SqlParameter tvpParam = new SqlParameter("@Products", SqlDbType.Structured)              {                  Value = dt,                  TypeName = "dbo.ProductType" // The TVP type defined in SQL Server              };                // Create the command to call the stored procedure              using (SqlCommand cmd = new SqlCommand("dbo.MergeProducts", conn))              {                  cmd.CommandType = CommandType.StoredProcedure;                  cmd.Parameters.Add(tvpParam);                    // Execute the stored procedure                  cmd.ExecuteNonQuery();              }          }            Console.WriteLine("Data has been successfully merged into SQL Server.");      }  }

Explanation of the C# Code:

  1. Create a DataTable: The CreateSampleDataTable method creates a DataTable with sample data that will be sent to SQL Server.
  2. Passing the DataTable to the Stored Procedure:
    • The SqlParameter with SqlDbType.Structured is used to pass the DataTable as a table-valued parameter (TVP).
    • The TypeName property of the parameter must match the name of the TVP created in SQL Server (dbo.ProductType).
  3. Calling the Stored Procedure:
    • The stored procedure dbo.MergeProducts is executed via SqlCommand.
    • The DataTable is passed as the TVP parameter, and the MERGE logic inside the stored procedure handles the insert and update operations.
  4. Execute the Procedure: The ExecuteNonQuery method is used because we are performing a DML (Data Manipulation Language) operation, and we don’t expect a result set.

Step 3: Testing the Solution

  1. Run the SQL script to create the TVP and stored procedure in your database.
  2. Run the C# application. It will create a sample DataTable and send it to SQL Server for merging with the Products table.
  3. Check the results in SQL Server:
    • If any products in the DataTable already exist in the Products table, their details will be updated.
    • If any products in the DataTable do not exist in the Products table, they will be inserted.

Benefits of Using a Stored Procedure for Merging

  1. Performance:
    • Offloading the merge logic to the database reduces network round-trips and allows SQL Server’s optimizations to handle large datasets more efficiently.
    • SQL Server’s MERGE statement is highly optimized for handling both insert and update operations in one go.
  2. Maintainability:
    • The merge logic is encapsulated in a stored procedure, making it easier to maintain, test, and modify without having to change the application code.
  3. Scalability:
    • Using table-valued parameters (TVPs) allows you to pass a large set of data to SQL Server in one operation, improving the scalability of the application when working with large datasets.

By using a stored procedure with a table-valued parameter (TVP), you can efficiently merge data from a DataTable into an existing SQL Server table, handling both insert and update operations in one go. This approach leverages SQL Server’s MERGE statement to optimize data integration, while keeping your application code clean and maintainable.