Working on Transaction in a Web Service

Introduction:

In this article, I am performing a simple transaction to describe how to work with transactions in a web service application. I am using the SqlTransaction class in a  web service application using C#. The SqlTransaction class is used for satisfying the ACID property of DBMS. It ensures that a body of code will affect a Database or keep the same as previous (Rollback). First we should know about it's two most important methods which will be used here. They are given below.

• Commit(): It commits the transaction. It save changes made in Database during transaction. In simple term we can say also that it shows the end of transaction at that time.
• Rollback(): It is used to rollback the transaction. It set the Database in previous stage which was, before the begin of transaction. 

Now we create a simple web service application to understand how it works. At first we create a Database as "transaction". We create two database tables as userdet and moneytrans. Insert some records in userdet table. Below, I am giving a screen shot of the output of a select command for showing the records of the tables.

select * from userdet
select * from moneytrans

Now create the ASP.NET Web Service Application and replace the given code with the  following code.
 
using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Web;
 using System.Web.Services;
 using System.Data.SqlClient;
 using System.Data;
  
 namespace sqltransaction
 {
     /// <summary>
     /// Summary description for TransactionWebService
     /// </summary>
     [WebService(Namespace = "example.org")]
     [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
     [System.ComponentModel.ToolboxItem(false)]
     // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
     // [System.Web.Script.Services.ScriptService]
     public class TransactionWebService : System.Web.Services.WebService
     {
         SqlConnection conn;
         SqlCommand comm1, comm2, comm3;
         SqlTransaction trans;
         SqlDataAdapter adapter1, adapter2;
         DataSet ds1, ds2;
         string constring = "Database=transaction;server=.;user=aa;password=aaaa";
         [WebMethod(Description="Transaction")]
         public string transaction(int userid,int amount)
         {
             conn = new SqlConnection(constring);
             conn.Open();
             comm2 = new SqlCommand("insert into moneytrans values(" + userid + "," + amount + ")", conn);
             comm3 = new SqlCommand("update userdet set amount=amount-'" + amount + "' where userid="+ userid +" ", conn);
             trans = conn.BeginTransaction();
             comm2.Transaction = trans;
             comm3.Transaction = trans;
                 try
                 {
                 comm2.ExecuteNonQuery();
                 comm3.ExecuteNonQuery();
                 trans.Commit();
                         return "Transaction Complted. ";
                     }
                     catch (Exception)
                     {
                         trans.Rollback();
                        return "Transaction Failed..";
                     }
             finally
                     {
                         conn.Close();
                     }
                 }
         [WebMethod(Description = "Show user detail")]
         public DataSet showuserdet()
         {
             adapter1 = new SqlDataAdapter("select * from userdet", constring);
             ds1 = new DataSet();
             adapter1.Fill(ds1);
             return ds1;
         }
         [WebMethod(Description = "Show transaction detail")]
         public DataSet showtransactionreport()
         {
             adapter2 = new SqlDataAdapter("select * from moneytrans", constring);
             ds2 = new DataSet();
             adapter2.Fill(ds2);
             return ds2;
         }
        
     }
 }
 

Now, run the service application.

Output:



This web service shows a simple transaction operation. There is "userdet" table which has userid, username and amount columns. The moneytrans table has record for all transaction ( say debit details). You can perform different operation on test page. Here I am taking ASP.NET Web Application to consume this service. So, let's create a web application.
Take a web application -> consume the web service( You can get help, here) and arrange UI controls same as given in below figure.



Write the following code on the .aspx.cs page.

using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Web;
 using System.Web.UI;
 using System.Web.UI.WebControls;
 
 namespace MyWebApplication
 {
     public partial class WebForm1 : System.Web.UI.Page
     {
         protected void Page_Load(object sender, EventArgs e)
         {
  
         }
     
         protected void btnshowuserdet_Click(object sender, EventArgs e)
         {
             localhost.TransactionWebService obj=new localhost.TransactionWebService();
             GridView1.DataSource = obj.showuserdet().Tables[0];
             GridView1.DataBind();
         }
  
         protected void btntrandet_Click(object sender, EventArgs e)
         {
             localhost.TransactionWebService obj = new localhost.TransactionWebService();
             GridView2.DataSource = obj.showtransactionreport().Tables[0];
             GridView2.DataBind();
         }
  
         protected void btnok_Click(object sender, EventArgs e)
         {
             localhost.TransactionWebService obj = new localhost.TransactionWebService();
             lblmsg.Text = obj.transaction(Convert.ToInt32(txtuserid.Text), Convert.ToInt32(txtamount.Text));
         }
     }
 }

Now the web application.

Output:



Click the "User Detail" button. It will show the record from "userdet" table.

Output:



You can click "Transaction Detail" button to show all transaction records. At beginning, it will not show any records because it has no record. Write the user ID and amount and click the "ok" button. It will show message as "Transaction Complted" on right entry and "Transaction Failed.." on wrong entry.

At shown in above figure I have entered 1 for user ID and 2000 for amount. After successful transaction, a message is shown to show completion of transaction. Now click at "Transaction Detail" button to show all transaction records.
 


Click the "ok" button.

 Now click at "Transaction Detail" button to show records from "moneytrans" table.




Click at "User Detail" button to show record after transaction.




If user do wrong entry then transaction will not be made. Suppose "userid" 10 is not available in Database. If a user enter user ID which is not available in database or wrong format of amount (which sholud be in number), then transaction will be failed.

Click "ok" button. ( There is no any user with "user ID" 10.)