Group INSERT, UPDATE, and DELETE operations from a DataSet or DataTable

Group INSERT, UPDATE, and DELETE operations from a DataSet or DataTable

Batch support in ADO.NET allows a DataAdapter to group INSERT, UPDATE, and DELETE operations from a DataSet or DataTable to the server, instead of sending one operation at a time. The reduction in the number of round trips to the server typically results in significant performance gains. Batch updates are supported for the .NET data providers for SQL Server (System.Data.SqlClient) and Oracle (System.Data.OracleClient).

public static void BatchUpdate(DataTable dataTable,Int32 batchSize) 
{ 
    // Assumes GetConnectionString() returns a valid connection string. 
    string connectionString = GetConnectionString(); 
 
    // Connect to the AdventureWorks database. 
    using (SqlConnection connection = new 
      SqlConnection(connectionString)) 
    { 
 
        // Create a SqlDataAdapter. 
        SqlDataAdapter adapter = new SqlDataAdapter(); 
 
        // Set the UPDATE command and parameters. 
        adapter.UpdateCommand = new SqlCommand( 
            "UPDATE Production.ProductCategory SET " 
            + "Name=@Name WHERE ProductCategoryID=@ProdCatID;", 
            connection); 
        adapter.UpdateCommand.Parameters.Add("@Name", 
           SqlDbType.NVarChar, 50, "Name"); 
        adapter.UpdateCommand.Parameters.Add("@ProdCatID", 
           SqlDbType.Int, 4, "ProductCategoryID"); 
         adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None; 
 
        // Set the INSERT command and parameter. 
        adapter.InsertCommand = new SqlCommand( 
            "INSERT INTO Production.ProductCategory (Name) VALUES (@Name);", 
            connection); 
        adapter.InsertCommand.Parameters.Add("@Name", 
          SqlDbType.NVarChar, 50, "Name"); 
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None; 
 
        // Set the DELETE command and parameter. 
        adapter.DeleteCommand = new SqlCommand( 
            "DELETE FROM Production.ProductCategory " 
            + "WHERE ProductCategoryID=@ProdCatID;", connection); 
        adapter.DeleteCommand.Parameters.Add("@ProdCatID", 
          SqlDbType.Int, 4, "ProductCategoryID"); 
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None; 
 
        // Set the batch size. 
        adapter.UpdateBatchSize = batchSize; 
 
        // Execute the update. 
        adapter.Update(dataTable); 
    } 
}

Referred from following link
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/performing-batch-operations-using-dataadapters

Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE

Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE

MERGE is a new feature that provides an efficient way to perform multiple DML operations.
One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

Consider there are two tables StudentDetails and StudentTotalMarks


In our example we will consider three main conditions while we merge this two tables.
1. Delete the records whose marks are more than 250.
2. Update marks and add 25 to each as internals if records exist.
3. Insert the records if record does not exists
MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);
GO



There are two very important points to remember while using MERGE statement.
• Semicolon is mandatory after the merge statement.
• When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.