How to obtain the database schema of a stored proc

问题:

I have a T-SQL stored procedure in SQL Server, which queries multiple columns from multiple tables. For example, it may pull employee id from one table, employee name from another table, and employee department from another. In the database, each of those columns has a different length (e.g. employee id may be a varchar(20), employee name may be a varchar(30), and department may be a varchar(40)).

How do I get the schema of the stored procedure results in .NET? In other words, in .NET, when I execute the stored procedure I want to know that 20 characters is the limit of the results first column, 30 characters is the limit of the results second column, and 40 is the limit of the results 3rd column.

Here is what I am currently doing, but its just returning a string, and tells me nothing about the database limits of the columns;

Dim dbCommandWrapper As DBCommandWrapper

dbCommandWrapper = GlobalDatabase.objDatabase.GetStoredProcCommandWrapper("My_StoredProcedure_Report")

If IsNothing(objGlobalDatabase.objTransaction) Then
   Return GlobalDatabase.objDatabase.ExecuteDataSet(dbCommandWrapper).Tables(0).DefaultView()
Else
   Return GlobalDatabase.objDatabase.ExecuteDataSet(dbCommandWrapper, objGlobalDatabase.objTransaction).Tables(0).DefaultView()
End If

Note I am using Enterprise Library. Answer is welcome in C# or VB (though the program is in vb).

回答1:

I am not aware of Enterprise Library but with plain ADO.NET the code would be similar to the following

//assume an open connection
using(connection)
{
    using (DbCommand command = connection.CreateCommand())
    {
        command.CommantText = "procedure name";
        //setup and add parameters.
        SqlParameter parameter = command.CreateParameter();
        parameter.Name = "param name";
        //set the mode - out/inputOutput etc
        //set the size
        //set value to DBNull.Value

        //execute the stored procedure with SchemaOnly parameter
        var reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
        var table = reader.GetSchemaTable();
     }
}

You can then analyze the DataTable for detailed result set information.

You can of course use generic types in the above code - DbCommand,DbParameter etc. My guess is with Enterprise Library you would need to basically do the same - execute the Stored Procedure as you would do normally except with 'SchemaOnly' setting.

回答2:

In similar situations, for complex stored procedures, I have found it necessary to add a dummy SELECT statement which returns zero rows of the correct types:

SELECT 0 AS ID, CAST(NULL AS NVARCHAR(20)) AS Country, ...
FROM someTable
WHERE 0 = 1

this is the shape of the result set as returned to things like GetSchemaTable.