Executing more than one stored procedures in a DataReader

Hey guys,

I have found out that we can execute multiple queries and receive multiple resultsets in a SqlDataReader by executing the queries with ";" separators,

However, what if we wanted to execute two sqlcommand storedprocedures? are there any other way rather than placing "Execute sp1;Execute sp2" in the command text?

I would like to do it in a way whereby I can pass in two storedprocedures with parameters binding capability rather than execute sp1(param1, param2);execute sp2(param1, param2, param3)

Hope to get some suggestions and advice from you guys,

Thank you very much in advance.

Combine the two stored procedures into a third that will execute both.

|||

thanks Mike for your response,

any other better ways?

as there would be too many storedprocs just for combining procedures and it might get messy..

I was thinking to write a custom class and configure the class as I would for Sqlcommand storedprocedure, and then combine the various StoredProc Classes with a ";" and pass into the sqlcommand text.

But would this add-on to more performance defect? as there will be extra objects involved..

Please advice. Thanks.

|||

I don't think by any means or ways its a good idea to execute 2 or more procedures simultaneously from code. If you create such a class which can call and handle 2 or more SPs then also you'll have some questions to answer, such as what about the parameters ? You must be having different parameters for different SPs. Out of them, some may be out put type. What if an error occurs while executing any of the SPs ?.

So, my advice to you is better you execute them one by one if you don't have the compulsion ( which I don't think you'll be having ) to execute them simultaneously.

|||

Hi,

The best way as one user suggested you is to create a stored proc that combines multiple stored procs into one :

For eg:

CREATE PROCEDURE sp_ProcCallMultiple
(
@.var1 varchar(10)
@.var2 varchar(10),
)
AS

EXEC sp_Proc1 @.var1

EXEC sp_Proc2 @.var1

Once done, you can then use sp_ProcCallMultiple using a data reader

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Suprotim Agarwal:

Hi,

The best way as one user suggested you is to create a stored proc that combines multiple stored procs into one :

For eg:

CREATE PROCEDURE sp_ProcCallMultiple
(
@.var1 varchar(10)
@.var2 varchar(10),
)
AS

EXEC sp_Proc1 @.var1

EXEC sp_Proc2 @.var1

Once done, you can then use sp_ProcCallMultiple using a data reader

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

Hi,

Thanks for showing this sample but I already know about this, just wanted to find out if there is an alternative.

To Dhimant: It is possible to handle Output Parameters and so on and it is more effective as it only takes one trip to the server. However, I only need to do this mostly for queries with select statements, other stored procs which require calling two or more procs, i usually execute them in one proc itself. The reason why i didnt want to combine the selection procs is because, there may be too many combinations and things will get messy. Thanks for your advice.

ExecuteNonQuery while dataReader still open

Hi all!

I basically need to get some records from a table and while looping through them i need to insert some records on other table.

I keep getting this error:

There is already an open DataReader associated with this connection which must be closed first.

The piece of code that I have is like this:

 ...
SqlCommand sqlCmd2 = new SqlCommand(sqlString2, dbConn);
sqlCmd2.Transaction = trans;
SqlDataReader dr = sqlCmd2.ExecuteReader(CommandBehavior.CloseConnection);
//loop through dr
while (dr.Read())
{
string sqlStr = "insert into prodQtyPrice (typeQtyId, prodId, typeId) values(28," + dr["prodId"] + "," + dr["typeId"] +")";
SqlCommand sqlCmd3 = new SqlCommand(sqlStr, dbConn);
//sqlCmd3.Transaction = trans;
sqlCmd3.ExecuteNonQuery();
}
 ...

Also I would like to have the insertions in the same transaction as the previous sql commands.

Thanks a million!

LAMwhy don't you just do that in a procedure? It looks like it's a simple select then insert right?

Insert Into ProdQtyPrice ( typeQtyId, prodId, typeId )
VALUES
SELECT 28, prodID, TypeID From WhereverYouGetThisFrom

GO|||First of all, thanks for answering so fast!

The main reason that is not inside a storedProcedure is requirements. The entire app is not using SP and this has to be done the same way.

LAM|||>>The entire app is not using SP

looks like you have bigger prbs to worry about.
anyway, to answer your q, since the connection is already open with the datareader, you need to open another connection to do the insert.

hth|||you all are making me cross.

command.CommandText = "Insert Into ProdQtyPrice ( typeQtyId, prodId, typeId ) VALUES SELECT 28, prodID, TypeID From WhereverYouGetThisFrom";