Friday, September 23, 2011

Distributed Transaction in ASP.NET with SQL-Server

Posted by Rahul Kharde at 4:15 AM

A distributed transaction is a transaction that updates data on two or more networked computer systems. Distributed transactions extend the benefits of transactions to applications that must update distributed data. Implementing robust distributed applications is difficult because these applications are subject to multiple failures, including failure of the client, the server, and the network connection between the client and server. In the absence of distributed transactions, the application program itself must detect and recover from these failures.

  • A Distributed Transaction spans multiple data sources.
  • Distributed Transactions enable you to incorporate several distinct operations, which occur on different systems, into an atomic action that either succeeds or fails completely.
  • Distributed Transactions are managed by DTC, i.e., Distributed Transaction Coordinator.

On the server where the trigger resides, you need to turn the MSDTC service on.

You can this by clicking

1. START -> CONTROL PANEL -> ADMINISTRATIVE TOOLS -> SERVICES.

2. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start.

We will use, of course, two SQL Server 2000/2005 databases. The first one, named Bank1, will be on the SQL Server "DB_Bank1", and it will contain an Accounts table; this table holds Bank1's accounts. The second database will be Bank2 (on the server " DB_Bank2") and it will contain another Accounts table

For Fist Server (DB_Bank1) create table Bank1

CREATE TABLE [dbo].[Bank1](

[AccID] [int] NOT NULL,

[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Amt] [money] NULL,

CONSTRAINT [PK_Bank1] PRIMARY KEY CLUSTERED

(

[AccID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]



INSERT INTO Bank1(AccID,Name,Amt)VALUES(1,'UserName1','8000')

INSERT INTO Bank1(AccID,Name,Amt)VALUES(2,'UserName2','7500')

INSERT INTO Bank1(AccID,Name,Amt)VALUES(3,'UserName3','10000')

INSERT INTO Bank1(AccID,Name,Amt)VALUES(4,'UserName4','15700')


AccID

Name

Amt

1

UserName1

8000.00

2

UserName2

7500.00

3

UserName3

10000.00

4

UserName4

15700.00

In Second Server(DB_Bank2)create Table Bank2

CREATE TABLE [dbo].[Bank2](

[AccID] [int] NOT NULL,

[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Amt] [money] NULL,

CONSTRAINT [PK_Bank2] PRIMARY KEY CLUSTERED

(

[AccID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]


INSERT INTO Bank2(AccID,Name,Amt)VALUES(1,'UserName1','4580')

INSERT INTO Bank2(AccID,Name,Amt)VALUES(2,'UserName2','9650')

INSERT INTO Bank2(AccID,Name,Amt)VALUES(3,'UserName3','14750')


AccID

Name

Amt

1

UserName1

4580.00

2

UserName2

9650.00

3

UserName3

14750.00


HTML

Here we take two dropdown list box which contain the Account No. source account id hold the Fist Server account Holder ID and destination account no hold the second server account holder Id. Grid shows the record of respective banks

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title>Untitled Pagetitle>

</head>

<body>

<form id="form1" runat="server">

<div>

<table style="width: 60%; font-family: Verdana; font-size: 10px;">

<tr>

<td style="text-align: right;">

Source Account ID

</td>

<td>

<asp:DropDownList ID="DropDownList1" runat="server" Width="79px">

<asp:ListItem>1asp:ListItem>

<asp:ListItem>2asp:ListItem>

<asp:ListItem>3asp:ListItem>

<asp:ListItem>4asp:ListItem>

</asp:DropDownList>

</td>

<td style="text-align: right;">

Destination Account ID

</td>

<td>

<asp:DropDownList ID="DropDownList2" runat="server" Width="79px">

<asp:ListItem>1asp:ListItem>

<asp:ListItem>2asp:ListItem>

<asp:ListItem>3asp:ListItem>

</asp:DropDownList>

</td>

</tr>

<tr>

<td>

</td>

<td colspan="2" style="text-align: center;">

Amount:

<asp:TextBox ID="TextBox3" runat="server">asp:TextBox>

</td>

<td>

</td>

</tr>

<tr>

<td style="text-align: center;" colspan="4">

<asp:Button ID="Button1" runat="server" Text="Submit" OnClick="Button1_Click" />

</td>

</tr>

<tr>

<td colspan="2" style="text-align: center;">

<b>Bank 1b>

</td>

<td colspan="2" style="text-align: center;">

<b>Bank 2b>

</td>

</tr>

<tr>

<td colspan="2" style="text-align: center; vertical-align: top;">

<asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#DEDFDE"

BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical">

<FooterStyle BackColor="#CCCC99" />

<RowStyle BackColor="#F7F7DE" />

<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />

<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />

<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />

<AlternatingRowStyle BackColor="White" />

</tasp:GridView>

</td>

<td colspan="2" style="text-align: center; vertical-align: top;">

<asp:GridView ID="GridView2" runat="server" BackColor="White" BorderColor="#DEDFDE"

BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical">

<FooterStyle BackColor="#CCCC99" />

<RowStyle BackColor="#F7F7DE" />

<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />

<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />

<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />

<AlternatingRowStyle BackColor="White" />

</asp:GridView>

</td>

</tr>

</table>

</div>

</form>

</body>

</html>



Code

Add the “System.Transactions” reference from “Add reference”

using System.Transactions;

using System.Data.SqlClient;

Declare global Connection string

private static string strConn1;

private static string strConn2;

page load

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

strConn1 = @"Data Source = ServerAddress1;User id = username;pwd = Password;Initial Catalog = DB_Bank1";

strConn2 = @"Data Source = ServerAddress2;User id = username;pwd = Password;Initial Catalog = DB_Bank2";

GridView1.DataSource = (DataSet)this.LoadGrid(strConn1, "Select * from Bank1");

GridView1.DataBind();

GridView2.DataSource = (DataSet)this.LoadGrid(strConn2, "Select * from Bank2");

GridView2.DataBind();

}

}

public DataSet LoadGrid(String StrConn, String SQL)

{

DataSet ds = new DataSet();

try

{

using (SqlConnection Con = new SqlConnection(StrConn))

{

SqlCommand Cmd = new SqlCommand(SQL, Con);

SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = Cmd;

da.Fill(ds);

return ds;

}

}

catch (Exception ex)

{

}

return ds;

}

Button click event

protected void Button1_Click(object sender, EventArgs e)

{

try

{

using (TransactionScope scope = new TransactionScope())

{

SqlConnection cnn1 = new SqlConnection(strConn1);

SqlConnection cnn2 = new SqlConnection(strConn2);

cnn1.Open();

cnn2.Open();

SqlCommand cmd1 = new SqlCommand("update Bank1 set amt=amt-" + Convert.ToDouble(TextBox3.Text) + " where AccID='" + Convert.ToInt32(DropDownList1.SelectedItem.Value) + "'", cnn1);

SqlCommand cmd2 = new SqlCommand("update Bank2 set amt=amt+" + Convert.ToDouble(TextBox3.Text) + " where AccID='" + Convert.ToInt32(DropDownList2.SelectedItem.Value) + "'", cnn2);

cmd1.ExecuteNonQuery();

cmd2.ExecuteNonQuery();

cnn1.Close();

cnn2.Close();


GridView1.DataSource = (DataSet)this.LoadGrid(strConn1, "Select * from Bank1");

GridView1.DataBind();

GridView2.DataSource = (DataSet)this.LoadGrid(strConn2, "Select * from Bank2");

GridView2.DataBind();

scope.Complete();

}

}

catch(Exception ex)

{

}

}



Download

Download Code Here..



If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top