Naor
Naor

Reputation: 24063

create transaction to stored procedures calls under c#

I am using stored procedures in sql server. I have several stored procedures calls written in c#. I want to wrap them inside transaction:

//Begin Transaction here
sp1Call();
sp2Call();
sp3Call();
//Commit here
//Rollback if failed

is there any way to do it?

Update: I am using the enterprise library. example to sp1Call():

public static void sp1Call(string itemName)
    {
        DbCommand command = db.GetStoredProcCommand("dbo.sp1_insertItem");
        db.AddInParameter(command, "@item_name", DbType.String, itemName);
        db.ExecuteNonQuery(command);
    }

Upvotes: 2

Views: 8002

Answers (2)

BrokenGlass
BrokenGlass

Reputation: 160852

Just a quick search comes up with DBTransaction:

   using (DbConnection connection = db.CreateConnection())
   {
     connection.Open();
     DbTransaction transaction = connection.BeginTransaction();
     try
     {
        DbCommand command = db.GetStoredProcCommand("dbo.sp1_insertItem");
        db.AddInParameter(command, "@item_name", DbType.String, itemName);
        db.ExecuteNonQuery(command, transaction);
        transaction.Commit();                   
     }
     catch
     {
       //Roll back the transaction.
       transaction.Rollback();
     }  
   }

Upvotes: 2

P Daddy
P Daddy

Reputation: 29527

You'll want to check out SqlTransaction.

Upvotes: 4

Related Questions