SQL Transactions in ASP.NET
Working with transactions concept with the help of banking transcations:
1)Transcation is a concept of executing more than one inter-related DML statements at one shot
2)Transcation contains a set of DML statements
3)when ever all DML statements of a transcation are executed successfully then data need to be committed(saved) or else data need to be rolled back(don’t save)
4)To work with transcations, .Net introduced oledbTranscation(sqltranascation) class
5)Tranasction need to be initiated with the help of begin transcations() of connection class
Example on banking transactions with sql server
Open sql server software
Select DB244 database
Then create a table as follows
Create table GTB(accno int,bal int)
Insert into GTB values(101, 50000);
Insert into GTB values(102, 10000);
Select * from GTB
Open windows forms app project
Start->programs->Microsoft visual studio 2010->Microsoft Visual studio 2010->file menu->new->
project->select visual c# from installed templates->select windows forms application project
design the form
From Ac
To Ac
Amount
|
Button1 |
Using System.Data.sqlclient; namespace
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsFormsApplication25
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string s1 = "update GTB set bal=bal-" + textBox3.Text + "where acno=" + textBox1.Text;
string s2 = "update GTB set bal=bal+" + textBox3.Text + "where accno=" + textBox2.Text;
SqlConnection cn = new SqlConnection("user id=sa;password=123;database=DB244");
cn.Open();
SqlTransaction tr = cn.BeginTransaction();
SqlCommand c1 = new SqlCommand(s1, cn, tr);
int a = c1.ExecuteNonQuery();
SqlCommand c2 = new SqlCommand(s2, cn, tr);
int b = c2.ExecuteNonQuery();
if (a == 0 || b == 0)
{
tr.Rollback();
MessageBox.Show("transaction is cancelled");
}
else
{
tr.Commit();
MessageBox.Show("transaction is successful");
}
}
}
}
execute the project F5
