How to bulk update sql server

Question

I using .net core mvc create a web then I need to update mutiple row at one time by store procedure . how should i do, plese

Anupam Maiti · Answer

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.

Manikandan Murugesan · Answer

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

Gurpreet Arora · Answer

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.