CODEWITHSUNDEEP
c#.netsql-servert-sql
PHeiberg
PHeiberg

Reputation: 29811

Getting .NET schema for a stored procedure result

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

Upvotes: 9

Views: 4797

Answers (4)

Amitabh
Amitabh

Reputation: 61167

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();

Upvotes: 10

VladV
VladV

Reputation: 10349

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

Upvotes: 1

KM.
KM.

Reputation: 103579

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.

Upvotes: 0

marc_s
marc_s

Reputation: 754278

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.

Upvotes: 1

Related Questions