July 25, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Develop Transactional .NET Web Services

  • July 26, 2004
  • By Thiru Thangarathinam
  • Send Email »
  • More Articles »

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.



Click here for a larger image.

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




Page 2 of 4



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel