Help returning DataReader in Async "world".

[vincenzo-antolini-os]

Hi,
doing some test with this library because the Oracle one is too bugged when connecting to MariaDb and this lib is very promising.
I'm very attracted by the Async features, I would like to migrate all currently developing software.

Need help... line return await command.ExecuteReaderAsync(); throws an exception.
Is the way I use it not correct.
I read again and again this snippet and nothing seems weird.
If, instead of returning the DataReader, I loop through it works.
Some suggestion?
Thanks

Follow snippet

public async Task<IDataReader> GetDataReaderAsync(string commandText)
{
        await using var connection = (MySqlConnection) DbHelper.CreateDbConnection(ProviderName, ConnectionString);
        await connection.OpenAsync();
        await using var command = new MySqlCommand(commandText, connection);
        return await command.ExecuteReaderAsync();
}

[vincenzo-antolini-os]

What I suspect is...
When returning the connection is closed and it makes the DataReader (byRef) unavailable.

[bgrainger]

The problem is this line:

await using var connection = (MySqlConnection) DbHelper.CreateDbConnection(ProviderName, ConnectionString);

When the connection variable goes out of scope, it is disposed. Once that happens, the returned IDataReader is no longer usable.

One solution is to use CommandBehavior.CloseConnection on your command, and remove the await using from var connection.

With this behaviour, the returned reader will keep the connection open until you dispose it, then it will close the connection.

There are some examples of doing this, as well as some pros and cons of the whole approach, at https://stackoverflow.com/questions/5689263/what-is-the-use-advantage-of-using-commandbehavior-closeconnection-in-executerea.

[vincenzo-antolini-os]

Thank you, it is like initially I did.
But something made me off the well-worn path.
The ExecuteReaderAsync is inside a Try/Catch block and it raises some sort of exception.
All works, data is ok but the hit on the breakpoint made me confused.
I attach a complete sshot debugging.
Don't know if this is some a bug but, I make you aware of.
Thanks

[bgrainger]

What's the exception (that was caught) and its full call stack?

[vincenzo-antolini-os]

Ahm... that make me struggle for some hours.
That was the first time I faced the scenario and I assume is related to Threads management of Async architecure.
At the end a gave up, but would like to understand if possible.

The variable ex is not initialized by Visual Studio Intellisense/Debugger (even inside the Locals panel no trace of it).
The line Log.Error is not hit and this is the weirdest part.
The debugger steps directly to the trow statement but acts like no exception was (the caller function doesn't rethrow).
I tried to debug downloading source code but, as you know, it goes very deep.

What I suspect is that there are two execution paths overlapped and one of them really raises exception.

I made a last test changing code, instead of throw I return null.

What I found is:

1. If the query is intentionally wrong (eg SLECTE * FROM tablename) the exception is raised and the stack shows the error. The caller function receives null and acts as expected.
2. If the query is ok, the catch block is hitted the same, should return null but actually the caller function receives the DataReader as no exception was. For that reason I suspect of overlapping executions.

Sorry, cannot help more. I will document in my code this scenario for future reference.

[bgrainger]

Are you debugging a Release build? Sometimes the "current line" shown in the debugger doesn't match the source code due to compiler or JIT optimisations.

[vincenzo-antolini-os]

yes, nothing wrong there.
I'll dig this in the next days. I'll use a blank project to exclude out of control factors.
Will let u know.