2 questions about the SqlCommand class

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I'm used to having my own code build the SQL that's passed to the SqlCommand
object, which is almost always in the format "EXEC
<databasename>.dbo.[<spname>] @<param1> = <something>, @param2 =
<something>, etc.". This is an habit from the days before I moved to DotNet,
which I am now reexamining. Maybe I should invoke stored procedures with the
built-in way (below)?

cmd = New SqlCommand
cmd.Connection = CurrentConnection
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = SomeStoredProcedureName
cmd.Parameters.Add("@param1", Param1Value)
cmd.Parameters.Add("@param2", Param2Value)
....

I have two questions regarding this:

1. My stored procedure names reside in different databases. I don't want to
have to check to see if I need to change the current connection's database
every time I invoke one. Only, when I put a fully qualified stored procedure
name into the CommandText property, it fails to parse anything with a dot in
it. Is the SqlCommand class just not designed to handle fully qualified
stored procedure names?

2. I always want to be able to see any SQL that's about to go to the server
if I need to. Where is the property in SqlCommand that will tell me what
it's about to send? Do I have to guess?

TIA,
Bob
 
For 1, you should be able to qualify the stored-proc with database and
owning schema just fine. If you post your code I'll take a look.

Regarding what we send to the server, when CommandType is StoredProcedure we
don't generate any extra SQL. We send the stored-proc name, and then each
parameter goes after the name in the wire-protocol format. In the only case
where what we send to the server is different from the command text is in
SQL batches with parameters with CommandType == Text. In that case you can
use the SQL profiler to see that we invoke sp_executesql with your SQL
statement as a parameter.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.
 
D'oh. Error on my part, you're right about it accepting fully qualified
stored procedure names.

Regarding the SQL, I checked and you're right - it adds no extra SQL, so
what I'll do is subclass and add an extra property to generate it.

Thanks!

Pablo Castro said:
For 1, you should be able to qualify the stored-proc with database and
owning schema just fine. If you post your code I'll take a look.

Regarding what we send to the server, when CommandType is StoredProcedure we
don't generate any extra SQL. We send the stored-proc name, and then each
parameter goes after the name in the wire-protocol format. In the only case
where what we send to the server is different from the command text is in
SQL batches with parameters with CommandType == Text. In that case you can
use the SQL profiler to see that we invoke sp_executesql with your SQL
statement as a parameter.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no
Click to expand...
rights.
 
Back
Top