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
Comments
Post a Comment