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?
Answers. It IS possible to handle multiple result sets with a reader. string sqlText = "Select this, that from here; select somethingelse from there"; ...
The SqlDataReader is used to read a row of record at a time which is got using SqlCommand. It is read only, which means we can only read the record; it can not be edited. And also it is forward only, which means you can not go back to a previous row (record).
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"); } } } } }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With