Is it possible to execute many stored procedures in a single operation?

Question

I am coding to read xml files to update the database. I get about 500 xml files and I want to process them as fast I can.

All database operations are done using stored procedures.

There are about 35 different stored procedures called for each xml file.

Initially I had written the code like this

var cmd = new SqlCommand("EXEC UpdateTeamStats("+teamId+","+points+")");
cmd.CommandType = CommandType.Text;

but after going through some best practices I changed it to

var cmd = new SqlCommand("UpdateTeamStats");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("teamId", 21);
cmd.Parameters.Add("points", 2);

because of the high number of stored procedures being called from the program I realized I have to make lesser number of calls in order to optimize.

So I want to collect all the 35 stored procedures together and execute them in one go.

The stored procedures are different with different parameters and I dont know a way to collect and execute them together after the parameter changes I did above.

I was thinking of calling one giant stored procedure and inside that stored procedure calling the other 35 , but I am not very good at SQL and it will lead to unnecessary complexity.

Is it Possible to do this entirely in C#?

Or is there some other better method to queue up the storedprocedures and run them quickly

Answer 1

Please download Microsoft Applications Data block from

http://www.microsoft.com/download/en/details.aspx?id=435

Fine, But how do I use it?

Usage of this wrapper class is quite simple.

DAC DC = new DAC();
DC.StoredProcedure = "nProc_InsertOrder";
DC.Params.Add("@OrderId", SqlDbType.VarChar, "Order1" );
DC.Params.Add("@CustomerName", SqlDbType.VarChar, "test");
DAC.Commands.Add(DC);

DC = new DAC();
DC.StoredProcedure = "nProc_InsertOrderLineItems";
DC.Params.Add("@OrderId", SqlDbType.VarChar, "Order1" );
DC.Params.Add("@OrderLineId", SqlDbType.VarChar, "A1");
DAC.Commands.Add(DC);

DC = new DAC();
DC.StoredProcedure = "nProc_InsertOrderLineItems";
DC.Params.Add("@OrderId", SqlDbType.VarChar, "Order1" );
DC.Params.Add("@OrderLineId", SqlDbType.VarChar, "A2");
DAC.Commands.Add(DC);

DC = new DAC();
DC.StoredProcedure = "nProc_CreateBill";
DC.Params.Add("@BillDate", SqlDbType.DateTime, DateTime.Now);
DC.Params.Add("@BillId", SqlDbType.VarChar, "Bill1");
DAC.Commands.Add(DC);
DAC.ExecuteBatch();

If the order insertion is failed, the bill should not be created. Similarly, if the line items are failed, then the order should not be created. We are achieving this in just a few lines of code through ADO.Net.

In this example, till we call ExecuteBatch, we are not actually inserting the records but preparing the object for making batch updations.