SQL Transactions in .net

SqlTransaction is used in cases we are updating Multiple tables at a Time. Here the error occurs such as first table gets updated and the next table which have a relation with the first table does not get Updated due to some Errors may be some Power Failure etc.... So to overcome these types of errors we can use SqlTransaction in the programs.....

Here is an example for the SqlTransaction


public partial class Form1 : Form
{
SqlConnection con = new SqlConnection(@"Integrated Security=SSPI;Initial Catalog=NationalBank;Data Source=.\SQLEXPRESS");
SqlCommand cmd;


Here you are going to make an Object for SqlTransaction named sqtr

SqlTransaction sqtr;

public Form1()
{
InitializeComponent();
con.Open();
}

private void button1_Click(object sender, EventArgs e)
{
The below step starts the transaction
sqtr = con.BeginTransaction();

try
{
eNameInsert(txtName.Text);
eAddressInsert(txtPassword.Text);
If the transaction completes successfully the commit method is called.....
sqtr.Commit();
}
catch (Exception ex)
{
The rollback method of the SqlTransaction Undos all the things which have been updated if an Error occurs
sqtr.Rollback();
MessageBox.Show(ex.Message);
}
}
static int i;
private void eNameInsert(string Name)
{


try
{

We must mention the SqlTransaction object name after the con with each SqlCommand
cmd = new SqlCommand("Insert into eName values(@eName)", con,sqtr);
cmd.Parameters.AddWithValue("@eName", Name);
cmd.ExecuteNonQuery();

cmd = new SqlCommand("Select EId from eName where EName='" + Name + "'", con,sqtr);
i = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (SqlException ex)
{
sqtr.Rollback();
MessageBox.Show(ex.Message);
}
}
private void eAddressInsert(string Address)
{

try
{
cmd = new SqlCommand("Insert into eAddress values(@id,@eAddress)", con,sqtr);
cmd.Parameters.AddWithValue("@eAddress", Address);
cmd.Parameters.AddWithValue("@id", i);
cmd.ExecuteNonQuery();
sqtr.Commit();
}
catch (SqlException ex)
{
sqtr.Rollback();
MessageBox.Show(ex.Message);
}
}