Using Transaction in asp.net

Transaction is the execution of multiple DML(Data Manupulation Lanugage) Statements like Insert Update and Delete any record from a database table as a single execution means either all the statement will be executed or none, if there occurs any error between the excution whole execution will be terminated with no database record affected.

To implement transaction in c# with SQL server as database you have to import following below namespaces.

Using System.Data;
Using System.Data.sqlclient;

Now declare all the ADO.net objects with SQLTransaction variable

Sqlconnection sqlcon;
Sqlcommand sqlcmd;
SqlTransaction sqltr;
sqlcon=new sqlconnection(ConnectionString);

Now Open Connection and Call the connection's BeginTransaction() function. which will create a Transaction Object for this Connection which you can hold in your variable


try
{
sqlcon.open();
sqltr=sqlcon.BeginTransaction();
}

catch (Exception ex)
{
Response.Write(ex.ToString());
sqlcon.Close();
return;
}

Now set the all the SqlCommand oject/objects (if multiple Sqlcommand objects) Transcation property. and after sucessful exection call commit() of sqltransaction object and if any exception occured then call rollback().

try
{
sqlcmd.Transaction=sqltr;
sqlcmd=new sqlcommand("User Storedproc or Direct Table");

sqlcmd.executenonquery();

sqlcmd.parameters.clear();

sqlcmd.commandtext="Userstroedproc or direct Table";

sqltr.commit();
sqlcmd.executenonquery();
}

catch(Exception ex)
{
Response.Write(ex.ToString());
sqltr.Rollback();
sqlcon.Close();
}
finally
{
sqlcon.Close();
}