Execute multiple stored procedures in one transaction from WCF

Question

This is my first post on here..

I'm writing a program in MVC3 that has a WCF service which acts as the Data Access Layer. In my DAL, I have to do some sort of 'batch' inserts and updates.. particularly with orders for example.. let's say one order has several items and could have several payment methods etc.. so when I insert a new order I'll need to insert all items related to that order and so on..

Therefore, what I'm looking for is the better way and feasible method to be able to run several stored procedures, e.g one which will insert the order, another which will insert its items, etc..

The tables Order and Item are linked together with a third table called Order_Items, which will have (fk) order_id, (fk) item_id, qty, price..

I know I can run multiple commands by changing command text and and executing non query withing a transaction.. but I would like to run stored procedures instead of hardcoding text commands.. or I can run the procedures by making command text something like

cmd.CommandText = 'exec sp_insert_order @order_number @order_date ...'
cmd.ExecuteNonQuery();

and then loop the items say

foreach (string s in insert_items)
{
    cmd.CommandText = s;
    cmd.ExecuteNonQuery();
}

all this within a transaction and then do a commit.. but I don't feel this is such a clean way of doing things.. can someone please share their opinion.

Answer 1

If you're using stored procedure, you should change the way you call them - I would recommend using this approach:

// define your stored procedure name, and the type 
cmd.CommandText = 'dbo.sp_insert_order';
cmd.CommandType = CommandType.StoredProcedure;

// define and fill your parameters
cmd.Parameters.Add("@order_number", SqlDbType.Int).Value = order_nr;
cmd.Parameters.Add("@order_date", SqlDbType.DateTime).Value = ......;

cmd.ExecuteNonQuery();

Basically, you'd have to do this for each stored procedure you want to call, and you could wrap all of those in a single transaction without any problems:

using(SqlConnection connection = new SqlConnection("your-connection-string-here"))
{
   SqlTransaction transaction = connection.BeginTransaction();

   try
   {
        // call all stored procuedures here - remember to assign the 
        // transaction to the SqlCommand!!
         ....

        transaction.Commit();
    }
    catch(Exception exc) 
    {
       transaction.Rollback();
    }        
}