ExecuteReader - Two select statments- returns only the first statment result

[orencs]

Hello,

I am using Microsoft.Practices.EnterpriseLibrary.Data.

I am running the following

sqlCommand = "SELECT var1 FROM table1 WHERE var2 IN (4,5,6) ; SELECT
var3 FROM table2 WHERE var2 IN (4,5,6)";

IDataReader dataReader = db.ExecuteReader(dbCommandWrapper); while
(dataReader.Read()) {

The problem is that the dataReader holds only the data from table1 i.e.
the first select statment.

Do you have any idea how to work it out?

 

[Marina]

You have to call .NextResult to access the result set for the second query.

You can also use the UNION operator in your SQL to get a union of the
results both queries return, if they are getting two different pieces of the
same data.

 

[Nicholas Paldino [.NET/C# MVP]]

You need to call the next result, like this:


// Get the reader.
using (IDataReader dataReader = db.ExecuteReader(dbCommandWrapper))
{
// Cycle while there are results.
do
{
// Cycle while there are records.
while (dataReader.Read())
{

}
} while (dataReader.NextResult());
}

I didn't run this through a compiler, but you should get the general
idea for the syntax. The key here is the call to NextResult. Of course,
putting it in a loop like this isn't going to be the right way to do this,
since you will probably need specific processing of each result.

Hope this helps.

 

[oren cs]

Thank you for your quick post.
I works. don't know why I have missed it.

New problem:
Do you know how to avoid the exception?

I am running in the inner, read, loop and check for data from both
statments. At each do while iteration loop I have one exception.

At the end I have the correct data but since exception is expsnsive I
want to avoid it all together.

Thank you

 

[Nicholas Paldino [.NET/C# MVP]]

oren,

You shouldn't get an exception. Without you giving the details (or how
you ultimately called the code), it's impossible to tell.

 

[orencs]

I am retrieving the data explicitly like this:
dataReader["a_max"];
dataReader["b_max"];
In the first I have loop the dataReader will throw and exception for
dataReader["b_max"];
in the second on dataReader["a_max"];

I can avoid it by knowing exactly what are the column names in each
iteration or using GetValues()