How do I detect an empty result set from ExecuteReader?

Topics: Data Access Application Block
Sep 12, 2008 at 11:42 PM
Edited Sep 13, 2008 at 12:55 AM

I have a SQL query command that takes one parameter. I need to call this query in a loop, modifying the parameter on each call, so as to limit the amount of data on each call. I need to keep the connection open across each call, so I am using the ExecuteReader() method to do this and then I'll call close on the reader when I'm done.

I'm running into two issues:
1 - How do I make successive calls using the same connection?
2 - How do I detect when the last query returns no result set?

Here's a code snippet I'm using. I know this isn't right since: 1) each call to ExecuteReader() creates a new connection into the database; and 2) it doesn't detect when the last result set is empty.

SqlDatabase sqldb = new SqlDatabase(connectionString);
DbCommand dbCommand = sqldb.GetSqlStringCommand(sqlQuery);

IDataReader dataReader;

// Define the initial user identity value
int userIdentity = 0;

// set parameters to the query
sqldb.AddInParameter(dbCommand, "@userIdn", DbType.Int32, userIdentity);

// loop until query returns no rows
while (true)
    // Set the next user Identity to start next query
    sqldb.SetParameterValue(dbCommand, "@userIdn, userIdentity);
    dataReader = sqldb.ExecuteReader(dbCommand);

    while (dataReader.Read())
        // Process the next row. First save the user identity as input to the next query run
        userIdentity = (int)dataReader["userIdn"];


Does anyone know how I can accomplish those two things: 1) use the same connection on each call to the database; and 2) how do I detect when the last result set is empty?

Any advice would be greatly appreciated.

Thank you.


Sep 13, 2008 at 12:07 AM
Edited Sep 13, 2008 at 12:56 AM
As an update: I tried to find anything on this on the Microsoft site, and I was unable to. I hope someone here can shed some light for me.
Thanks again.
Sep 13, 2008 at 8:19 AM
When the result set is turned out to be empty your code fails at
userIdentity = (int)dataReader["userIdn"];
So just handle this in try-catch.

When you try to open a new connection to database this connection is actually requested from a connection pool so it doesn't mean you open a new connection on each command.
Some words on this are here