- Here's the Scenario
- Atomicity, Consistency, Isolation, and Durability (ACID)
- Automatic Web Service Transactions Are Easy
- A Web Service That Uses An Automatic Transaction
- Using the Web Service from an ASP.NET Page
- Summary
A Web Service That Uses An Automatic Transaction
Let's create a web service that uses an automatic transaction. The web service controls a bank transaction, either a deposit or a withdrawal. The transaction is recorded in a SQL Server database. If all goes well, the web method finishes successfully, the execution thread is back in the calling method without an exception being thrown, and the transaction remains in the database. If anything goes wrong, though, the transaction won't be committed, and the transaction record won't be in the database.
Step 1: Create the Project
With Visual Studio .NET, create a new project. Select an ASP.NET web service for either a VB or a C# project. In Figure 1, a C# web service is being created.
Figure 1 Creating a C# ASP.NET web service.
Step 2: Rename the Service and Class
Once the web service has been created, I always rename Service1.asmx to something more meaningful, and then open the source file and rename the class something other than Service1. (Visual Studio .NET names web services Service1 by default when they're created.)
For the example that I created in the preceding section, I changed the name of Service1.asmx to BankTransaction.asmx. I then opened the BankTransaction.asmx file and renamed the class from Service1 to BankTrans. There are two edits you must make in C# (only one in VB): the name of the class and the name of the constructor. The following examples show the top of the .asmx source file, and where the edits should come. The changes are marked in bold blue text.
In C#, change this:
using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Diagnostics; using System.Web; using System.Web.Services; namespace BP { /// <summary> /// Summary description for Service1. /// </summary> public class Service1 : System.Web.Services.WebService { public Service1() { //CODEGEN: This call is required by the ASP.NET Web Services Designer InitializeComponent(); }
to this (replacing MyName with whatever your class is named):
using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Diagnostics; using System.Web; using System.Web.Services; namespace BP { /// <summary> /// Summary description for Service1. /// </summary> public class MyName : System.Web.Services.WebService { public MyName() { //CODEGEN: This call is required by the ASP.NET Web Services Designer InitializeComponent(); }
In VB, change this:
Imports System.Web.Services <WebService(Namespace := "http://tempuri.org/")> _ Public Class Service1 Inherits System.Web.Services.WebService
to this (replacing MyName with whatever your class is named):
Imports System.Web.Services <WebService(Namespace := "http://tempuri.org/")> _ Public Class MyName Inherits System.Web.Services.WebService
Step 3: Preparing to Add the Code
There are some things that we need to do before we can add any code. Two using statements (for C#) or two import statements (for VB) need to be added:
System.Data.SqlClient is needed for the SQL Server consumer namespace.
System.EnterpriseServices is needed for the transaction support.
System.EnterpriseServices isn't available unless you add a reference in the Visual Studio .NET Solution Explorer window. To do this, right-click the References folder and select Add Reference from the menu. In the dialog box that appears, select System.EnterpriseServices (see Figure 2).
Figure 2 You must add the System.EnterpriseServices namespace.
Step 4: Add the Method
The last thing to do is to add the method into the .asmx file. The method that I added, Transact, is shown in Listings 12.
Listing 1The Transact() Method (C#)
[WebMethod(TransactionOption=TransactionOption.RequiresNew)] public void Transact( int nType, double dBalance, double dAmount, string strAccount, string strPIN ) { // Create the connection to the database and // open it. SqlConnection objConnection = new SqlConnection( "server=localhost;uid=sa;pwd=;database=WebServiceTransactions" ); objConnection.Open(); // Type=1 means we're adding to the balance. if( nType == 1 ) { dBalance += dAmount; } // Otherwise we're subtracting from the balance. else { dBalance -= dAmount; } // But we always subtract a 15 cent transaction fee. dBalance -= 0.15; // Create the SQL statement. It's a simple INSERT. // We use the string.Format() method to avoid // causing the CLR to create a bunch of temp objects. string strSql = string.Format( "Insert Into Transactions " + "(Account,Balance,Amount) VALUES ('{0}',{1},{2})", strAccount, dBalance, dAmount ); // Create the command object and execute the SQL. SqlCommand objCommand = new SqlCommand( strSql, objConnection ); objCommand.ExecuteNonQuery(); // See if the PIN is four characters long. if( strPIN.Length != 4 ) { // If it is not, throw an exception. throw new Exception( "Invalid PIN" ); } }
Listing 2The Transact() Method (VB)
<WebMethod(TransactionOption:=TransactionOption.RequiresNew)> _ Public Function Transact(ByVal nType As Integer, _ ByVal dBalance As Double, ByVal dAmount As Double, _ ByVal strAccount As String, ByVal strPIN As String) ' Create the connection to the database and ' open it. Dim objConnection As _ New SqlConnection(_ "server=localhost;uid=sa;pwd=;database=WebServiceTransactions") objConnection.Open() ' Type=1 means we're adding to the balance. If nType = 1 Then dBalance += dAmount Else ' Otherwise we're subtracting from the balance. dBalance -= dAmount End If ' But we always subtract a 15 cent transaction fee. dBalance -= 0.15 ' Create the SQL statement. It's a simple INSERT. ' We use the string.Format() method to avoid ' causing the CLR to create a bunch of temp objects. Dim strSql as String = string.Format( _ "Insert Into Transactions " + _ "(Account,Balance,Amount) VALUES ('{0}',{1},{2})", _ strAccount, dBalance, dAmount ) ' Create the command object and execute the SQL. Dim objCommand As _ New SqlCommand(strSql, objConnection) objCommand.ExecuteNonQuery() ' See if the PIN is four characters long. If strPIN.Length <> 4 Then ' If it is not, throw an exception. Throw New Exception("Invalid PIN") End If End Function
Let's take a look at the Transact() method's code. The first thing it does is create a SqlConnection object, with the connection string as the only argument to the constructor.
NOTE
The SQL Server password on my server is not blank, but I removed the password to avoid security risks. A worm went around several months back, known as DispId. It attacked SQL Server for the user named sa if the password was blank.
I also heard Rob Howard mention that when Microsoft first released their Forum application's source code, it had the connection string in all of its glory, including the password.
Once the SqlConnection object has been created, the Open() method is called. If the connection string is somehow incorrect, an exception will be thrown to the calling method.
If the nType variable contains a zero, this is a deposit, and dAmount is added to dBalance. If nType is nonzero, it's a withdrawal, and dAmount is subtracted from dBalance. Notice that the parameters and results aren't checked for validity. A constraint on the balance field in the database will cause an exception to be thrown if the balance is less than zero.
A value of 15 cents is always subtracted from the balance. This is a per-transaction fee.
The SQL string we'll use is a simple insert statement. If the account number is 12345, the new balance is $54.00, and the amount that was added was $12.00, the statement will be as follows:
Insert Into Transactions (Account,Balance,Amount) VALUES ('12345',54.00,12.00)
TIP
You can create the same string by concatenating strings with plus (+) symbols as follows:
C#:
strSql = "Insert Into Transactions (Account,Balance,Amount) VALUES ('" + strAccount + "'," + Convert.ToString( dBalance ) + "," + Convert.ToString( dAmount ) + ")";
VB:
strSql = "Insert Into Transactions (Account,Balance,Amount) VALUES ('" + _ strAccount + "'," + _ Convert.ToString( dBalance ) + "," + Convert.ToString( dAmount ) + ")"
But the Common Language Runtime (CLR) creates a number of temporary strings in the background and therefore does more work (not to mention the extra memory requirements). For this reason, the string.Format() method is preferred, since it doesn't create any of these temporary strings.
Once the SQL string is created, a SqlCommand object is created. The two parameters to the SqlCommand object's constructor are the SQL string and the SqlConnection object. With the SqlCommand object created, a call to its ExecuteNonQuery() method performs the SQL and inserts the record into the database.
The last thing that the Transact() method does is check the PIN to make sure it's four characters long. Ordinarily, the PIN would be compared to something in the database, but this is a simple demo program. If the PIN is not four characters long, an exception is thrown, thus aborting the transaction.
NOTE
This web service is available for you to use right now. The URL is http://www.aspnet-solutions.com/BankTransaction/BankTransaction.asmx.