Database Transactions with Multiple TableAdpaters

• Download demo - 907 KB

Introduction

TableAdapters, in conjunction with a Strongly Typed DataSet, provide functionality for filling DataTables or submitting updates to a database. While single queries work well within a TableAdapter, wrapping a transaction around multiple queries, from multiple TableAdapters, can present a challenge. In this article, I will walk you through the process of performing a transaction while using multiple TableAdapters.

This article is for programmers with a working knowledge of ADO.NET, Strongly Typed Datasets, DataTables, TableAdapters, and Partial Classes. If you are unfamiliar with any of these topics, then this article may not be easy to follow.

Summary

Processing a transaction using multiple Table Adapters, basically, requires only three things:

1. Each TableAdapter must share the same Connection object.
2. A Transaction object must be created from the shared Connection.
3. Every Command object that will be executed in the transaction must have its Transaction property assigned to the Transaction object created in step 2.

While there may be several different ways to satisfy these criteria, I will be using an approach that hinges on Partial Classes.

Scenario

Recently, I was tasked with writing an application to manage documents externally from a system that was developed by a third party vendor. The data which pertains to each document was being stored across multiple tables in an Oracle database. Adding a new document to the system required inserting one record each into three separate tables and twelve records into another.

Fifteen insert commands must execute for each new document added to the system. If any one of these inserts fails, then any changes made by the previous commands must be undone. The easiest way to obtain this functionality is by grouping the commands into a Transaction. However, I wanted to do this from within a Strongly Typed Dataset, and that presented a problem.

Scope

For the purposes of this article, I will be using SQL Express instead of Oracle. I will keep the code simple, and will not include things like validation, exception handling, etc. I will also limit the example database to two tables, using data that has no practical meaning. The only intended purpose of this article is to provide a good understanding of how to use multiple Table Adapters within a Transaction.

Data

In the database, there are two tables. The EMPLOYEES table (parent) consists of two columns: ID and NAME. The EMP_PROPERTIES table consists of four columns: ID, NAME, VALUE, and EMP_ID. Each Employee may have one or more Properties associated with him. Each Property must belong to one and only one Employee.

Solution Setup

To get started, create a new Blank Solution and name it Demo1.

Next, add a new Class Library project and name it Demo1_DAL. This project will be used for our Data Access Layer. Delete the default Class1.cs file that was added automatically.

Add a second project, using the Console Application template, and accept the default name of ConsoleApplication1. This project will be used as a simple interface for testing. The source code for this article includes a SQL Express file named Demo1.mdf. Add this file to your ConsoleApplication1 project.

Data Access Layer

Add a new DataSet to the Demo1_DAL project, and name it DataSet1.xsd. Visual Studio’s Dataset Designer should now be visible. If the Demo1.mdf database is not visible from the Server Explorer, then double-click the file from your Solution Explorer to open it. From the Server Explorer, under Data Connections, expand the Tables folder for Demo1.mdf. Drag the EMPLOYEES table onto the Dataset Designer, and then do the same with the EMP_PROPERTIES table.

By default, the Insert, Update, and Delete methods are created automatically for the Table Adapters. Although the Insert method contains the SQL code to retrieve the SCOPE_IDENTITY() of the ID column, the query is being run using the ExecuteNonQuery() method of the Data Adapter, instead of ExecuteScalar(). I have not found anywhere that this can be changed. (Thanks Microsoft, that makes a lot of sense.) There is more than one way around this, but I chose to add a new Insert method to the Table Adapter.

From the Dataset Designer, right click on the EMPLOYEESTableAdapter and select ‘Add Query...’ from the menu. Continue through the wizard, adding a new Insert query with the following SQL:

INSERT INTO [dbo].[EMPLOYEES] ([NAME]) VALUES (@NAME);
SELECT ID FROM EMPLOYEES WHERE (ID = SCOPE_IDENTITY())

Since an Insert method already exists for the EMPLOYEESTableAdapter, you will have to give the new Insert query a different name. I named mine InsertScalar(). Once you have added the query, right-click on the method signature in the Dataset Designer, and choose ‘Properties’ from the menu. Set the ExecuteMode to Scalar. Save your changes and close the Dataset Designer.

Next, we will employ Partial Classes to extend the functionality of our DataSet and Table Adapters. This will provide the needed support for Transactions. Right-click DataSet1.xsd in the Solution Explorer, then select the ‘View Code’ menu option. This will create a DataSet1.cs file with the following code in it:

namespace Demo1_DAL
{
    partial class DataSet1
    {
    }
}

This is where we will be adding our own custom methods to the DataSet1 class. By default, a declaration for the DataSet1 class is all that gets created for us. We will also be extending the functionality for each Table Adapter being used by the dataset. Since these classes exist in a different namespace, we must add a second namespace to our DataSet1 code file. Add the following code, to the end of the DataSet1 code file:

namespace Demo1_DAL.DataSet1TableAdapters
{
    partial class EMPLOYEESTableAdapter
    {
    }
    partial class EMP_PROPERTIESTableAdapter
    {
    }
}

Note: Methods used to extend the functionality of a Designer generated class are often referred to as ‘Helper’ methods.

We need to provide a public mechanism for assigning our Transaction object to each of our Command objects. These Command objects belong to the Data Adapter used within each Table Adapter. Since the Data Adapter is a private property of the Table Adapter object, we can not access the Command objects directly from outside the Table Adapter class. In order to accommodate this, we will be adding our own public methods to the Table Adapters.

Remember that we added a custom insert method, named InsertScalar(), to the EMPLOYEESTableAdapter. We will also need to assign our Transaction to its Command object. Since this Command is stored in an array of Command objects, we will simply assign all Command objects in the array to the Transaction. (This prevents us from having to hard-code the exact position of the Command within the array. If more queries are added to the Table Adapter in the future, then this position could change.)

To do this, add the following method to both the EMPLOYEESTableAdapter and EMP_PROPERTIESTableAdapter classes:

public void AttachTransaction(System.Data.SqlClient.SqlTransaction t)
{
    this.Adapter.InsertCommand.Transaction = t;
    this.Adapter.UpdateCommand.Transaction = t;
    this.Adapter.DeleteCommand.Transaction = t;
    foreach (System.Data.SqlClient.SqlCommand cmd
             in this.CommandCollection)
    {
        cmd.Transaction = t;
    }
}

This is the only addition that we need to make to our Table Adapter classes.

Next, we are going to add a new method to our DataSet1 class. I will name the method InsertEmployee. This method will be declared as static, so it can be called without having to instantiate a DataSet1 object. This method will require two parameters; a string containing the Employee’s name, and a Hashtable containing a collection of name-value pairs for each Employee property. This method will also return the number of rows affected. The method declaration should look something like this:

public static int InsertEmployee(string pName,
                  System.Collections.Hashtable pProps)
{
}

The code which makes up the body of this method is listed below. I have added comments to the code to help explain what it is doing.

int myReturnValue = 0;  // Rows affected
// Create Table Adapters
DataSet1TableAdapters.EMPLOYEESTableAdapter taEmp = new
                  DataSet1TableAdapters.EMPLOYEESTableAdapter();
DataSet1TableAdapters.EMP_PROPERTIESTableAdapter taProps = new
                  DataSet1TableAdapters.EMP_PROPERTIESTableAdapter();
// Open a Connection on one of the adapters and
// Make sure All Adapters are using the same connection.
taEmp.Connection.Open();
taProps.Connection = taEmp.Connection;
// Start a Transaction on the Open Connection
System.Data.SqlClient.SqlTransaction myTrans =
                  taEmp.Connection.BeginTransaction();
// Assign the Transaction to our Table Adpaters Command objects
taEmp.AttachTransaction(myTrans);
taProps.AttachTransaction(myTrans);
// Process Updates
try
{
    //Retrieve SCOPE_IDENTITY() of new Employee
    int myEmpID = System.Convert.ToInt32(taEmp.InsertScalar(pName));
    myReturnValue += 1;
    foreach (System.Collections.DictionaryEntry de in pProps)
    {
        taProps.Insert(de.Key.ToString(),
        de.Value.ToString(), myEmpID);
        myReturnValue += 1;
    }
    // Commit Updates
    myTrans.Commit();
}
catch
{
    // Rollback on any Exception
    myTrans.Rollback();
    myReturnValue = 0;
}
finally
{
    // Dispose of unmanaged resources
    myTrans.Dispose();
    taProps.Dispose();
    taEmp.Dispose();
}
return myReturnValue;

In the InsertEmployee method, I have intentionally kept the parameters simple. There may be situations were it would be better to pass other objects, such as a dataset, datatables, datarows, etc.

Notice that I also included the InsertEmployee method in the DataSet1 class. If you wanted the ability to bind this method to an Object Datasource, then you could have just as easily included it in one of the Table Adapters instead.

This concludes the code that is needed in the Data Access Layer. Save your changes, and build the Demo1_DAL project.

Testing

Now that our DAL is complete, we are now ready to test it. For this part, we will be working with the ConsoleApplication1 project.

The first thing we need to do is add a reference to our DAL. Right-click on the ConsoleApplication1 project and select ‘Add Reference...’ from the menu. Select the ‘Projects’ tab, make sure that the Demo1_DAL project is highlighted, and click OK.

Next, we will create some simple console prompts to gather some data and insert it into the database. Add the following code to the Main method of the Program.cs file:

string LoopAgain;
string EmpName;
string PropName;
string PropVal;
do
{
    System.Collections.Hashtable EmpProps =
                          new System.Collections.Hashtable();
    Console.WriteLine("Enter Employees Name:");
    EmpName = Console.ReadLine();
    do
    {
        Console.WriteLine("Enter Property Name:");
        PropName = Console.ReadLine();
        Console.WriteLine(string.Format("Enter value for {0}:",
                                        PropName));
        PropVal = Console.ReadLine();
        EmpProps.Add(PropName, PropVal);
        Console.WriteLine("Add another Property?");
        LoopAgain = Console.ReadLine();
    }
    while (LoopAgain.ToUpper() == "Y");
    int RowsAdded = Demo1_DAL.DataSet1.InsertEmployee(EmpName,
                                                      EmpProps);
    Console.WriteLine(string.Format("{0} records added.",
                                    RowsAdded.ToString()));
    Console.WriteLine("Add another Employee?");
    LoopAgain = Console.ReadLine();
}
while (LoopAgain.ToUpper() == "Y");

Be sure to set the ConsoleApplication1 project as the Startup Project, and then run it.

Conclusion

Partial Classes are a very powerful tool in Visual Studio’s arsenal. Once you understand the base structure of a class, it is very easy to extend its functionality. Datasets and Table Adapters were not built with a default behavior to support transactions. By using Partial Classes, it is very easy for us to add this support and/or any other functionality we desire.