Knowing the type of the stored proc when invoking from C#

Question

I am making a windows service to be able to run operations on a sql server database (insert, edit, etc) and invoke Stored Procs.

However, is there a way for me to know the type of the SP? When invoking from C#, I need to knof if it is returning 1 value, or more, or none (so I can use executereader, scalar, etc)?

Thanks

Answer 1

1. A non-query is usually called with SqlCommand.ExecuteNonQuery(). But it's valid to run it as SqlCommand.ExecuteReader(). The only difference is that the first call to DataReader.Read() returns false for a stored procedure that does not return a resultset.
2. A rowset is run as SqlCommand.ExecuteReader(). The first call to DataReader.Read() will return true.
3. A scalar is just a shortcut for a rowset with one column and one row.

So you can use ExecuteReader in all three scenarios.

Though it seems unnecessary for your question, you can retrieve meta-data for the resultset using the fmtonly option. The setting causes the statement to return the column information only; no rows of data are returned. So you could run:

SET FMTONLY ON;
EXEC dbo.YourProc @par1 = 1;
SET FMTONLY OFF;

Executing this as a CommandText from C#, you can examine the column names the stored procedure would return.

To verify that a stored procedure run in this way does not produce any side effects, I ran the following test case:

create table table1 (id int)
go
create procedure YourProc(@par1 int)
as
insert into table1 (id) values (@par1)
go
SET FMTONLY ON;
EXEC dbo.YourProc @par1 = 1;
SET FMTONLY OFF;
go
select * from table1

This did not return any rows. So the format-only option makes sure no actual updates or inserts occur.