C# DataReader 中的倍数表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12969318/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiples Table in DataReader
提问by muhammad kashif
I normally use DataSetbecause It is very flexible. Recently I am assigned code optimization task , To reduce hits to the database I am changing two queries in a procedure. one Query returns the countand the other returns the actual data. That is , My stored procedurereturns two tables. Now, I know how to read both tables using DataSets, But I need to read both tables using DataReader. In search of that I found This.
我通常使用，DataSet因为它非常灵活。最近我被分配了代码优化任务，为了减少对数据库的命中，我在一个过程中更改了两个查询。一个查询返回count，另一个返回actual data。也就是说，Mystored procedure返回两个表。现在，我知道如何使用 读取两个表DataSets，但我需要使用DataReader. 为了寻找这一点，我找到了This。
I follow the article and wrote my code like this:
我按照文章写了这样的代码：
dr = cmd.ExecuteReader();
while (dr.Read())
{
}
if (dr.NextResult()) // this line throws exception
{
while (dr.Read())
{
But I am getting an exception at dt.NextResult. Exception is :
但我在 dt.NextResult 处遇到异常。例外是：
Invalid attempt to call NextResult when reader is closed.
I also googled above error , but still not able to solve the issue.
Any help will be much appreciated. I need to read multiple tables using datareader, is this possible?
我也用谷歌搜索了上面的错误，但仍然无法解决问题。任何帮助都感激不尽。我需要使用 读取多个表datareader，这可能吗？
采纳答案by Pranay Rana
Try this because this will close connection ,data reader and command once task get over , so that this will not give datareader close exception
试试这个，因为一旦任务结束，这将关闭连接、数据读取器和命令，这样就不会给数据读取器关闭异常
Also do check like this if(reader.NextResult())to check there is next result,
也做这样的检查if(reader.NextResult())以检查是否有下一个结果，
using (SqlConnection connection = new SqlConnection("connection string here"))
{
using (SqlCommand command = new SqlCommand
("SELECT Column1 FROM Table1; SELECT Column2 FROM Table2", connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
MessageBox.Show(reader.GetString(0), "Table1.Column1");
}
if(reader.NextResult())
{
while (reader.Read())
{
MessageBox.Show(reader.GetString(0), "Table2.Column2");
}
}
}
}
}
回答by Tim Schmelter
I have tried to reproduce this issue (also because i haven't used multiple tables in a reader before). But it works as expected, hence i assume that you've omitted the related code.
我试图重现这个问题（也是因为我之前没有在阅读器中使用过多个表）。但它按预期工作，因此我假设您已经省略了相关代码。
Here's my test code:
这是我的测试代码：
using (var con = new SqlConnection(Properties.Settings.Default.ConnectionString))
{
using (var cmd = new SqlCommand("SELECT TOP 10 * FROM tabData; SELECT TOP 10 * FROM tabDataDetail;", con))
{
int rowCount = 0;
con.Open();
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
String object1 = String.Format("Object 1 in Row {0}: '{1}'", ++rowCount, rdr[0]);
}
if (rdr.NextResult())
{
rowCount = 0;
while (rdr.Read())
{
String object1 = String.Format("Object 1 in Row {0}: '{1}'", ++rowCount, rdr[0]);
}
}
}
}
}
回答by tolsen64
I built on Pranay Rana's answer because I like keeping it as small as possible.
我以 Pranay Rana 的答案为基础，因为我喜欢让它尽可能小。
string rslt = "";
using (SqlDataReader dr = cmd.ExecuteReader())
{
do
{
while (dr.Read())
{
rslt += $"ReqID: {dr["REQ_NR"]}, Shpr: {dr["SHPR_NR"]}, MultiLoc: {dr["MULTI_LOC"]}\r\n";
}
} while (dr.NextResult());
}
