Multiples Table in DataReader
Solution 1
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,
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");
}
}
}
}
}
Solution 2
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]);
}
}
}
}
}
Solution 3
I built on Pranay Rana's answer because I like keeping it as small as possible.
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());
}
muhammad kashif
Technical Team Leader at Omnicom Media Group. Experience with: Angular+, .Net Core, SQL Server, Elastic Search, SPLUNK Phantom, MongoDB, AWS Lambda, AWS Dynamo DB, AWS API Gateway, AWS Cloud Watch, AWS S3, Cloud formation, CSS, HTML, C#, ASPNETCore
Updated on July 09, 2022Comments
-
muhammad kashif almost 2 years
I normally use
DataSet
because 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 thecount
and the other returns theactual data
. That is , Mystored procedure
returns two tables. Now, I know how to read both tables usingDataSets
, But I need to read both tables usingDataReader
. In search of that I found 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 :
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?