For the purposes of this example, we will create a new Visual C# Class Library project named TransactionDemoComponent. In this project, we will add three classes named Dept, Employee, and TransactionalClass. To the Dept and Employee classes, we will add methods to add a new employee and a dept respectively. From the TransactionalClass, we will invoke the methods in these classes to add a new dept and an employee. Since we want both of these operations to be running as part of a transaction, we will use the ServiceConfig object in the TransactionalClass to use the distributed transactions capability of COM+ services.
After creating the project, we will rename the default class to Dept and add the following lines of code to the Dept class.
public class Dept
{
public
Dept()
{
}
public int
AddDept(string deptName, string deptDescription)
{
int
deptNo;
string
connString =
"server=localhost;uid=sa;pwd=thiru;database=15Seconds;";
string
commandName = "AddDept";
using
(SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand
command = new SqlCommand(commandName,conn);
//Set the Command Type to Stored Procedure
command.CommandType
= CommandType.StoredProcedure;
//Create the DeptName parameter and set its properties
SqlParameter
paramDeptName = new
SqlParameter("@DeptName",
SqlDbType.VarChar,50);
paramDeptName.Direction
= ParameterDirection.Input;
paramDeptName.Value
= deptName;
command.Parameters.Add(paramDeptName);
//Create the DeptDescription parameter and set its value
SqlParameter
paramDeptDesc = new
SqlParameter("@DeptDescription",SqlDbType.VarChar,250);
paramDeptDesc.Direction
= ParameterDirection.Input;
paramDeptDesc.Value
= deptDescription;
command.Parameters.Add(paramDeptDesc);
//Create the Return parameter and set its properties
SqlParameter
paramReturnValue = new
SqlParameter("@@identity",SqlDbType.VarChar,250);
paramReturnValue.Direction =
ParameterDirection.ReturnValue;
command.Parameters.Add(paramReturnValue);
//Execute the Stored Procedure
command.ExecuteNonQuery();
//Get the identity value from the stored procedure
deptNo =
(int)command.Parameters["@@identity"].Value;
}
return
deptNo;
}
}
As you can see from the above code, the Dept class contains only one method named AddDept, which executes a stored procedure named AddDept to add a dept to the database. After inserting the dept details into the database, the stored procedure returns the identity value of the newly inserted dept ID to the caller. We capture that returned ID by using the Parameters collection of the SqlCommand object, and we do this after executing the stored procedure by means of an ExecuteNonQuery method call. The Stored procedure AddDept is defined as follows.
CREATE proc AddDept
@DeptName varchar(50) ,
@DeptDescription varchar(250)
as
set nocount on
insert into Dept(DeptName, DeptDescription) Values(@DeptName, @DeptDescription)
return @@identity
GO
Now that we have created the Dept class, let us take a look at the code of the Employee class.
public class Employee
{
public Employee()
{
}
public int
AddEmployee(string empName, int deptNo)
{
string
connString =
"server=localhost;uid=sa;pwd=thiru;database=15Seconds;";
string
commandName = "AddEmployee";
int
empNo;
using
(SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand
command = new SqlCommand(commandName,conn);
//Set the Command Type to Stored Procedure
command.CommandType
= CommandType.StoredProcedure;
//Create the EmpName Parameter
SqlParameter
paramEmpName = new
SqlParameter("@EmpName",
SqlDbType.VarChar,50);
paramEmpName.Direction = ParameterDirection.Input;
paramEmpName.Value
= empName;
command.Parameters.Add(paramEmpName);
//Create the DeptNo Parameter
SqlParameter
paramDeptNo = new
SqlParameter("@DeptNo",SqlDbType.Int);
paramDeptNo.Direction
= ParameterDirection.Input;
paramDeptNo.Value
= deptNo;
command.Parameters.Add(paramDeptNo); //Create the Return parameter and set its properties
SqlParameter
paramReturnValue = new
SqlParameter("@@identity",SqlDbType.VarChar,250);
paramReturnValue.Direction
=
ParameterDirection.ReturnValue;
command.Parameters.Add(paramReturnValue);
//Execute the Stored Procedure
command.ExecuteNonQuery();
//Get the identity value from the stored procedure
empNo =
(int)command.Parameters["@@identity"].Value;
}
return
empNo;
}
}
As you can see from the above code, the Employee class is very similar to the Dept class in that it also contains only one method, named AddEmployee, that simply adds the details of an employee to the database. Similar to the AddDept method, the AddEmployee method also returns the ID of the newly added employee to the caller.
Implementation of the TransactionalClass
Now that we have developed the Employee and Dept classes, let us turn our focus to the TransactionalClass, which is responsible for ensuring that the details of a dept and an employee are added as part of a single transaction.
public class TransactionalClass
{
public TransactionalClass()
{
}
public bool
AddEmployeeDept(string deptName, string deptDescription,
string empName)
{
ServiceConfig
config = new ServiceConfig();
//Set the properties of ServiceConfig object
config.TrackingEnabled
= true;
config.TrackingAppName
= "SimpleComponent Application";
config.TrackingComponentName
= "SimpleComponent";
//Set
the TransactionOption to Required to indicate that a new //Transaction Context
needs to be created
config.Transaction
= TransactionOption.Required; //Enter inside the Service Domain to use COM+ Services
ServiceDomain.Enter(config);
Dept
department = new Dept();
int deptNo = department.AddDept(deptName, deptDescription);
Employee
emp = new Employee();
emp.AddEmployee(empName,
deptNo);
ServiceDomain.Leave();
return true;
}
}
The AddEmployeeDept method starts by creating an instance of the ServiceConfig class. After that, it assigns values to properties such as TrackingEnabled, TrackingAppName, and TrackingComponentName. As we already mentioned, these properties are used to determine if and how the tracking information about the component is reported. Then we set the Transaction property to TransactionOption.Required. This ensures that the object always runs within the scope of a transaction. Once we assign appropriate values to the ServiceConfig object, we are ready to enter inside the context. This is accomplished using the ServiceDomain.Enter method. Once we are within the context of a service domain, we can now invoke the AddDept, and AddEmployee methods through their corresponding objects. Since we have already set the Transaction property to TransactionOption.Required, the code that is enclosed within Enter and Leave methods will always run in a transaction. This means that if there is an exception during the execution of any of the methods, the transaction will be rolled back, ensuring the consistency of the data.
Implementation of the Client Application
To test the Transactional component, let us create a simple Visual C# Windows application named TransactionDemoComponentClient. Once the project is created, we will reference the TransactionDemoComponent using the Project->Add Reference menu. After that, add a command button named btnInvoke to the form and modify the Click event of the command button to look like the following.
private void btnInvoke_Click(object sender, System.EventArgs e)
{
TransactionalClass trans = new TransactionalClass();
MessageBox.Show(trans.AddEmployeeDept("Production Dept",
"Production Dept Descr","Thiru").ToString());
}
If the AddEmployeeDept method executes successfully, you will see a Boolean true value in the message box. If there is an exception during the execution of the AddEmployeeDept method, no information will be added to the database.
