How to use SQLTransactions in .NET?
In this article I am going to explain about SQLTransaction in .NET. Most of the cases we need to rollback some operation if incomplete / error occurred for that time use SQL Transaction to rollback incomplete transaction.
Description:
BeginTransaction() :
This is method is used to starts a database transaction.
Rollback():
Rolls back method is a transaction from a pending state. It is used to rollback previous execution when error is occurred
Commit():
This is used Commits the database transaction when your transaction run successfully.
Below i have explained code check it how to use sql transaction first example i given syntax example and second one execute all query if error occur in second query then rollback previous execution
sing System.Data;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
SqlConnection sqlcon = new SqlConnection(@"Server=RAVI-PC\SQLEXPRESS;database=test1;uid=ravindran;pwd=srirangam;");
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
SqlTransaction tran;
string query = "";
protected void Page_Load(object sender, EventArgs e)
{
}
//Insert record using single query and defined how to use rollback this is syntax example
protected void Button1_Click(object sender, EventArgs e)
{
try
{
sqlcon.Open();
//create transaction
tran = sqlcon.BeginTransaction();
query="insert into emp values('107','test','8000')";
sqlcmd = new SqlCommand(query,sqlcon);
//assign transaction to sql command
sqlcmd.Transaction = tran;
sqlcmd.ExecuteNonQuery();
//Commit transaction once your query successfully run
tran.Commit();
}
catch (Exception ex)
{
//Here i have call rollback
tran.Rollback();
}
finally
{
sqlcon.Close();
}
}
//Insert multiple record using if error occured reject all previous insert record
protected void Button2_Click(object sender, EventArgs e)
{
try
{
sqlcon.Open();
tran = sqlcon.BeginTransaction();
string[] q = new string[2];
q[0] = "insert into emp values('108','test','8000')";
q[1] = "insert into emp values('109,'test','8000')";
//Run multiple query if error is come reject whole insert statements
RunMutilpleQuery(q,tran);
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
}
finally
{
sqlcon.Close();
}
}
//write method to run all query
void RunMutilpleQuery(string[] qr,SqlTransaction tran)
{
try
{
for (int i = 0; i <= qr.Length - 1; i++)
{
sqlcmd = new SqlCommand(qr[i].ToString(), sqlcon,tran);
sqlcmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
}
} Source code:
Client Side: ASP.NET
Code Behind: C#Conclusion
I hope this code snippet is help you to how to use SQL transaction in .NET.