In the first two parts of this series of articles on ADO.NET, we have learnt the basics of ADO.NET and also how we can use ADO.NET to perform CRUD operations in our applications. In this concluding part of this series, we will explore some advanced issues like Bulk Copy, Batch Updates and Transaction Scope.
The bulk copy feature of ADO.NET is used to transfer large amount of data from a source of data to its destination. In ADO.NET each of the data providers provide support for bulk copy operations, i.e., you have specific bulk copy classes for each of the data providers that ADO.NET supports. Using these classes, you can perform single or multiple bulk copy operations. You can also perform bulk copy operations within transactions.
Here is an example that illustrates how you can perform bulk copy operations using ADO.NET:
String connectionString = "Data Source=.;Initial Catalog=Student;
User ID=sa;Password=sa"; SqlConnection sourceConnection; SqlConnection targetconnection; try { //Create source and destination connection instances sourceConnection = new SqlConnection(connectionString); targetconnection = new SqlConnection(connectionString); //Read data from source SqlCommand sqlCommand = new SqlCommand("SELECT * FROM StudentsMaster", source); SqlDataReader sqlDatareader = sqlCommand.ExecuteReader(); //Perform the bulk copy operation to copy the data to the target SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(targetconnection); sqlBulkCopy.DestinationTableName = "SampleBackup"; sqlBulkCopy.WriteToServer(sqlDatareader); } catch(Exception ex) { //Write your error handling code here } finally { //Close theinstances sqlDatareader.Close(); sqlBulkCopy.Close(); targetconnection.Close(); sourceConnection.Close(); }
The batch update feature of ADO.NET is used for improving performance of data centric applications. When we use the Data Adapter for performing updates, each individual update operation is performed one at a time. Hence, if you have 10 rows to be updated, the Data Adapter will perform an update on each of these 10 rows as distinct operations. Hence, there would be 10 separate update operations executed, one for each row.
However, the SqlDataAdapter comes in with support for batch updates, where, you can send multiple update statements in a batch, so that, the entire batch is submitted at one shot. This improves the performance because the update statements are not executed individually rather; they are submitted to the database engine in one batch. The UpdateBatchSize property of the Data Adapter can be used to specify the size of one batch, i.e., the number of statements to be executed in one batch.
There are two important events associated to batch updates in ADO.NET. These are RowUpdating and RowUpdated. If you don't use the batch update feature, these events will be fired once for each update operation. However, if batch updates are used, the RowUpdating event will be fired once per row, but the RowUpdated event will be fired once after a batch has been submitted for updation.
As an example, if the UpdateBatchSize is 10 and you have two batches to update, the RowUpdating event will be called 10 times per batch, so, it will be executed 20 times in all. On the contrary, the RowUpdated event will be fired only 2 times, i.e., the number of times the batches will be submitted.
Here is the complete code.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace BatchUpdates
{
class Program
{
static String connectionString = "Data Source=.;
Initial Catalog=Student;User ID=sa;Password=sa";
private static int recordsUpdated = 0;
private static int batchCount = 0;
private const int BatchSize = 0;
static void Main(string[] args)
{
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString = connectionString;
try
{
sqlConnection.Open();
SqlDataAdapter sqlDataAdapter = new
SqlDataAdapter("Select * from StudentMaster", sqlConnection);
//Binding event handlers
sqlDataAdapter.RowUpdating += new
SqlRowUpdatingEventHandler(sqlDataAdapter_RowUpdating);
sqlDataAdapter.RowUpdated += new
SqlRowUpdatedEventHandler(sqlDataAdapter_RowUpdated);
//Populating a DataSet instance with data from the database
DataSet dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet, "StudentMaster");
//Updating data in the DataSet
foreach (DataRow row in dataSet.Tables[0].Rows)
{
String name = row["Name"].ToString();
row["Name"] = name;
String address = row["Address"].ToString();
row["Address"] = address;
String phone = row["Phone"].ToString();
row["Phone"] = phone;
}
//Creating Command instance
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = "Update StudentMaster Set Name=@Name, _
Address = @Address, Phone = @Phone where StudentID=@StudentID";
//Creating parameters
SqlParameter sqlParamName =
new SqlParameter("@Name", SqlDbType.VarChar);
sqlParamName.SourceColumn = "Name";
SqlParameter sqlParamAddress =
new SqlParameter("@Address", SqlDbType.VarChar);
sqlParamAddress.SourceColumn = "Address";
SqlParameter sqlParamPhone =
new SqlParameter("@Phone", SqlDbType.VarChar);
sqlParamPhone.SourceColumn = "Phone";
SqlParameter sqlParamStudentID =
new SqlParameter("@StudentID", SqlDbType.VarChar);
sqlParamStudentID.SourceColumn = "StudentID";
sqlCommand.Parameters.Insert(0, sqlParamName);
sqlCommand.Parameters.Insert(1, sqlParamAddress);
sqlCommand.Parameters.Insert(2, sqlParamPhone);
sqlCommand.Parameters.Insert(3, sqlParamStudentID);
sqlCommand.UpdatedRowSource = UpdateRowSource.None;
sqlDataAdapter.UpdateCommand = sqlCommand;
sqlDataAdapter.UpdateBatchSize = BatchSize;
sqlDataAdapter.Update(dataSet, "StudentMaster");
Console.WriteLine("Records updated :" + recordsUpdated);
Console.WriteLine("Batch count :" + batchCount);
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message);
}
finally
{
if (sqlConnection.State == ConnectionState.Open)
sqlConnection.Close();
}
Console.ReadLine();
}
private static void sqlDataAdapter_RowUpdating
(object sender, SqlRowUpdatingEventArgs e)
{
//This event will be fired for each record.
recordsUpdated++;
}
private static void sqlDataAdapter_RowUpdated
(object sender, SqlRowUpdatedEventArgs e)
{
//This event will be fired for each batch
batchCount++;
}
}
}
We have already discussed transactions in the earlier part in this series. In this section we will explore more on ADO.NET transactions. We will learn the TransactionScope class and how it can be used.
The TransactionScope class in the System.Transactions namespace provides support for distributed transactions. You can create and manage distributed transactions using the TransactionScope class.
Here is a sample code that illustrates how you can work with TransactionScope class in ADO.NET. Note that if you use TransactionScope, you need not commit or rollback the transaction instance manually - your transaction handling code gets much simplified if you use TransactionScope:
using (TransactionScope scope =
new TransactionScope(TransactionScopeOption.Required, options))
{
SqlConnection sqlConnection = new SqlConnection(connectionString);
SqlCommand sqlCommand;
sqlConnection.Open();
try
{
sqlCommand = new SqlCommand("Delete from Products", sqlConnection);
sqlCommand.ExecuteNonQuery();
sqlCommand = new SqlCommand("Delete from Stock", sqlConnection);
sqlCommand.ExecuteNonQuery();
IsConsistent = true;
}
catch (SqlException ex)
{
//Write your error handling code here
}
sqlConnection.Close();
}
Conclusion
In this series of articles on ADO.NET we have had a look at ADO.NET in details. We discussed the basics, the concepts involved and also the advanced issues. We took a look at how we can use ADO.NET to perform CRUD operations in our applications. We also discussed the advanced features in ADO.NET like, bulk copy, batch updates and transaction scope.