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.
![transaction in web service]()
Now click at "Transaction Detail" button to show records from "moneytrans"
table.
![transaction in web service]()
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.
![transaction in web service]()
Click "ok" button. ( There is no any user with "user ID" 10.)
![]()