如何获取存储过程的数据库模式 [英] How to obtain the database schema of a stored procedure

问题描述

我在SQL Server中有一个T-SQL存储过程，该过程从多个表中查询多个列。例如，它可以从一个表中提取员工ID，从另一个表中提取员工名称，从另一个表中提取员工部门。在数据库中，每个列的长度都不同（例如，员工ID可以是 varchar（20），员工名称可以是 varchar（ 30），部门可能是 varchar（40））。

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

如何.NET中如何获取存储过程结果的模式？换句话说，在.NET中，当我执行存储过程时，我想知道第一列结果的限制为20个字符，第二列结果的限制为30个字符，第三列结果的限制为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

注意我正在使用企业图书馆。在C＃或VB中欢迎回答（尽管程序在vb中）。

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

推荐答案

我不知道企业库，但是普通的ADO.NET代码类似于以下内容

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.

您当然可以在上面的代码中使用泛型-DbCommand，DbParameter等。
我的猜测是企业库，您基本上需要相同-除了使用 SchemaOnly设置以外，按照通常的方式执行存储过程。

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.

这篇关于如何获取存储过程的数据库模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！