Transaction

A transaction is a sequence of operations performed as single logical unit or work. Means all the statement written within Transaction block should execute successfully or none.

A transaction must follow ACID rule.

Automoicity :- A transaction must be Atomic. Either all of its data modification should performed or none.

Consistency :- A transaction must leave a database into consistent state whether it complete successfully or not. It should not be the case that we have transferred 1000 Rs. from account A to account B. A has credited 1000 but B has not due to electricity failure or any other reason.

Isolation :- A transaction will never affect another transaction running at same time. Means a transaction should not find the database in intermediate state because of another transaction. It should find it before the other transaction completion or after the completion.

Durability :- After the completion of transaction its effect should be permanent. Even in case of System Failure or database crash. Transaction log are maintained so that the database can be restored in its original position before failure takes place.

You can do it in .Net and SQL both.
There are three main function in transaction BeginTransaction, Rollback and Commit

string connectionString = ".........";
SqlConnection objConnection = new SqlConnection(connectionString);
objConnection.Open();

// Start transaction.
SqlTransaction objTransaction = objConnection.BeginTransaction();
// Assign command in the current transaction.

SqlCommand objCommand = new SqlCommand();
myCommand.Transaction = objTransaction;
try
{
//.........................Begin Database operations........................//

//.........................End Database operations........................//

objTransaction.Commit();
Console.WriteLine("Records are modified in the database.");
}
catch(Exception e)
{

objTransaction.Rollback();

Console.WriteLine(e.ToString());

Console.WriteLine("Neither record was written to database.");
}
finally
{
objConnection.Close();
}

In Sql Server we can do it like this

CREATE PROCEDURE DeleteDepartment
(
@DepartmentID int
)
AS

-- This sproc performs deletes all of the department's associated employees.

-- STEP 1: Start the transaction
BEGIN TRANSACTION

-- STEP 2 & 3: Issue the DELETE statements, checking @@ERROR after each statement
DELETE FROM Employees
WHERE DepartmentID = @DepartmentID

-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error in deleting employees in DeleteDepartment.', 16, 1)
RETURN
END

-- Commit the transaction....
COMMIT