Getting the field names and definitions from a stored procedure in c#

Question

Getting the field names and definitions from a stored procedure in c#

Mischa E.J. Hoogendoorn

Mr or Mrs. 500

Points: 558
More actions
July 7, 2010 at 2:27 am

#226597

Hello,
I am a DBA, and no programmer, and my programmers have a little problem, and I could use some help.
The programmers are using stored procedures to get information from the database, or write information to the database.
My stored procedures are all using the RETURN value to give a error code back (0 = ok, everything else is a error).
But when I have a simple stored procedure for example:
select a, b, c
from test
with a return value, the programmers are only seeing the return value from that stored procedure, and not the fields (a, b, c). They can add those manually, but the question is: How can they automaticly see, the return value and the fields (a, b, c) and there definitions in the designer of C#, when they connect the stored procedure to there ADO.NET component?
I did try the SET FMTONLY ON statement, but then my stored procedure will not return any results (as expected).
I am looking for a solution, that will give all fieldnames and definitions to my developers without dropping the functionallity of my stored procedure.
We are using SQL-Server 2008R2 Enterprise X64, with Visual Studio 2010.
I hope that someone can help me on this one.
I thank you in advance.
Best regards,
Mischa

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

The SqlCommand class of the SQLDataReader has to be properly initialized and set up with the command parameters (both input and output) to the stored procedure before calling ExecuteReader. Any output parameters (including the return code) will not be available until after all rows have been read or the Reader has been closed.

Pass this link on to your developers, it should pretty much explain it: http://support.microsoft.com/kb/308621

The probability of survival is inversely proportional to the angle of arrival.

Lowell SSC Guru Points: 323518 More actions · Answer 2

you could use the OPENROWSET command to run the proc and insert its resulting data into a table on the fly.

then you can drag the table into a typed dataset into your solution, so you'd have all the definitions.

here's an example:

SELECT *

into MyTableOnTheFly

FROM OPENROWSET('SQLOLEDB','Server=yourservernamehere;Trusted_Connection=Yes;Database=Master',

'Set FmtOnly OFF; EXEC dbo.sp_Who')

now I can examine the table with sp_help or anything i need to do. I do this quite a lot where i need the definition in my DataSet.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mischa E.J. Hoogendoorn Mr or Mrs. 500 Points: 558 More actions · Answer 3

Thanks for all of your help, I have not found the solution yet, but I will keep on working on it, and I will inform you all when I have found it!

Best regards,

Mischa