Users Online

· Guests Online: 159

· Members Online: 0

· Total Members: 188
· Newest Member: meenachowdary055

Forum Threads

Newest Threads
No Threads created
Hottest Threads
No Threads created

Latest Articles

· Do while loop
· The for loop in C
· C while loop
· Pointers in C
· Functions in C

Articles Hierarchy

Articles Home » DOTNET » Batch Operations in C# Using ADO.NET DataAdapters:

Batch Operations in C# Using ADO.NET DataAdapters:

Batch Operations in C# Using ADO.NET DataAdapters:

In this article, I am going to discuss How to Perform Batch Operations in C# Using ADO.NET DataAdapters with Examples. Please read our previous article where we discussed How to Perform Bulk INSERT using SqlBulkCopy Class in C# with Examples.

 

Performing Batch Operations in C# Using ADO.NET DataAdapters

Using ADO.NET DataAdapter, we can perform batch INSERT, UPDATE, and DELETE operations from a DataSet or DataTable to the server, instead of performing the operations one by one. So, with Batch Operations we can reduce the number of round trips to the server which ultimately improves the performance of the application. Now, the SQL Server Data Provider (System.Data.SqlClient) and Oracle Data Provider (System.Data.OracleClient) support Batch updates i.e. batch INSERT, UPDATE, and DELETE operations.

 

In ADO.NET 1.0, when we are updating the database with changes from a DataSet or DataTable, the Update method of the DataAdapter object performed updates to the database one row at a time. It basically iterated through the rows in the specified DataTable, then it will check DataRow to see if it had been modified. If the row had been modified, it called the appropriate UpdateCommand, InsertCommand, or DeleteCommand, depending on the value of the RowState property for that data row. Every row update involved a network round-trip to the database.

  

But from ADO.NET 2.0, the DataAdapter provides one property called UpdateBatchSize. We need to set this UpdateBatchSize property to a positive integer number and then that number updates are sent to the database as a batch. For example, setting the UpdateBatchSize to 10 will group 10 separate statements and submit them as a single batch. Setting the UpdateBatchSize to 0 will cause the DataAdapter to use the largest batch size that the server can handle. Setting it to 1 disables batch updates which means updates are going to be performed one by one.

Note: Executing a large batch can decrease your application performance. Therefore, you should test for the optimum batch size setting before implementing it in your application.

   

Using DataAdapter UpdateBatchSize Property in C#:

When we enabled batch updates i.e. setting the UpdateBatchSize property to a positive integer number greater than 1, then the UpdatedRowSource property value of the DataAdapter’s UpdateCommand, InsertCommand, and DeleteCommand should be set to None or OutputParameters. This is because when we are performing a Batch update, we do not want to return anything.

Example to Understand Batch Operations in C# ADO.NET

We are going to use the following Employee tables to understand the Batch INSERT, UPDATE, and DELETE Operations using ADO.NET Data Adapter.

 

Example to Understand Batch Operations in C# ADO.NET

 

Please use the following SQL Script to create the EmployeeDB and populate the Employee table with the required sample data.

CREATE DATABASE EmployeeDB;
 GO 
 
 USE EmployeeDB;
 GO 
 
 CREATE TABLE Employee(
Id INT IDENTITY(100, 1) PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(50),
Mobile VARCHAR(50),
)
 GO 
 
 INSERT INTO Employee VALUES ('Anurag','Anurag@dotnettutorial.net','1234567890')
 INSERT INTO Employee VALUES ('Priyanka','Priyanka@dotnettutorial.net','2233445566')
 INSERT INTO Employee VALUES ('Preety','Preety@dotnettutorial.net','6655443322')
 INSERT INTO Employee VALUES ('Sambit','Sambit@dotnettutorial.net','9876543210')
 GO

Note: We are going to discuss the Batch INSERT, UPDATE, and DELETE by taking three different examples. Let us start with the Batch INSERT Operation using SQL Data Adapter.

Batch INSERT Operations in C# using ADO.NET DataAdapter:

The following example shows how to use the DataAdapter UpdateBatchSize property to perform Batch INSERT Operation. The BatchInsert method takes two parameters. The first parameter is a DataSet which contains the data which we want to INSERT into the database, and the second parameter is an integer number representing the batch size (the number of rows that we need to INSERT or sent to the database server as a batch). For a better understanding, please have a look at the following image.

  

Batch INSERT in C# using ADO.NET DataAdapter

Let us understand the above code in detail step by step:

   

  1. Step 1: Create an Instance of SqlDataAdapter class as using this instance we are going to perform the Batch Insert Operation.
  2. Step 2: As we are going to perform the Batch INSERT, so we need to set the InsertCommand property of the SqlDataAdapter instance to the command object. The command object must include the command text (i.e. the INSERT Query) and connection object where we want to execute the INSERT query.
  3. Step 3: Then we need to add the parameters which are required for our INSERT query. You can see, in our INSERT query, we are using three parameters i.e. @Name, @Email, @Mobile. So, we need to add these parameters in the Parameters collection and also, we need to specify the source column name from where the value is coming i.e. the data table column names.
  4. Step 4: In the next step, we need to set the UpdateRowSource property value to None as we are simply going to ignore the output parameter and return values.
  5. Step 5: Next, we need to set the batch size i.e. how many rows or records we want to send to the database server as a batch.
  6. Step 6: Finally, we need to call the Update method on the SqlDataAdapter instance by passing the data table and the data table should have some data to be inserted into the database.

The complete example code is given below. As you can see, from the Main method we are creating a DataTable and storing some rows and then calling the BatchInsert method and here, we are passing the data table and the batch size as 3. That means at a time, three rows are going to send to the data server for processing.

 

using System; 
 using System.Data; 
 using System.Data.SqlClient; 
 
 namespace BatchOperationUsingSqlDataAdapter
 { 
class Program
{ 
static void Main(string[] args) 
{ 
try 
{ 
//Creating Data Table 
DataTable EmployeeDataTable = new DataTable("Employees");
 
//Add Columns to the Data Table as per the columns defined in the Table Type Parameter 
DataColumn Name = new DataColumn("Name");
EmployeeDataTable.Columns.Add(Name);
 
DataColumn Email = new DataColumn("Email");
EmployeeDataTable.Columns.Add(Email);
 
DataColumn Mobile = new DataColumn("Mobile");
EmployeeDataTable.Columns.Add(Mobile);
 
//Follwoing Rows are going to be Inserted 
EmployeeDataTable.Rows.Add("A", "A@dotnettutorials.net", "12345");
EmployeeDataTable.Rows.Add("B", "B@dotnettutorials.net", "23456");
EmployeeDataTable.Rows.Add( "C", "C@dotnettutorials.net", "34567");
EmployeeDataTable.Rows.Add( "D", "D@dotnettutorials.net", "45678");
EmployeeDataTable.Rows.Add( "E", "E@dotnettutorials.net", "56789");
EmployeeDataTable.Rows.Add( "F", "F@dotnettutorials.net", "67890");
 
BatchInsert(EmployeeDataTable, 3);
} 
catch (Exception ex) 
{ 
Console.WriteLine($"Exception Occurred: {ex.Message}");
} 
 
Console.ReadKey();
} 
public static void BatchInsert(DataTable dataTable, int batchSize) 
{ 
// connection string.  
string connectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
 
// Connect to the EmployeeDB database.  
using (SqlConnection connection = new SqlConnection(connectionString)) 
{ 
// Create a SqlDataAdapter object  
SqlDataAdapter adapter = new SqlDataAdapter();
 
// Set the INSERT Command and Parameter.  
string InsertQuery = "INSERT INTO Employee (Name, Email, Mobile) VALUES (@Name, @Email, @Mobile);";
adapter.InsertCommand = new SqlCommand(InsertQuery, connection);
adapter.InsertCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "Name");
adapter.InsertCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 50, "Email");
adapter.InsertCommand.Parameters.Add("@Mobile", SqlDbType.NVarChar, 50, "Mobile");
//Set UpdatedRowSource value as None 
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
 
// Set the batch size.  
adapter.UpdateBatchSize = batchSize;
 
// Execute the update.  
adapter.Update(dataTable);
 
Console.WriteLine($"Batch Insert with size {batchSize} Successful");
} 
} 
} 
 }

Output: Batch Insert with size 3 Successful

 

Now, you can verify the newly inserted records in the Employee database and you should see the new employee’s data as shown in the below image.

  

How to Perform Batch Operations in C# Using ADO.NET DataAdapters with Examples

 

Batch UPDATE Operations in C# using ADO.NET DataAdapter:

The Batch UPDATE using SQL Data Adapter is very much similar to the Batch INSERT, the difference is that here instead of the INSERT Query, we need to prepare the UPDATE query, and instead of the InsertCommand, we need to use the UpdateCommand. For a better understanding, please have a look at the following example which is going to perform the Batch UPDATE operations using ADO.NET Data Adapter.

using System; 
 using System.Data; 
 using System.Data.SqlClient; 
 
 namespace BatchOperationUsingSqlDataAdapter
 { 
class Program
{ 
static void Main(string[] args) 
{ 
try 
{ 
// Connection string.  
string connectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
// Connect to the EmployeeDB database.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Create a SqlDataAdapter
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM EMPLOYEE", connection);
//Fetch the Employee Data and Store it in the DataTable
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
// Set the UPDATE command and parameters.
string UpdateQuery = "UPDATE Employee SET Name=@Name, Email=@Email, Mobile=@Mobile WHERE ID=@EmployeeID;";
adapter.UpdateCommand = new SqlCommand(UpdateQuery, connection);
adapter.UpdateCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "Name");
adapter.UpdateCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 50, "Email");
adapter.UpdateCommand.Parameters.Add("@Mobile", SqlDbType.NVarChar, 50, "Mobile");
adapter.UpdateCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 4, "ID");
//Set UpdatedRowSource value as None
//Any Returned parameters or rows are Ignored.
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
//Change the Column Values of Few Rows
DataRow Row1 = dataTable.Rows[0];
Row1["Name"] = "Name Changed";
DataRow Row2 = dataTable.Rows[1];
Row2["Email"] = "Email Changed";
DataRow Row3 = dataTable.Rows[2];
Row2["Mobile"] = "Mobile Changed";
// Set the batch size.
adapter.UpdateBatchSize = 2;
// Execute the update.
adapter.Update(dataTable);
Console.WriteLine($"Batch UPDATE with size 2 Successful");
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}

Output: Batch UPDATE with size 2 Successful

Now, you can verify the updated records in the Employee database and you should see the updated data as shown in the below image.

Batch UPDATE in C# using ADO.NET DataAdapter

Batch DELETE Operations in C# using ADO.NET DataAdapter in C#:

The Batch DELETE using SQL Data Adapter is very much similar to the Batch INSERT and UPDATE, the difference is that here instead of the INSERT and UPDATE Query, we need to prepare the DELETE query, and instead of the InsertCommand and UpdateCommand, we need to use the DeleteCommand. For a better understanding, please have a look at the following example which is going to perform the Batch DELETE operations using ADO.NET Data Adapter. So, here, we need to call the Delete method on the Data Row which we need to delete from the database.

 

using System;
using System.Data;
using System.Data.SqlClient;
namespace BatchOperationUsingSqlDataAdapter
{
class Program
{
static void Main(string[] args)
{
try
{
// Connection string.
string connectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
// Connect to the EmployeeDB database.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Create a SqlDataAdapter
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM EMPLOYEE WHERE Id > 105", connection);
//Fetch the Employee Data and Store it in the DataTable
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
// Set the DELETE command and parameter.
string DeleteQuery = "DELETE FROM Employee WHERE ID=@EmployeeID;";
adapter.DeleteCommand = new SqlCommand(DeleteQuery, connection);
adapter.DeleteCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 4, "Id");
//Set UpdatedRowSource value as None
adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
// Set the batch size.
adapter.UpdateBatchSize = 2;
foreach (DataRow row in dataTable.Rows)
{
//Delete the Data Row from the Data Table
row.Delete();
}
// Execute the update.
adapter.Update(dataTable);
Console.WriteLine($"Batch DELETE with size 2 Successful");
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}

Output: Batch DELETE with size 2 Successful

Now, you can verify the Employee table and you should not see the deleted data as shown in the below image.

Batch DELETE in C# using ADO.NET DataAdapter in C#

Performing Batch INSERT, UPDATE, and DELETE using a Single Update:

In our last three examples, we have discussed how to perform the Batch INSERT, UPDATE, and DELETE Operations in C# using ADO.NET Data Adapter. It is also possible that using a single Update call, we can perform the Batch INSERT, UPDATE, and DELETE Operations. In this case, based on the RowState property of the data row of a data table, the respective command object is going to be executed. DataRowState is an enum with the following values.

Performing Batch INSERT, UPDATE, and DELETE using a Single Update

The following are the meaning of each enum value:

  1. Detached: The row has been created but is not part of any DataRowCollection. A DataRow is in this state immediately after it has been created and before it is added to a collection, or if it has been removed from a collection
  2. Unchanged: The Data Row has not changed since the AcceptChanges was last called.
  3. Added: The Data Row has been added to a DataRowCollection, and the AcceptChange method has not been called. This will cause the InsertCommand to fire in a Batch operation while performing using ADO.NET Data Adapter.
  4. Deleted: The Data Row has been deleted using the Delete method of the DataRow object. This will cause the DeleteCommand to fire in a Batch operation while performing using ADO.NET Data Adapter.
  5. Modified: The row has been modified and the AcceptChanges have not been called. This will cause the UpdateCommand to fire in a Batch operation while performing using ADO.NET Data Adapter.
Example to Understand Batch INSERT, UPDATE and DELETE Operations in C# using Data Adapter:

Let us first modify the Employee table data by executing the following SQL Statement. Here, we are simply truncating the table and then adding four records.

 

TRUNCATE TABLE Employee;
GO
INSERT INTO Employee VALUES ('Anurag','Anurag@dotnettutorial.net','1234567890')
INSERT INTO Employee VALUES ('Priyanka','Priyanka@dotnettutorial.net','2233445566')
INSERT INTO Employee VALUES ('Preety','Preety@dotnettutorial.net','6655443322')
INSERT INTO Employee VALUES ('Sambit','Sambit@dotnettutorial.net','9876543210')
GO

Once you execute the above SQL queries, the Employee table should now contain the following data.

Example to Understand Batch INSERT, UPDATE and DELETE Operations in C# using Data Adapter

Now, in the above database table, we are going to perform batch INSERT, UPDATE, and DELETE Operations using a single Update method. We are basically going to add two new records with the IDs 104 and 105, then we are going to update the Name and Email of the first two employees and finally, we are going to delete the employees whose IDs are 103 and 104. The following example code exactly does the same. The following example code is self-explained, so please go through the comment lines.

using System;
using System.Data;
using System.Data.SqlClient;
namespace BatchOperationUsingSqlDataAdapter
{
class Program
{
static void Main(string[] args)
{
try
{
BatchUpdate();
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
public static void BatchUpdate()
{
// Connection string
string connectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
// Connect to the EmployeeDB database.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Create SqlDataAdapter Instance and Fetch the Employee data and store in the Data Table
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM EMPLOYEE", connection);
DataTable EmployeeDataTable = new DataTable();
adapter.Fill(EmployeeDataTable);
//Add Few Records into the DataTable
//These Data Row has been added to a DataRowCollection, and
//the AcceptChange method has not been called.
//This will cause the InsertCommand to fire in a Batch operation
//while performing using ADO.NET Data Adapter.
EmployeeDataTable.Rows.Add(104, "Pranaya", "Pranaya@dotnettutorials.net", "1234512345");
EmployeeDataTable.Rows.Add(105, "Kumar", "Kumar@dotnettutorials.net", "2345623456");
// Set the INSERT Command and the required Parameter.
string InsertQuery = "INSERT INTO Employee (Name, Email, Mobile) VALUES (@Name, @Email, @Mobile);";
adapter.InsertCommand = new SqlCommand(InsertQuery, connection);
adapter.InsertCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "Name");
adapter.InsertCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 50, "Email");
adapter.InsertCommand.Parameters.Add("@Mobile", SqlDbType.NVarChar, 50, "Mobile");
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
// Set the UPDATE command and parameters.
string UpdateQuery = "UPDATE Employee SET Name=@Name, Email=@Email, Mobile=@Mobile WHERE ID=@EmployeeID;";
adapter.UpdateCommand = new SqlCommand(UpdateQuery, connection);
adapter.UpdateCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "Name");
adapter.UpdateCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 50, "Email");
adapter.UpdateCommand.Parameters.Add("@Mobile", SqlDbType.NVarChar, 50, "Mobile");
adapter.UpdateCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 4, "ID");
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
//Change the Column Values of Few Rows
//These rows has been modified and the AcceptChanges has not been called.
//This will cause the UpdateCommand to fire in a Batch operation
//while performing using ADO.NET Data Adapter.
DataRow Row1 = EmployeeDataTable.Rows[0];
Row1["Name"] = "Name Changed";
DataRow Row2 = EmployeeDataTable.Rows[1];
Row2["Email"] = "Email Changed";
// Set the DELETE command and its required parameter.
string DeleteQuery = "DELETE FROM Employee WHERE ID=@EmployeeID;";
adapter.DeleteCommand = new SqlCommand(DeleteQuery, connection);
adapter.DeleteCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 4, "Id");
adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
foreach (DataRow row in EmployeeDataTable.Rows)
{
//Delete Third and Fourth Row
if (Convert.ToInt32(row["Id"]) == 102 || Convert.ToInt32(row["Id"]) == 103)
{
//The Data Row has been deleted using the Delete method of the DataRow object.
//This will cause the DeleteCommand to fire in a Batch operation while performing
//using ADO.NET Data Adapter.
row.Delete();
}
}
// Set the batch size.
adapter.UpdateBatchSize = 2;
// Execute the Update method
adapter.Update(EmployeeDataTable);
Console.WriteLine($"Batch Operations with size 2 Completed Successfully");
}
}
}
}

Output: Batch Operations with size 2 Completed Successfully

Now, you can verify the data in the Employee table and you should get the data as expected as shown in the below image.

Example to Understand Batch INSERT, UPDATE and DELETE Operations in C# using ADO.NET Data Adapter

In the next article, I am going to discuss Connected and Disconnected Architecture in ADO.NET with Examples. Here, in this article, I try to explain How to Perform Batch Operations in C# Using ADO.NET Data Adapters with Examples. I hope you enjoy this How to Perform Batch Operations in C# Using ADO.NET Data Adapters article.

· Posted by Superadmin on November 15 2023 · 0 Comments · 143 Reads · Print

Comments

No Comments have been Posted.

Post Comment

Please Login to Post a Comment.

Ratings

Rating is available to Members only.

Please login or register to vote.
No Ratings have been Posted.
Render time: 1.56 seconds
10,808,716 unique visits