c# - SqlNullValueException when executing a stored procedure with MySqlCommand

问题描述

I'm writing a C# application to retrieve recipes from a MySQL database, using Dapper for ORM. So far, I've written my DAL in C# with direct queries (Which I know is unsafe), and this works great. I've now started to transition over to stored procedures with parameters to better protect the database from SQL injection, as well as just using as close to best practice as I can.

However, when I'm using Dapper's QueryAsync<T> (This also applies to Query<T>) along with DynamicParameters, I get an exception, with the message "Data is Null. This method or property cannot be called on Null values."

However, if I either execute the query as a string literal SQL statement, or use a string literal to call the stored procedure, it works fine. I know the data is there, and not null, because it works when running it directly in MySQL with a set id number I know exists. I have also tried running the methods listed below in C# with an id I know exists, some of them work fine, some of them return the stated error.

I have no idea where this is failing once I make the QueryAsync<Recipe>("...") call. I don't know if the parameters I provide to the method are not being passed into the stored procedure, or if the procedure is returning null, or something else if going wrong.

Any help with working out where this may be failing with that call would be greatly appreciated. I've included the stack trace at the bottom, which I can't make sense of as yet. I still need to learn to understand stack traces.

Edit: I have recreated the MySql database in SQL Server, and created a new DAL connector. All exactly mirroring the MySql structure and DAL. GetRecipeByIdAsync1(int id) works exactly as expected with SQL Server. So there must be something about the way Dapper/DynamicParameters/MySql.Data is interacting with the stored procedure in MySQL

My Recipe class:

public class Recipe
{

        [Description("id")]
        public int Id { get; set; }

        [Description("name")]
        public string Title { get; set; }

        [Description("description")]
        public string Description { get; set; }

        [Description("source_site")]
        public string SourceSite { get; set; }
}

This is my recipes table in MySQL:

recipes
=============
id (pk)     | INT          | Not Null   | Auto-Increment
name        | VARCHAR(45)  | Not Null   |
description | VARCHAR(250) | Allow Null |
source_site | VARCAHR(200) | Allow Null |

This is the helper class I'm using to set the custom mapping so my columns don't need to match the property names:

public class Helper
{
    public static void SetTypeMaps()
    {
        var recipeMap = new CustomPropertyTypeMap(typeof(Recipe),
            (type, columnName) => type.GetProperties().FirstOrDefault(prop => GetDescriptionFromAttribute(prop) == columnName));

        SqlMapper.SetTypeMap(typeof(Recipe), recipeMap);

        // Other custom mappers omitted
    }

The stored procedure I'm using:

PROCEDURE `sp_recipes_GetByRecipeId`(IN RecipeId INT)
BEGIN
    SELECT r.*
    FROM recipes r
    WHERE r.id = RecipeId;
END

Now for the various versions of the method I'm using in my DAL (I've numbered them here for ease):

/// This does not work
public async Task<Recipe> GetRecipeByIdAsync1(int id)
{
    using (IDbConnection db = new MySqlConnection(GlobalConfig.CnnString("CookbookTest1")))
    {
        var p = new DynamicParameters();
        p.Add("RecipeId", id, dbType: DbType.Int32, direction: ParameterDirection.Input);

        // This is the line where the exception occurs
        var result = await db.QueryAsync<Recipe>("sp_recipes_GetByRecipeId", p, commandType: CommandType.StoredProcedure); 

        return result.FirstOrDefault();
    }

}

// This also does not work
public async Task<Recipe> GetRecipeByIdAsync2(int id)
{
    using (IDbConnection db = new MySqlConnection(GlobalConfig.CnnString("CookbookTest1")))
    {
        // This is the line where the exception occurs
        var result = await db.QueryAsync<Recipe>("sp_recipes_GetByRecipeId", new {RecipeID = id}, commandType: CommandType.StoredProcedure); 

        return result.FirstOrDefault();
    }

}

// Nor this
public async Task<Recipe> GetRecipeByIdAsync3(int id)
{
    using (IDbConnection db = new MySqlConnection(GlobalConfig.CnnString("CookbookTest1")))
    {
        // This is the line where the exception occurs
        var result = await db.QueryAsync<Recipe>("sp_recipes_GetByRecipeId", new {id}, commandType: CommandType.StoredProcedure); 

        return result.FirstOrDefault();
    }

}

// This works perfectly, but I'm not sure how safe it is
public async Task<Recipe> GetRecipeByIdAsync4(int id)
{
    using (IDbConnection db = new MySqlConnection(GlobalConfig.CnnString("CookbookTest1")))
    {
        var result = await db.QueryAsync<Recipe>($"call sp_recipes_GetByRecipeId({id})"); 

        return result.FirstOrDefault();
    }

}

// And of course, this works, but is horrible practice
public async Task<Recipe> GetRecipeByIdAsync5(int id)
{
    using (IDbConnection db = new MySqlConnection(GlobalConfig.CnnString("CookbookTest1")))
    {
        var result = await db.QueryAsync<Recipe>($"SELECT * FROM recipes WHERE recipes.id = {id}"); 

        return result.FirstOrDefault();
    }

}

Connection string if anyone wanted

<connectionStrings>
    <add name="CookbookTest1" connectionString="Server=localhost;Database=cookbook_test1;Uid=vs_dev;Pwd=developer;" providerName="MySql.Data"/>
</connectionStrings>

Stack trace:

System.Data.SqlTypes.SqlNullValueException
  HResult=0x80131931
  Message=Data is Null. This method or property cannot be called on Null values.
  Source=MySql.Data
  StackTrace:
   at MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(Int32 index, Boolean checkNull)
   at MySql.Data.MySqlClient.MySqlDataReader.GetString(Int32 i)
   at MySql.Data.MySqlClient.MySqlDataReader.GetString(String column)
   at MySql.Data.MySqlClient.SchemaProvider.GetProcedures(String[] restrictions)
   at MySql.Data.MySqlClient.ISSchemaProvider.GetProcedures(String[] restrictions)
   at MySql.Data.MySqlClient.ISSchemaProvider.GetSchemaInternal(String collection, String[] restrictions)
   at MySql.Data.MySqlClient.SchemaProvider.GetSchema(String collection, String[] restrictions)
   at MySql.Data.MySqlClient.MySqlConnection.GetSchemaCollection(String collectionName, String[] restrictionValues)
   at MySql.Data.MySqlClient.ProcedureCache.GetProcData(MySqlConnection connection, String spName)
   at MySql.Data.MySqlClient.ProcedureCache.AddNew(MySqlConnection connection, String spName)
   at MySql.Data.MySqlClient.ProcedureCache.GetProcedure(MySqlConnection conn, String spName, String cacheKey)
   at MySql.Data.MySqlClient.StoredProcedure.GetParameters(String procName)
   at MySql.Data.MySqlClient.StoredProcedure.CheckParameters(String spName)
   at MySql.Data.MySqlClient.StoredProcedure.Resolve(Boolean preparing)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)
   at Dapper.SqlMapper.<QueryAsync>d__33`1.MoveNext() in C:\projects\dapper\Dapper\SqlMapper.Async.cs:line 468
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at CookbookLibrary.DataAccess.MySqlConnector.<TestStoredProcAsync>d__5.MoveNext() in C:\Users\cyclone\Desktop\VS Projects\DigitalCookbook\CookbookLibrary\DataAccess\MySqlConnector.cs:line 119
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at DigitalCookbook.ViewModel.MainWindowModel.<TestProcedure>d__38.MoveNext() in C:\Users\cyclone\Desktop\VS Projects\DigitalCookbook\DigitalCookbook\ViewModel\MainWindowModel.cs:line 228
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at DigitalCookbook.ViewModel.MainWindowModel.<<get_TestCommand>b__31_0>d.MoveNext() in C:\Users\cyclone\Desktop\VS Projects\DigitalCookbook\DigitalCookbook\ViewModel\MainWindowModel.cs:line 114

标签： c#mysqlstored-proceduresormdapper

解决方案

这看起来像是 Oracle 的 MySQL 连接器/NET（又名MySql.Data）中的一个错误。它看起来不像我在那个错误数据库中熟悉的任何错误。它可能需要作为新问题提交。（错误 75301看起来很相似，但不是很明显这是同一个问题。）

我建议切换到MySqlConnector；它是 MySQL 的备用 ADO.NET 库，与 Dapper 具有很好的兼容性，并修复了 MySQL Connector/NET 中的许多已知错误。MySqlConnector 也有真正的异步 I/O 支持，这在 Connector/NET中没有实现；如果您想QueryAsync在代码中使用，这将很重要。

如果您想继续使用 Oracle 的 MySQL 连接器/NET，您可以通过添加CheckParameters=false连接字符串来解决该问题。请注意，这可能是对您的代码的重大更改；如果将设置设置为 false，则必须手动确保添加到每个参数的参数CommandType.StoredProcedure MySqlCommand与数据库的顺序完全相同（因为 MySql.Data 将不再为您修复它们）。

更新：查看 Connector/NET 源代码后，您的数据库似乎有一些它不期望的数据。以下两个查询中的任何一个是否产生行？如果是这样，哪些值是NULL？

SELECT * FROM information_schema.routines
WHERE specific_name IS NULL OR
    routine_schema IS NULL OR
    routine_name IS NULL OR
    routine_type IS NULL OR
    routine_definition IS NULL OR
    is_deterministic IS NULL OR
    sql_data_access IS NULL OR
    security_type IS NULL OR
    sql_mode IS NULL OR
    routine_comment IS NULL OR
    definer IS NULL;

SELECT * FROM mysql.proc
WHERE specific_name IS NULL OR
    db IS NULL OR
    name IS NULL OR
    type IS NULL OR
    body IS NULL OR
    is_deterministic IS NULL OR
    sql_data_access IS NULL OR
    security_type IS NULL OR
    sql_mode IS NULL OR
    comment IS NULL OR
    definer IS NULL;

您使用的是什么 MySQL 服务器（MySQL、MariaDB、Amazon Aurora）以及哪个版本？

c# - SqlNullValueException when executing a stored procedure with MySqlCommand

问题描述

解决方案

推荐阅读