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 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

Example from above source

SqlConnection cn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
DataTable schemaTable; 
SqlDataReader myReader; 

//Open a connection to the SQL Server Northwind database.
cn.ConnectionString = "Data Source=server;User ID=login;
                       Password=password;Initial Catalog=DB";
cn.Open();

//Retrieve records from the Employees table into a DataReader.
cmd.Connection = cn;
cmd.CommandText = "SELECT Id, IntField, NullableIntField, VarcharField, DateField FROM SomeTable";

myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo);

//Retrieve column schema into a DataTable.
schemaTable = myReader.GetSchemaTable();

//For each field in the table...
foreach (DataRow myField in schemaTable.Rows){
    //For each property of the field...
    foreach (DataColumn myProperty in schemaTable.Columns) {
    //Display the field name and value.
    Console.WriteLine(myProperty.ColumnName + " = " + myField[myProperty].ToString());
    }
    Console.WriteLine();

    //Pause.
    Console.ReadLine();
}

//Always close the DataReader and connection.
myReader.Close();
cn.Close();

Answer 2

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.