Multiples Table in DataReader
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");
}
}
}
}
}
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]);
}
}
}
}
}