We’re currently working on a .NET backend API project that retrieves data from a MySQL database and returns it to a frontend React app. As this is a migration project we’re working with a pre-existing MySQL database which given it’s currently supporting a live application we’re loath to make structural changes to.
One issue that we’ve encountered is that the database structure doesn’t really reflect how the data is actually used by the frontend application and it takes a lot of transformation in .NET or some complicated Entity Framework queries to retrieve the desired data. Some of these queries either don’t generate valid SQL or do but it’s very inefficent.
In order to get around these issues we’ve been writing a few stored procedures to extract this data as a JSON string which is then deserialized into the desired object in .NET. There doesn’t seem to be much good documentation around running MySQL stored procedures in .NET, especially around making them generic, hence this blog post.
All the examples below can be seen in a demo project here which will help to fill in the details of how they’re used.
There’s various ways of calling stored procedures from .NET but one of the easiest if you’re using Entity Framework and your data matches to an existing entity is to use the FromSqlRaw method to return the results directly as an IQueryable.
public IQueryable<T> RunQueryableStoredProcedure<T>(string storedProcedureName, Dictionary<string, object> parameters) where T : class
{
List<MySqlConnector.MySqlParameter> mySqlParameters = parameters.Select(p => new MySqlConnector.MySqlParameter(p.Key, p.Value)).ToList();
string sql = $"CALL {storedProcedureName}({string.Join(",", mySqlParameters.Select(x => $"@{x.ParameterName}"))})";
return _dbContext.Set<T>().FromSqlRaw(sql, mySqlParameters.ToArray());
}An alternative is to return a rowset from your stored procedure and to then use a function to project each row to your desired object. This is a better option than having a separate method for accessing each stored procedure individually as the code is much more generic and the mapping function can be delegated to your business logic layer.
public async IAsyncEnumerable<T> RunStoredProcedureAsync<T>(string storedProcedureName, Dictionary<string, object> parameters, Func<IDataReader, T> projection)
{
List<MySqlParameter> mySqlParameters = parameters.Select(p => new MySqlParameter(p.Key, p.Value)).ToList();
using (MySqlConnection connection = CreateSqlConnection(_connectionString))
{
using (MySqlCommand command = new MySqlCommand(storedProcedureName, connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(mySqlParameters.ToArray());
await connection.OpenAsync();
StringBuilder jsonResult = new StringBuilder();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
yield return projection(reader);
}
}
await connection.CloseAsync();
}
}
}Depending on how complex your data structure is it might make more sense to have your stored procedure generate JSON and return that to .NET. This is particually useful if you have lots of nested list objects as it avoids data duplication.
public async Task<T> RunJsonStoredProcedureAsync<T>(string storedProcedureName, Dictionary<string, object> parameters)
{
List<MySqlParameter> mySqlParameters = parameters.Select(p => new MySqlParameter(p.Key, p.Value)).ToList();
T results;
using (MySqlConnection connection = CreateSqlConnection(_connectionString))
{
using (MySqlCommand command = new MySqlCommand(storedProcedureName, connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(mySqlParameters.ToArray());
await connection.OpenAsync();
StringBuilder jsonResult = new StringBuilder();
MySqlDataReader reader = command.ExecuteReader();
if (!reader.HasRows)
{
if (typeof(T).GetGenericTypeDefinition() == typeof(List<>))
jsonResult.Append("[]");
else
jsonResult.Append("");
}
else
{
while (await reader.ReadAsync())
{
jsonResult.Append(reader.GetValue(0).ToString());
}
}
results = JsonConvert.DeserializeObject<T>(jsonResult.ToString(), new DateOnlyJsonConverter())!;
await connection.CloseAsync();
}
}
return results;
}If you’re returning multiple objects from your database it might make more sense to return an individual object per row which will allow result stream as opposed to the method above which would accept a single array of objects.
public async IAsyncEnumerable<T> RunJsonRowSetStoredProcedureAsync<T>(string storedProcedureName, Dictionary<string, object> parameters)
{
List<MySqlParameter> mySqlParameters = parameters.Select(p => new MySqlParameter(p.Key, p.Value)).ToList();
using (MySqlConnection connection = CreateSqlConnection(_connectionString))
{
using (MySqlCommand command = new MySqlCommand(storedProcedureName, connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(mySqlParameters.ToArray());
await connection.OpenAsync();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
yield return JsonConvert.DeserializeObject<T>(
(reader.GetString(0).ToString()),
new DateOnlyJsonConverter())!;
}
}
await connection.CloseAsync();
}
}
}In the above examples the stored procedure parameters are passed in as a key/value dictionary. This won’t allow you to retrieve output parameters if you’re using them so you may need to make some changes if they’re required (though I’d argue they shouldn’t be needed if you’re just mapping results directly to objects).
One issue you may have is that by default MySQL has a max length for concatinating strings of 1024 characters, if you’re stored procedure is attempting to return arrays containing more than this number of characters then the JSON will get truncated and an invalid object will be generated. The way to fix this is to increase the concat length with the following code at the start of your stored procedure.
SET SESSION group_concat_max_len = 1048576;This is session specific but it’s also possible to set this at a global level.
0 Comments