Database reader closes before reading the data

3

I want to query my MySQL database by using the MySQL.Data connector. I know that I could use the Entity Framework or other handy tools similiar to ORMs e.g. Dapper. But I would like to try out the "native" way. First of all I created a base repository dealing with the the query execution

public abstract class BaseRepository
{
    private async Task<MySqlConnection> GetDatabaseConnection()
    {
        string databaseConnectionString = "connection string";
        MySqlConnection mySqlConnection = new MySqlConnection(databaseConnectionString);

        await mySqlConnection.OpenAsync();

        return mySqlConnection;
    }
    
    private async Task<DbDataReader> Execute(string commandText, Dictionary<string, object> parameterValues)
    {
        await using MySqlConnection mySqlConnection = await GetDatabaseConnection();
        
        MySqlCommand mySqlCommand = new MySqlCommand()
        {
            CommandText = commandText,
            Connection = mySqlConnection
        };

        foreach (KeyValuePair<string, object> parameterValue in parameterValues)
        {
            mySqlCommand.Parameters.AddWithValue(parameterValue.Key, parameterValue.Value);
        }
        
        await mySqlCommand.PrepareAsync();
        
        return await mySqlCommand.ExecuteReaderAsync();
    }
    
    protected async Task<TResult> Read<TResult>(string commandText, Dictionary<string, object> parameterValues, Func<DbDataReader, Task<TResult>> action)
    {
        DbDataReader dbDataReader = await Execute(commandText, parameterValues);
        
        return await action(dbDataReader);
    }
    
    protected async Task<int> Write(string commandText, Dictionary<string, object> parameterValues)
    {
        DbDataReader dbDataReader = await Execute(commandText, parameterValues);

        return dbDataReader.RecordsAffected;
    }
}

As you can see there is a Read and Modify method. Since MySQL is not able to return the inserted/updated row (as Postgres does) I only return the amount of affected rows for update and delete. I know the base repository does not support transactions yet, but ignore it for now. The following example demonstrates the usage

    public Task<User> GetUserAsync(string username)
    {
        return Read<User>(
            "SELECT * FROM person WHERE username = @username", 
            new Dictionary<string, object>()
            {
                { "username", username }
            },
            async dbDataReader =>
            {
                try
                {
                    bool recordFound = await dbDataReader.ReadAsync();
                    
                    if (!recordFound)
                    {
                        return null;
                    }
                    
                    // ...
                }
                catch (Exception e)
                {
                    throw;
                }
            });
    }

The problem is that when the debugger hits await dbDataReader.ReadAsync() I get this exception

MySql.Data.MySqlClient.MySqlException (0x80004005): Invalid attempt to Read when reader is closed.

Why does the reader close too early? How can I fix that?


Update

As already mentioned in the comments the reason is that await using MySqlConnection mySqlConnection = await GetDatabaseConnection(); closes the connection at the end of the calling method.

I could handle the whole reader logic in each repository method but I want to avoid repeating myself in each repository method.

Is there a way I can redesign the BaseRepository methods so that they won't close the connection too early?

c#
ado.net
mysql-connector
mysql.data
asked on Stack Overflow Jul 4, 2020 by Question3r • edited Aug 11, 2020 by HoldOffHunger

2 Answers

2

I think @Question3r's answer should be fine but here is a shorter solution. The Read method is redundant and the Write method runs the same logic as Read does. There should be no need for them anymore, the relevant part is the Execute method.

protected async Task<TResult> Execute<TResult>(string commandText, Dictionary<string, object> parameterValues, Func<DbDataReader, Task<TResult>> action)
{
    // ... logic remains the same ...

    return await action(dbDataReader);
}

protected async Task<int> Execute(string commandText, Dictionary<string, object> parameterValues)
{
    return await Execute<int>(commandText, parameterValues, dbDataReader => Task.FromResult(dbDataReader.RecordsAffected));
}
answered on Stack Overflow Jul 8, 2020 by Indept_Studios • edited Jul 8, 2020 by Olaf Svenson
1

So I think I found a solution that works for me. I have to work with callbacks and make Execute call a callback before closing the reader instead. So the updated code would be

private async Task<TResult> Execute<TResult>(string commandText, Dictionary<string, object> parameterValues, Func<DbDataReader, Task<TResult>> action)
{
    await using MySqlConnection mySqlConnection = await GetDatabaseConnection();
        
    MySqlCommand mySqlCommand = new MySqlCommand()
    {
        CommandText = commandText,
        Connection = mySqlConnection
    };
    
    foreach (KeyValuePair<string, object> parameterValue in parameterValues)
    {
        mySqlCommand.Parameters.AddWithValue(parameterValue.Key, parameterValue.Value);
    }
        
    await mySqlCommand.PrepareAsync();
        
    DbDataReader dbDataReader = await mySqlCommand.ExecuteReaderAsync();

    return await action(dbDataReader);
}
    
protected async Task<TResult> Read<TResult>(string commandText, Dictionary<string, object> parameterValues, Func<DbDataReader, Task<TResult>> action)
{
    return await Execute(commandText, parameterValues, action);
}
    
protected async Task<int> Write(string commandText, Dictionary<string, object> parameterValues)
{
    return await Execute(commandText, parameterValues, dbDataReader => Task.FromResult(dbDataReader.RecordsAffected));
}

What do you think about it? Improvements are highly appreciated :)

answered on Stack Overflow Jul 8, 2020 by Question3r

User contributions licensed under CC BY-SA 3.0