How to bulk update sql server

Using a Table-Valued Parameter to perform bulk updates in SQL Server can be highly efficient. I implemented a similar approach in one of my projects.

Let's see in details:

1- Create Table Type in Db.

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

 

2- Create SP in Db.

CREATE PROCEDURE UpdateEmployees  
    @EmployeeTable EmployeeType READONLY  
AS  
BEGIN  
    UPDATE e  
    SET   
        e.Name = et.Name,  
        e.Department = et.Department,  
        e.Salary = et.Salary,  
        e.JoinDate = et.JoinDate  
    FROM Employees e  
    INNER JOIN @EmployeeTable et ON e.EmployeeID = et.EmployeeID  
END  

 

3- Create entity and DbContect 

public class Employee  
{  
    public int EmployeeID { get; set; }  
    public string Name { get; set; }  
    public string Department { get; set; }  
    public decimal Salary { get; set; }  
    public DateTime JoinDate { get; set; }  
}  
  
public class AppDbContext : DbContext  
{  
    public DbSet Employees { get; set; }  
  
    public AppDbContext(DbContextOptions options)  
        : base(options)  
    {  
    }  
  
}  

 

4 - Add method to call the SP

public class AppDbContext : DbContext  
{  
    public AppDbContext(DbContextOptions options)  
        : base(options)  
    {  
    }  
  
    public DbSet Employees { get; set; }  
  
    public async Task BulkUpdateEmployeesAsync(List employees)  
    {  
        var employeeTable = new DataTable();  
        employeeTable.Columns.Add("EmployeeID", typeof(int));  
        employeeTable.Columns.Add("Name", typeof(string));  
        employeeTable.Columns.Add("Department", typeof(string));  
        employeeTable.Columns.Add("Salary", typeof(decimal));  
        employeeTable.Columns.Add("JoinDate", typeof(DateTime));  
  
        foreach (var employee in employees)  
        {  
            employeeTable.Rows.Add(employee.EmployeeID, employee.Name, employee.Department, employee.Salary, employee.JoinDate);  
        }  
  
        var employeeTableParam = new SqlParameter("@EmployeeTable", employeeTable)  
        {  
            TypeName = "EmployeeType",  
            SqlDbType = SqlDbType.Structured  
        };  
  
        await Database.ExecuteSqlRawAsync("EXEC UpdateEmployees @EmployeeTable", employeeTableParam);  
    }  
}  

I will create an article and add detailed steps. Once done I will share here.

Kindly refer to the following URL: 

https://dotnettutorials.net/lesson/bulk-insert-and-update-using-stored-procedure-in-ado-net-core/

https://stackoverflow.com/questions/20635796/bulk-update-in-c-sharp

Example Stored Procedure for Updating Multiple Rows:

Suppose you want to update the locations with specific LID values.

1. Using Parameters for Each Row

CREATE PROCEDURE UpdateLocations
    @LID1 INT,
    @NewLocation1 VARCHAR(100),
    @LID2 INT,
    @NewLocation2 VARCHAR(100)
AS
BEGIN
    -- Update the first row
    UPDATE Location
    SET Location = @NewLocation1
    WHERE LID = @LID1;

    -- Update the second row
    UPDATE Location
    SET Location = @NewLocation2
    WHERE LID = @LID2;

    -- You can repeat this pattern for more rows or use a loop for dynamic data.
END;

Executing the Procedure:

You would call this stored procedure by passing values for each parameter:

EXEC UpdateLocations 1, 'Chittagong', 2, 'Barisal';
EXEC UpdateLocations 1, 'Chittagong', 2, 'Barisal';

This will update two rows at once, changing the location for LID 1 and 2.

2. Using a Temporary Table or Table-Valued Parameter

If you have many rows to update, passing a table as a parameter is more efficient. Here's how you can use a table-valued parameter to update multiple rows at once:

Step 1: Create a User-Defined Table Type

CREATE TYPE LocationUpdateTable AS TABLE
(
    LID INT,
    NewLocation VARCHAR(100)
);

Step 2: Create the Stored Procedure

CREATE PROCEDURE UpdateMultipleLocations
    @LocationUpdates LocationUpdateTable READONLY
AS
BEGIN
    UPDATE L
    SET L.Location = U.NewLocation
    FROM Location L
    JOIN @LocationUpdates U ON L.LID = U.LID;
END;

Step 3: Execute the Stored Procedure

You can pass a table variable to the stored procedure:

DECLARE @Updates LocationUpdateTable;

-- Insert values for updates
INSERT INTO @Updates (LID, NewLocation) VALUES (1, 'Chittagong');
INSERT INTO @Updates (LID, NewLocation) VALUES (2, 'Barisal');

-- Execute the stored procedure
EXEC UpdateMultipleLocations @Updates;

This approach is better for batch updates when you have multiple rows to update, and it allows you to scale more easily without hardcoding parameters.