以编程方式获取存储过程输出结构 [英] Getting stored procedure output structure programmatically

问题描述

我正在编写一个方法来检索指定存储过程返回的数据集结构。



C＃中的代码（开发中有点脏）如下：

I'm writing a method to retrieve the dataset structure as returned by a specified stored procedure.

The code in C# (under development so a little dirty) is as follows:

public static TableInfoList DeriveTableInfo(string linkedServer, string query)
        {
            string temporaryTable = string.Format("generatorTemp{0}", new Random(DateTime.Now.Millisecond).Next(0, 1000).ToString());

            string sql = string.Format("SELECT * INTO {0} FROM OPENQUERY ({1}, \'{2}\')", temporaryTable, linkedServer, query);

            new DataAccess().ExecuteCommandText(sql);

            TableInfoList derived = new TableInfoList("dbo", temporaryTable);

            sql = string.Format("DROP TABLE {0}", temporaryTable);

            new DataAccess().ExecuteCommandText(sql);

            return derived;
        }

基本方法可以概括为：



1）创建临时表

2）使用OPENQUERY将sproc运行到表中

3）从临时表中导出所有必要的信息（由调用sys.sp_columns的TableInfoList构造函数）并将其删除



这种方法很有效，直到我尝试调用使用临时表的sproc。由sproc创建的表未被识别，因为它超出了范围，我收到一条错误消息#WhateverTheTempTableIsCalled不是有效对象。



鉴于该方法旨在用于基于大量预先存在的sprocs的各种分析和代码生成任务，更改目标sproc不是一种选择。



有没有人知道OPENQUERY的工作区或者可能是使用它的替代方法？

With the basic approach being summarisable as:

1) Create a temp table
2) Use OPENQUERY to run the sproc into the table
3) Derive all the necessary info from the temporary table (done by the TableInfoList constructor with a call to sys.sp_columns) and drop it

This works perfectly until I attempt to invoke a sproc which employs a temporary table. The table created by the sproc is not recognised as it is out of scope and I get an error message to the effect of "#WhateverTheTempTableIsCalled is not a valid object."

Given that the method is intended for use in various analysis and code generating tasks based on a vast number of pre-existing sprocs, altering the target sproc is not an option.

Does anyone know of a workround with OPENQUERY or perhaps an alternative to using it?

推荐答案

我只是使用ExecuteReader来执行语句，然后使用GetSchemaTable来访问结果的详细信息。

I just use ExecuteReader to execute the statement and then use GetSchemaTable to access the details of the result.

这篇关于以编程方式获取存储过程输出结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！