Transaction in C#

This article describes how to use transaction in C#? Summary of the article:

• What is a Transactions?
• Why we should use Transactions?
• Adding a Connection String
• Namespaces for  Transactions
• Transactions in ADO.NET C#

What is a Transactions?
A transaction groups a set of task into a single execution unit. Every transaction begins with a specific task and ends when all the tasks in the group successfully complete. If the execution of any of the tasks is fails, the transaction is fails. Therefore, we can say a transaction has only two results: success or failure. Any incomplete steps result in the failure of the total transaction. We can use transactions in SQL.

Why we should use Transactions?
In our application sometimes we need to execute two or more SQL statements in such a way that if any one of the statements fails, then other statements will not make any change the database. We can handle this type of situation by using Transaction.

Adding a Connection String
we need to add or create a connection string in Web.config file.Add the following element to the <connectionStrings> element in Web.config file:

 <add name="ConStr" connectionString="Data Source=localhost;Initial Catalog=TestDB;Persist Security Info=True;User ID=sa;Password=sa123"
        providerName="System.Data.SqlClient"/>

Namespaces for Transactions
For Transactions we may need to include some namespaces.Please add the following namespaces:

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Data.SqlTypes;

Transactions in ADO.NET C#
Write the following C# codes:

SqlConnection oSqlConnection;
SqlTransaction oSqlTransaction;
string ConnectionString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
string Sql = "INSERT INTO Students (StudentName, Address, Phone) VALUES (‘Jon Smith’, ‘London’, ’44024876′)";
            
oSqlConnection = GetDBConnection(ConnectionString);
oSqlTransaction = BeginTransaction(oSqlConnection);
try
{
    InsertData(Sql, oSqlConnection, oSqlTransaction);
    TransactionCommit(oSqlTransaction);
}
catch (Exception oException)
{
    TransactionRollback(oSqlTransaction, oException);
}
finally
{
    Finally(oSqlConnection);
}

Related Methods
Write the following methods:

public void InsertData(String Sql, SqlConnection oSqlConnection, SqlTransaction oSqlTransaction)
{        
    SqlCommand oSqlCommand = new SqlCommand(Sql, oSqlConnection, oSqlTransaction);
    try
    {            
        oSqlCommand.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message.ToString());
    }
}
    
           
public SqlConnection GetDBConnection(string ConnectionString)
{
    SqlConnection oSqlConnection = new SqlConnection(ConnectionString);
    try
    {
        if (oSqlConnection.State.ToString() == "Open")
        {
            oSqlConnection.Close();
        }
        oSqlConnection.Open();
    }
    catch (Exception ex)
    {            
        throw new Exception(ex.Message.ToString());
    }
    return oSqlConnection;
}


public static SqlTransaction BeginTransaction(SqlConnection oSqlConnection)
{
    return oSqlConnection.BeginTransaction();
}


public void TransactionCommit(SqlTransaction oSqlTransaction)
{
    oSqlTransaction.Commit();
}


public  void TransactionRollback(SqlTransaction oSqlTransaction, Exception oException)
{
    oSqlTransaction.Rollback();
    throw new Exception(oException.Message);
}


public  void Finally(SqlConnection oSqlConnection)
{        
    oSqlConnection.Close();
    oSqlConnection.Dispose();
}

That’s all about Transactions in ASP.NET C#.