databasedev.co.uk - database solutions and downloads for microsoft access

ADO.NET

Working with ADO.NET [Part III]

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.

Bulk Copy

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(); }

Batch Updates

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++;
        }
    }
}

Transaction Scope

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.

The Author

Joydip Kanjilal is a Microsoft MVP in ASP.NET.

He has more than 12 years of industry experience in IT with more than six years in Microsoft .NET and its related technologies.

He has authored articles for some of the most reputable sites, including http://www.asptoday.com, http://www.devx.com, http://www.aspalliance.com, http://www.aspnetpro.com, http://www.sql-server-performance.com, and http://www.sswug.com.

Many of these articles have been selected at http://www.asp.net, MicrosoftÂ’s official site for ASP.NET. Joydip was also a community credit winner at http://www.community-credit.com a number of times.

He is currently working as a Lead Architect in a reputable company in Hyderabad, India. He has years of experience in designing and architecting solutions for various domains. His technical strengths include, C, C++, VC++, Java, C#, Microsoft .NET, AJAX, Design Patterns, SQL Server, Operating Systems, and Computer Architecture.

Joydip blogs at http://aspadvice.com/blogs/joydip and spends most of his time reading books and blogs, and writing books and articles. His hobbies include watching cricket and soccer and playing chess