I have two tables in my databse viz.PurchaseOrderMST and SiteTRS. The primary key of PurchaseOrderMST is foreign key in SiteTRS. The case is first data is inserted in PurchaseOrderMST and then SiteTRS using two individual SPs. I want to maintain transaction during insertion on these two tables from the asp.net(C#). If data in the first table is inserted successfully then and only then data in the second tables should be inserted. If insertion fails in second tables, the insertion in the first tables should also rollback. How can I do this using transaction mechanism of asp.net???
Add a comment
|
1 Answer
This is not really related to ASP.NET, but generally how transactions work in the .NET framework. With SqlClient, this is how you do it:
using (var connection = new SqlConnection("your connectionstring"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
try
{
using (var command1 = new SqlCommand("SP1Name", connection, transaction))
{
command1.ExecuteNonQuery();
}
using (var command2 = new SqlCommand("SP2Name", connection, transaction))
{
command2.ExecuteNonQuery();
}
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
You of course need to add Parameters to the SqlCommand objects before you execute them, and which execute method you use (ExecuteNonQuery(), ExecuteScalar() or ExecuteReader()) depends on whether your stored procedures actually returns any data or not.
1 Comment
Vandel212
I may get this tattooed on me. Very helpful. Thank you.