multiple parameterized sql-statements in ado.net on SQL Server

Question

I have a problem executing two parameterized questions in one batch using SqlCommand in ADO.NET. (I want to reduce round-trips and avoid pinging)

Since I don't want execution plan pollution I expect ADO.NET to transform my CommandText from "stmt1; stmt2" with all parameters belonging to stmt1 and stmt2 added to SqlCommand.Parameters

to:

sp_executesql 'stmt1', 'paramdecl', param1values;
sp_executesql 'stmt2', 'paramdec2', param2values

But I can't find a way to make this happen.

Neither can I get the complete Text that is being sent to sql-server per SqlCommand so that I can combine two of them myself.

How do I do it?

Regards,

Jens Nordenbro

Answer 1

Do you expect any return values/result sets from the sql statements? Are the parameters for each statement different?

You can use SQL Profiler to monitor the SQL statments hitting the database.

You can use a single SQLConnection object and submit multiple commands before closing the connection. you will either have to dispose of the sqlCommand or clear it before re-using it again for your next query.

Adam