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?
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));
}
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 :)
User contributions licensed under CC BY-SA 3.0