Getting .NET schema for a stored procedure result

Question

I have a couple of stored procedures in T-SQL where each stored procedure has a fixed schema for the result set.

I need to map the result sets for each procedure to a POCO object and need the column name and type for each column in the result set. Is there a quick way of accessing the information?

The best way I have found so far is accessing each stored procedure from .NET and writing my own extension method on a IDataReader/IDataRecord for dumping the information (column names and types) out.

Example, a stored procedure executing the following query:

SELECT Id, IntField, NullableIntField, VarcharField, DateField FROM SomeTable

would require me to have the mapping information:

Id - Guid
IntField - System.Int32
NullableIntField - Nullable<System.Int32>
VarcharField - String
DateField - DateTime

Answer 1

I think what you're doing is probably the best approach. There's no magic repository that holds all that information, really. You can find out about the stored proc parameters from the system catalog views, but the shape and field names and types of the result set aren't stored anywhere in SQL Server system views, unfortunately.

Answer 2

I think you should be able to use SqlDataReader.GetSchemaTable method to access the schema.

More information can be found here.

http://support.microsoft.com/kb/310107

Answer 3

if this is a one time thing you need to do, and not something you'll do at runtime each time the procedure is called, then just modify the result set query to dump the rows into a new table using INTO ThrowArayTable:

SELECT
    Col1, col2, col3, ...
    INTO ThrowArayTable     ----<<<add this line to existing result set query
    FROM ...
    WHERE ...

Run the application or call the procedure manually to generate the ThrowArayTable. You can now look up the column aames and datatypes using any method, SSMS or INFORMATION_SCHEMA.COLUMNS

Just remember to change the procedure back (remove the INTO ThrowArayTable value) so it will actually return the results set.

Answer 4

Another option (might be better or worse than GetSchemaTable() depending on your needs).
The following code gives you a DataTable with column structure identical to your resultset:

DataTable table = new DataTable();
var cmd = new SqlCommand("...");
using (var reader = cmd.Execute(CommandBehaviour.SchemaOnly))
    table.Load(reader);