Multiples Table in DataReader
C#asp.net.NetSqldatareaderDatareaderC# Problem Overview
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 the count
and the other returns the actual data
. That is , My stored procedure
returns 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.
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?
C# Solutions
Solution 1 - C#
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 - C#
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 - C#
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());
}
Solution 4 - C#
The question is old but I find the answers are not correct. Here's how I do it:
List<DataTable> dataTables = new();
using IDataReader dataReader = command.ExecuteReader();
do
{
DataTable dataTable = new();
dataTable.Load(dataReader);
dataTables.Add(dataTable);
}
while (!dataReader.IsClosed);