21 April 2019

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

1 comment:

Comments Welcome

Implementing OAuth validation in a Web API

 I mplementing OAuth validation in a Web API Implementing OAuth validation in a Web API using C# typically involves several key steps to sec...