How to call multiple stored procedures in one database connection?

Sep 10, 2008 at 9:29 PM
Suppose I will populate 3 controls on my web form by calling 3 different stored procedures in my SQL server.

How, using Enterprise Library 4.0, can I make calls to these 3 different stored procedures in a single database connection (and get a dataset)?  Any idea?  Thanks a lot.  I am using ASP.NET 3.5 with C#.
Sep 11, 2008 at 3:49 AM
If you are looking for one magical statement to run, I think you may be disappointed. Although my preference is to use a stored procedure that returns multiple result sets, the following is one way.

Database db = DatabaseFactory.CreateDatabase();
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
DbTransaction trans = conn.BeginTransaction();

DataSet dataSet1 = db.ExecuteDataSet Method (trans, "storedProc1, new object[] { "param1", "param2") ;
DataSet dataSet2 = db.ExecuteDataSet Method (trans, "storedProc2, new object[] { "param3") ;

// dataSet1 and dataSet2 will have one table each
DataSet dataSet = new DataSet();
dataSet.Tables.Add(dataSet1.Tables[0]);
dataSet.Tables.Add(dataSet2.Tables[0]);
}

You could also use extension methods to add the functionality that you are looking on Database object.

Sep 11, 2008 at 2:03 PM
Edited Sep 11, 2008 at 2:14 PM
Thank you.  I actually did that with DbTransaction, but I thought that we use DbTransaction only when we insert, update, delete stuffs in the database.  I didn't know that it works even if the stored procedure merely select data from the database.

Originally, I thought that we must call
trans.Commit();  

or
trans.Rollback(); 
if we have

DbTransaction trans = conn.BeginTransaction();
But, it seems that it is not the case.

One quick question: OK, so, we use DbTransaction like we've done here, but does this mean that these sprocs will be called in a single DB connection?  I ask because I don't know what is going on internally inside DbTransaction.  Any idea?
Sep 11, 2008 at 3:29 PM

Hi,

You're correct in that a transaction shouldn't be necessary to read data. However transactions are associated to a connection, so they are a way to use a specific connection across calls to the Database methods (you can see there are no overloads for ExecuteDataset that just take a connection).

The transaction would be discarded when the connection is closed, but adding a call to Rollback() wouldn't hurt.

Regards,
Fernando

Sep 11, 2008 at 5:34 PM
By using the transaction, you avoid the connection pool open/close/reset overhead.

If you are able to wrap the multiple stored procedure calls in another stored procedure, you could use Database.ExecuteReader() to execute the stored procedure. Then you may be able to use DataSet..::.Load Method (IDataReader, LoadOption, array<DataTable>[]()[]) to load all of the data tables.  See the example on the MSDN link.

Your approach really depends on your end goals (performance, understandable code, etc).

Good Luck,

Phil

Sep 12, 2008 at 9:11 PM
fsimonazzi: Thank you very much for your clarification concerning DbTransaction.
Sep 12, 2008 at 9:14 PM
pbolduc:

Yes, I am aware of the approached you talked about, and I actually did that before. 

But I think the disadvantage of this approach is that we would have to create a wrapper sproc for different sets of sprocs we wanna run in a batch. 

And as the number of such wrapper sprocs increases, it will cause a db maintainence overhead.  What do you think?