不使用SqlDataAdapter.Fill执行存储过程返回多个表（）？ [英] Execute stored procedure returning multiple tables without using SqlDataAdapter.Fill()?

问题描述

我知道，通常推荐的从存储过程填充DataSet的实例是使用SqlDataAdapter.Fill（数据集）。每一个答案我已经能够找到说使用它弄出来盒子的方式。我会给予，通常，它的伟大工程。

I know that the usually recommended way to fill a DataSet instance from a stored procedure is to use SqlDataAdapter.Fill(DataSet). Every answer I've been able to find says to use it the way it comes out of the box. I'll grant that normally, it works great.

然而，它打破了，当你需要做的存储过程的输出和输入数据集之间的数据处理。具体而言，当输出使用自定义的CLR类型，它与打破InvalidOperationException异常：DataReader.GetFieldType（N）返回null ，其中 N 是列的列索引（在表中的一个;似乎有说服力的表列索引引用没有明显的方式），它使用其输出的CLR类型。这是可以理解的，因为读者不知道是有问题的列什么类型的数据。

However, it breaks down when you need to do processing of the data between the stored procedure output and the DataSet input. Specifically, when using a custom CLR type in the output, it breaks with InvalidOperationException: DataReader.GetFieldType(n) returned null. where n is the column index of the column (in one of the tables; there seems to be no obvious way of telling which table the column index references) which uses the CLR type in its output. This is understandable, since the reader has no idea what kind of data is in the column in question.

我可以打出来的CLR类型的反序列化逻辑，并用它在这两个地方，但是我似乎没有来得及填充之前调用任何这样的反序列化（）的方法分解。

I can break out the deserialization logic of the CLR type and use it in both places, but I don't seem to have time to call any such deserialization method before Fill() breaks down.

我知道 SqlCommand.ExecuteReader（），而只能似乎得到一个表出来的那一个。

I know about SqlCommand.ExecuteReader(), but can only seem to get a single table out of that one.

在讨论的CLR类型的设计更换核心系统领域（这是目前存储为自由格式文本）的东西多一点严格的之一​​。历尽数据库，并添加一个方法调用到这般田地的每一个返回的实例的问题已经讨论了作为一种可能性，但肯定是工作中一个不平凡的量。因此，有人认为，在转换可以在DAL来完成，而不是（只需要这样的列可以通过编程来识别，这是可行的），使得存储对客户透明的和的客户端使用的数据仍透明的数据库。

The CLR type in question is designed to replace one of the core system fields (which is currently stored as free-form text) with something a little more stringent. The question of going through the database and adding a method call to every single returned instance of such a field has been discussed as a possibility but would definitely be a non-trivial amount of work. So it was thought that the conversion could be done in the DAL instead (only requiring that such columns can be identified programmatically, which is doable), making storage transparent to the client and client use of the data still transparent to the database.

这样：如何获得所有表中的存储过程的输出，而无需使用SqlDataAdapter.Fill（）？ 或者，我怎么勾到SqlDataAdapter.Fill（）的执行做执行SP和填充DataSet中的人工处理？

Thus: How do I get access to all the tables in the stored procedure output, without using SqlDataAdapter.Fill()? Alternatively, how do I hook into the execution of SqlDataAdapter.Fill() to do manual processing between execution of the SP and the populating the DataSet?

推荐答案

您可以选择以的DataReader 多个表。您可以使用 阅读器。 NextResult ，以检查是否有更多的结果集，并以超前的数据读取器给它：

You can select multiple tables with a DataReader. You can use reader.NextResult to check if there are more resultsets and to advance the data reader to it:

using (var con = new SqlConnection(Properties.Settings.Default.ConnectionString))
{
    using (var cmd = new SqlCommand("StoredProcedureName", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        int rowCount = 0;
        con.Open();
        using (IDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                Console.WriteLine("Object 1 in Row {0}: '{1}'", ++rowCount, rdr[0]);
            }
            if (rdr.NextResult())
            {
                rowCount = 0;
                while (rdr.Read())
                {
                    Console.WriteLine("Object 1 in Row {0}: '{1}'", ++rowCount, rdr[0]);
                }
            }
        }
    }
}

这篇关于不使用SqlDataAdapter.Fill执行存储过程返回多个表（）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！