Develop Transactional .NET Web Services

With distributed applications becoming pervasive, you need tools and techniques to create, destroy, and monitor transactions across multiple platforms and servers. Having these tools and techniques available within a single architecture such as the .NET Framework makes it easy to develop distributed applications and handle transactions. This article looks at the transaction support the .NET Framework provides, which you can use to develop transactional .NET Web services.

Introduction to Transactions

Transaction processing is easy as long as the transaction involves only a single database. Unfortunately, a company rarely stores its data in a single database. In many cases, companies spread the information out over many databases. For example, an online retailer might store customer account information in a SQL Server database at the corporate office. But, it may store order-fulfillment information in an Oracle database at a partner company’s warehouse. When a customer places an order, the online retailer should debit the customer’s account in the SQL Server database at the corporate office and write an order record to the partner company’s Oracle database. So in this case, the retailer needs to perform the updates on both of these databases as a single transaction. If either operation fails, each resource manager will have to roll back its part of the transaction. This type of transaction is called a distributed transaction.

Considering the number of entities that may be involved in a distributed transaction, it may get really complex and require a lot of code for its support. After seeing the amount of time developers spent writing this type of plumbing code, Microsoft decided to build distributed transaction capabilities into the .NET Web services API. As its name suggests, this API provides for most of the plumbing code required to automate distributed transactions across .NET Web services.

The .NET Framework encapsulates most of the plumbing required for creating distributed, transaction-based Web services in easy-to-use, very flexible classes. These classes are contained in the System.EnterpriseServices namespace, and they rely on the services COM+ provides for supporting automatic transactions.

If you have done any MTS/COM+ programming using Visual Studio 5, you know that for a COM component to use COM+ services, it needs to have a reference to the ObjectContext interface that encapsulates most of the core COM+ functionality. The .NET framework has a class named ContextUtil that acts as a wrapper around the ObjectContext class. It also is contained in the System.EnterpriseServices namespace. The methods in the ContextUtil class are the primary means by which applications written in managed code communicate with Microsoft Distributed Transaction Coordinator (MSDTC).

One of the most important features the ContextUtil class provides is Transaction Processing Monitoring (TPM). The MSDTC internally maintains two flags through COM+, done bit and consistency bit, which you use not only to vote for the outcome of a transaction but also to control the deactivation of objects. Use the following methods to explore the combinations of done and consistency bits and indicate to the COM+ runtime the state of the transaction and whether the object can be deactivated:

• SetComplete: This method sets both done and consistency flags to true, which indicates that the current transaction can be committed and the object deactivated.
• SetAbort: This method sets the done bit to true and consistency flag to false, which indicates that the current transaction has to be rolled back, and the object deactivated.
• EnableCommit: This method sets the done bit to false and consistency flag to true.
• DisableCommit: This method sets both done and consistency flags to false.

The above-mentioned methods allow you to control both the transactional and the activation behavior of an object. However, you sometimes may want to individually manipulate the done bit and consistency bit for a finer level of control over the transactions. The ContextUtil class exposes the following two properties through which you can deal with the done bit and consistency bits individually:

• MyTransactionVote: a property get and set through which you can either get or set the value of the consistency bit
• DeactivateOnReturn: a property get and set that you use to either get or set the value of the done bit

You can programmatically specify whether you want to commit or rollback the transaction in a Web service in the following two ways:

• Using the utility methods (explained above) that the ContextUtil class supplies
• Using the AutoComplete attribute class

You will see both of these methods in action later.

This article demonstrates how to take advantage of the built-in transaction capabilities that .NET Web services provides when creating managed applications in the .NET Framework. It is sectioned into two parts:

• The first part shows you how to create two transactional Web service methods. Within these methods, you will use the ContextUtil class to control transactions.
• The second part shows you how to create Web service clients that will access your transactional Web services. While creating this Web service client, you also will learn how a transaction initiated by a Web service or a Web page can be used to control the transactional behavior of the consumed Web services.

Implementation of a Web Service

This demonstration begins by stepping you through the creation of two tables named Emp and Dept in a SQL Server database. You then will programmatically add records to these two tables by using EmployeeService and DeptService Web service classes. To start, create a new project named WebServiceTransactions, as shown in Figure 1.

Figure 1. Create a New Project

After you’ve created the project, rename the default Web service from Service1 to DeptService. The following section walks you through the code required to implement the DeptService Web service.

Implementation of DeptService Web Service

As mentioned previously, the DeptService class encapsulates the functionalities related to the Dept table, such as adding a new dept, updating an existing dept, and so on. This article considers only the addition of a new dept. The code for adding a new dept to the Dept table is as follows:

[WebMethod(false,TransactionOption.Required)]
public int AddDept(string deptName, string location)
{
   try
   {
      string connString =
         System.Configuration.ConfigurationSettings.AppSettings
         ["connectionString"];
      int deptNo;
      //Create the connectionection object passing to it the
      //connection string
      SqlConnection connection = new SqlConnection(connString);
      connection.Open();
      //Create and set the SqlCommand object
      SqlCommand command = new SqlCommand("AddDept",connection);
      command.CommandType = CommandType.StoredProcedure;

      //Add the DeptName parameter
      SqlParameter paramDeptName = new 
         SqlParameter("@DeptName",SqlDbType.VarChar,50);
      paramDeptName.Value = deptName;
      paramDeptName.Direction = ParameterDirection.Input;
      command.Parameters.Add(paramDeptName);

      //Add the Location parameter
      SqlParameter paramLocation = new
         SqlParameter("@Location",SqlDbType.VarChar,50);
      paramLocation.Value = location;
      paramLocation.Direction = ParameterDirection.Input;
      command.Parameters.Add(paramLocation);

      //Add the DeptNo parameter as the Output parameter
      SqlParameter paramDeptNo = new
         SqlParameter("@DeptNo",SqlDbType.Int,4);
      paramDeptNo.Direction = ParameterDirection.Output;
      command.Parameters.Add(paramDeptNo);

      command.ExecuteNonQuery();

      deptNo = (int)command.Parameters["@DeptNo"].Value;

      ContextUtil.SetComplete();
      return deptNo;

   }
   catch(Exception ex)
   {
      ContextUtil.SetAbort();
      throw ex;
   }
}

Because you want to expose the AddDept method as a Web service, you decorate that method with the WebMethod attribute. To the constructor of the WebMethod class, you supply two parameters. The first parameter indicates that you want to disable the session for that method, and the second parameter specifies that the AddDept method always be executed as part of a transaction. Due to the transaction attribute Required, your AddDept method will run within the scope of the caller’s transaction (if the caller of the method is already running in a transaction). Otherwise, the AddDept method will create a new transaction for itself and run within the scope of that transaction:

[WebMethod(false,TransactionOption.Required)]

The possible values you can specify for the TransactionOption enum are:

• Supported: Object will share the transaction of the client (if the client has one)
• NotSupported: Object is created in a context without any governing transaction
• Required: Shares the transaction with the client (if the client has one); otherwise, creates a new one for itself
• RequiresNew: Creates the object in a new transaction context regardless of the caller’s current transaction context state
• Disabled: Completely ignores any transaction in the current context

In the AddDept method, you execute a stored procedure named AddDept and return the newly created DeptID to the caller. The following is the code for the stored procedure (download the code for this stored procedure):

CREATE Procedure AddDept
   (
      @DeptName varchar(50),
      @Location varchar(50),
      @DeptNo int out
   )

as
   set nocount on
   insert into Dept
      (DeptName,Location)
   values
      (@DeptName,@Location)

   select @DeptNo = @@identity
GO

As you can see, the AddDept stored procedure simply inserts a record into the Dept table and then returns the newly created identity value to the caller:

public int AddDept(string deptName, string location)
{
   try
   {

The following lines of code retrieve the connection string from the web.config file:

string connString =
   System.Configuration.ConfigurationSettings.AppSettings
   ["connectionString"];

int deptNo;
//Create the SQLConnection object passing to it the connection
//string

Here you create the SqlConnection object by passing to it the connection string connects to the database:

SqlConnection sqlConn = new SqlConnection(connString);

Once you have created an instance of the SqlConnection object, you then can open the connection by invoking the Open method:

sqlConn.Open();

In this step, you create an instance of the SqlCommand object and pass to it the name of the stored procedure to be executed and the SqlConnection object as arguments:

//Create and set the SqlCommand object
SqlCommand sqlCommand = new SqlCommand("AddDept",sqlConn);

You then set the CommandType to CommandType.StoredProcedure to indicate that you want to execute a stored procedure:

sqlCommand.CommandType = CommandType.StoredProcedure;

Now that you have created the SqlCommand object, you can add the parameters to to the stored procedure by using the following code:

//Add the DeptName parameter
SqlParameter paramDeptName = new
   SqlParameter("@DeptName",SqlDbType.VarChar,50);
paramDeptName.Value = deptName;
paramDeptName.Direction = ParameterDirection.Input;
sqlCommand.Parameters.Add(paramDeptName);

//Add the Location parameter
SqlParameter paramLocation =
   new SqlParameter("@Location",SqlDbType.VarChar,50);
paramLocation.Value = location;
paramLocation.Direction = ParameterDirection.Input;
sqlCommand.Parameters.Add(paramLocation);

Because the DeptNo is specified as an output parameter in the stored procedure, you set the Direction property to ParameterDirection.Output:

//Add the DeptNo parameter as the Output parameter
SqlParameter paramDeptNo = new SqlParameter("@DeptNo",SqlDbType.Int,4);
paramDeptNo.Direction = ParameterDirection.Output;
sqlCommand.Parameters.Add(paramDeptNo);

Now that you have added all the parameters, you execute the stored procedure by invoking the ExecuteNonQuery method:

sqlCommand.ExecuteNonQuery();

After the stored procedure is executed, you can retrieve the output parameter (that the stored procedure returned) from the Parameters collection of the SqlCommand object:

deptNo = (int)sqlCommand.Parameters["@DeptNo"].Value;

Now that you have executed the stored procedure successfully, you can commit the transaction. You accomplish this by invoking the SetComplete method of the ContextUtil class:

   ContextUtil.SetComplete();
   return deptNo;

   }

If any errors occur during the execution of the above statements, the control will be transferred to the catch block, where you rollback all of the previously performed database operations by invoking the SetAbort method of ContextUtil class:

   catch(Exception ex)
   {
      ContextUtil.SetAbort();
      throw ex;
   }
}

Implementation of EmployeeService Web Service

Similar to the DeptService class, the EmployeeService class performs operations related to an employee. This article considers only the addition of a new employee using the EmployeeService class. (Click here to download the code for this article.)

Because most of the code is similar to the DeptService class, I will explain only the lines that are unfamiliar to you.

In the AddDept method, you controlled the outcome of the transaction by using the ContextUtil.SetComplete method (if everything went fine) and the ContextUtil.SetAbort method (if any exceptions occurred during the execution). You can achieve a similar result by using an attribute named AutoComplete. Instead of calling the SetComplete and SetAbort methods, you can delegate this responsibility to the AutoComplete attribute, which automatically calls either SetComplete (if no exceptions occur) or SetAbort (if an exception is thrown). For the AddEmp method, you will use the AutoComplete method:

[WebMethod(false,TransactionOption.Required)]
   [AutoComplete]
   public int AddEmp(string empName,string empAddress,int deptNo)
   {
      try
      {
      string connString =
         System.Configuration.ConfigurationSettings.AppSettings
         ["connectionString"];
      int empNo;
      SqlConnection sqlConn = new SqlConnection(connString);
      //Open the Connection to the database
      sqlConn.Open();

      //Create and set the SqlCommand object options
      SqlCommand sqlComm  = new SqlCommand("AddEmp",sqlConn);
      sqlComm.CommandType = CommandType.StoredProcedure;

      //Set the EmpName parameter
      SqlParameter paramName = new
         SqlParameter("@EmpName",SqlDbType.VarChar,50);
      paramName.Direction    = ParameterDirection.Input;
      paramName.Value        = empName;
      sqlComm.Parameters.Add(paramName);

      //Set the Address parameter
      SqlParameter paramAddress =
         new SqlParameter("@Address",SqlDbType.VarChar,250);
      paramAddress.Direction = ParameterDirection.Input;
      paramAddress.Value = empAddress;
      sqlComm.Parameters.Add(paramAddress);

      //Set the DeptNo parameter
      SqlParameter paramDeptNo =
         new SqlParameter("@DeptNo",SqlDbType.Int,4);
      paramDeptNo.Value = deptNo;
      paramDeptNo.Direction = ParameterDirection.Input;
      sqlComm.Parameters.Add(paramDeptNo);

      //Set the EmpNo parameter as the Stored Procedure return value
      SqlParameter paramEmpNo =
         new SqlParameter("@EmpNo",SqlDbType.Int,4);
      paramEmpNo.Direction = ParameterDirection.ReturnValue;
      sqlComm.Parameters.Add(paramEmpNo);

      //Execute the sql stored procedure
      sqlComm.ExecuteNonQuery();
      empNo = (int)sqlComm.Parameters["@EmpNo"].Value;
      return empNo;
   }
   catch(Exception e)
   {
      throw e;
   }
}

Now that you have seen the code required for implementing transactions in Web services, you can move on to the implementation of the client applications that invoke these Web services.

Implementation of client applications

To demonstrate the transactional behavior—and usefulness—of the Web services, you will create client applications that can initiate a transaction and then include your EmployeeService and DeptService Web services as part of that transaction. You’ll learn how these transactional Web services can all be part of a single transaction, hence the operations they perform either commit in their entirety or completely roll back. The two types of client applications you will create are:

• ASP.NET Web service application
• ASP.NET Web application

You will start by creating an ASP.NET Web service client application for your DeptService and EmployeeService Web services.

Implementation of ASP.NET Web Service Client Application

To start, add a new ASP.NET Web service class named DeptEmployeesService to your WebServiceTransactions project.

Once you’ve created the project, rename the default Web service to DeptEmployeesService. Add a new method called AddDeptEmployees to the code-behind file of the Web service. As the name of the method suggests, it will invoke the methods of your transactional Web services (DeptService and EmployeeService) to first add a new Dept and then add employees to that Dept. By making these operations part of a single transaction initiated by the Web service, you ensure that either both the Dept and Employee details are added or nothing is added. The code for the AddDeptEmployees method looks like the following:

[WebMethod(false,TransactionOption.Required)]
public bool AddDeptEmployees(string deptName,string deptLocation,
string empName,string empAddres)
{

   try
   {
   int deptNo;
      Dept deptDetails = new Dept();
      Emp empDetails   = new Emp();
      //Add the Dept details to the database
      deptNo = deptDetails.AddDept(deptName,deptLocation);

      //Add the Employee details to the database
      int empNo = empDetails.AddEmp(empName,empAddres,deptNo);
      return true;
   }
   catch(Exception e)
   {
      throw e;
   }
}

The WebMethod attribute indicates that the AddDeptEmployees method is to be exposed as a Web service, and when it is deployed, the Visual Studio.NET runtime provides all the plumbing required to make this method discoverable to the clients that wish to use this Web service. The code also specifies that you want your Web service to run in a transaction by using the TransactionOption.Required enum constant.

Next, create instances of the DeptService and EmployeeService classes, and then call the AddDept and AddEmp methods to add the dept and employee details to the database:

      //Add the Dept details to the database
      deptNo = deptDetails.AddDept(deptName,deptLocation);
      //Add the Employee details to the database
      int empNo = empDetails.AddEmp(empName,empAddres,deptNo);
      return true;
   }
   catch(Exception e)
   {
      throw e;
   }
}

You can test the above code by navigating to the path http://localhost/MyProjects/WebServiceTransactions/DeptEmployeesService.asmx. As a result, you should see a screen similar to Figure 2.

Figure 2. Test Harness Generated by ASP.NET

Using the above test harness (generated by the ASP.NET), you can test your Web service by supplying the required parameters. When you invoke the Web service, you get the screen displayed in Figure 3.

Figure 3. Test Your Web Service by Supplying the Required Parameters

Now, if you go back to the database, you will find that both the dept and employee details have been added successfully. You can make a slight modification to your code to see whether the DeptService and EmployeeService objects are running part of the single transaction initiated by the Web service. In the AddDeptEmployees method, you’d modify the following line of code:

int empNo = empDetails.AddEmp(empName,empAddres,deptNo);

In the above line, you replace the deptNo variable with the value of 100 (assuming no dept has the DeptNo 100 in the Dept table). After modification, it looks like the following:

int empNo = empDetails.AddEmp(empName,empAddres,100);

Now, if you execute the Web service, you will get the exception message in Figure 4, which cites a Foreign Key Violation. And, if you check the database, you will find that no values have been added to either the Dept table or the Emp table. This clearly shows that both the objects are running part of the same transaction initiated by the Web service.

Figure 4. Exception Message Citing a Foreign Key Violation

Implementation of ASP.NET Web Application

When you created the ASP.NET Web service client application, you saw how to initiate a transaction from the Web service by making use of the Transaction attribute. In the same way, you can create ASP.NET Web forms that can initiate a transaction and then include other objects in it. To accomplish this, you need to add an attribute to the Page Directive named transaction. By using this attribute, you can indicate to the ASP.NET runtime whether the page needs to be run in a transaction. The values you can use with the transaction attribute are Disabled, NotSupported, Supported, Required, and RequiresNew.

To illustrate the usage of the transactions in ASP.NET Web forms, consider the following code snippet:

<%@ Page transaction="Required" language="c#"
    Codebehind="WebForm1.aspx.cs" AutoEventWireup="false"
    Inherits="WebServiceTransactionsClient.WebForm1" %>

In the Page directive, you set the transaction attribute to Required, indicating that this ASP.NET page runs in a transaction. Next, you also need to add Web reference to the EmployeeService and DeptService. To do this, select Project->Add Web Reference from the menu and type in the location of the Web services. Then click the Add Reference command button to add those references.

In the Design view of the Web form, add the required controls for capturing dept and emp details, and then add a command button to save the entered information. In the Click event of the command button, add the following code:

private void btnSubmit_Click(object sender, System.EventArgs e)
{
   try
   {
      int deptNo;
      DeptService dept    = new DeptService();
      EmployeeService emp = new EmployeeService();
      //Add the Dept details to the database
      deptNo = dept.AddDept(txtDeptName.Text,txtLocation.Text);
      //Add the Employee details to the database
      int empNo = 
         emp.AddEmp(txtEmployeeName.Text,txtAddress.Text,deptNo);
      lblResult.Text = "Transaction completed succesfully";
   }
   catch(Exception ex)
   {
      lblResult.Text = "Transaction failed";
   }
}

When you execute the above code, you get the screenshot in Figure 5 as a result.

Figure 5. Successfully Completed Transaction

Transactional Web Services Made Easy

You have seen how to create transactional Web services by using the classes supplied by the .NET Framework. You also saw how easy it is to leverage those transactional Web services from client applications such as an ASP.NET Web service and an ASP.NET Web application.

Download the Code

To download the accompanying source code for this article, click here.

About the Author

Thiru Thangarathinam has six years of experience in architecting, designing, developing, and implementing applications using object-oriented application development methodologies. He also possesses a thorough understanding of the software life cycle (design, development, and testing). He holds several certifications, including MCAD for .NET, MCSD, and MCP. Thiru is an expert with ASP.NET, .NET Framework, Visual C# .NET, Visual Basic .NET, ADO.NET, XML Web services, and .NET Remoting. Thiru also has authored numerous books and articles. Contact him at thiruthangarathinam@yahoo.com.