SQL Reader objects within a recursive function?

[betawarz]

Hi all,

I am writing a recursive function that loops over some query results and builds a string. The function simply uses the same connection global connection object and instantiates new query and reader objects each call. The queries are being executed fine, so this is not an issue with having more than one command object per connection object (I saw many google results about this issue). The problem I am encountering is that the Read function call, on the reader object, is not iterating to the next item in the query result. It's like the child recursive calls are messing with the parent's reader object. Does anyone know why this might be occurring? An example pseudo-code follows:

Code: Select all

private string foo(int id, string path)
{
    SqlCommand the_command = "SELECT id, directory FROM table WHERE id = :id"
    the_command.connection_object = g_GlobalConnection

    SqlReader the_reader = the_command.execute()

    while (the_reader.Read())
    {
        int child_id = the_reader.readint()
        string child_path = the_reader.readstring()

        foo(child_id, child_path)
    }
}

Obviously this code path would not return or do anything, but it shows the flow of my function and where the child call is inside the Read loop.

Any ideas?

[Shalex]

Best practice:
While the PgSqlDataReader is in use, the associated PgSqlConnection is busy serving the PgSqlDataReader, and no other operations should be performed on the PgSqlConnection other than closing it. This is the case until the Close method of the PgSqlDataReader is called.

But I have tried a simple test with dotConnect for PostgreSQL v 5.30.202 where two data readers use alternately the same connection object. The output of the program was correct.
Try using "Protocol=Ver20;" and "Protocol=Ver30;" connection string parameters. Does it make any difference?
If possible, send us a small complete test project with the corresponding DDL/DML script so that we can reproduce the issue in our environment.