Transaction :-

Transaction is a sequence of operation performed as a single logical unit of work. a logical unit if work must exhibit four properties called the ACID properties(Atomicity,Consistency,Isolation and Durability) to qualify as a transaction.

    Atomic – All statements in a group must execute, or no statement in a group must execute.
    Consistent – This follows naturally from atomic – a group of SQL statements must take the database from a known starting state to a known ending state. If the statements execute, the database must be at the known ending state. If the statements fail, the database must be at the known starting state.
    Isolated – A group of statements must execute independently from any other statement groups being executed at the same time. If this wasn’t the case, it would be impossible for statement groups to be consistent – the known ending state could be altered by a code you have no control over or knowledge of. This is one of those concepts that are great in theory, but total isolation has important performance implications in the real world. More on how SQL Server implements this is explained later.
    Durable – Once the group of SQL statements execute, the results need to be stored in a permanent media – if the database crashes right after a group of SQL statements execute, it should be possible to restore the database state to the point after the last transaction committed.

Frequently in a database application you come across a situation where you need to execute two or more SQL commands in such a way that if any one of the statements fails, then no other statement will be able to change the database. The classic example of this is transferring money from a bank account into another bank account:

UPDATE Accounts SET Balance = Balance – 10 WHERE Customer = 1;
UPDATE Accounts SET Balance = Balance + 10 WHERE Customer = 2;

If the first SQL statement was to execute and the second SQL statement was to fail, then ten dollars would be removed from the first customer’s account but will never be added to the second customer’s account. This would, naturally, be bad – ten dollars has just disappeared entirely.

SQL TRANSACTION:->

SQL allow a developer to work with transaction with two simple statement:

¤ Begin Transaction
¤ Commit Transaction

ADO.NET TRANSACTION:->

       con.Open();
        SqlTransaction trans;
        trans = con.BeginTransaction();
        try
        {
            cmd = new SqlCommand("sp_Test", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id", SqlDbType.Int).Value = txtID.Text;
            cmd.Parameters.AddWithValue("@name", SqlDbType.VarChar).Value = txtName.Text.ToString();
            cmd.Transaction = trans;
            cmd.ExecuteNonQuery();
            trans.Commit();
        }
        catch
        {
            trans.Rollback();
            Label1.Text = "Insert Fail";
        }
        con.Close(); con.Dispose();