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

}
}