使用MySqlCommand执行存储过程时出现SqlNullValueException [英] SqlNullValueException when executing a stored procedure with MySqlCommand

问题描述

我正在编写一个C#应用程序，以使用Dapper for ORM从MySQL数据库检索配方.到目前为止，我已经用C#编写了直接查询的DAL(我知道这是不安全的)，而且效果很好.我现在已经开始过渡到具有参数的存储过程，以更好地保护数据库免受SQL注入的侵扰，以及尽可能地使用最佳实践.

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.

但是，当我同时使用Dapper的QueryAsync<T>(这也适用于Query<T>)和DynamicParameters时，出现了异常，消息为数据为空.无法调用此方法或属性.空值."

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."

但是，如果我将查询作为字符串文字SQL语句执行，或者使用字符串文字调用存储过程，则可以正常工作.我知道数据在那里，而不是null，因为当在MySQL中以我知道的ID号直接运行它时，它就可以工作.我还尝试过使用我知道的id在C#中运行下面列出的方法，其中一些工作正常，其中一些返回所述错误.

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.

我不知道一旦拨打QueryAsync<Recipe>("...")会在哪里失败.我不知道我提供给该方法的参数是否没有传递到存储过程中，或者该过程是否返回null，或者如果出错则返回其他信息.

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.

我已经在SQL Server中重新创建了MySql数据库，并创建了一个新的DAL连接器.所有这些都精确地映射了MySql结构和DAL. GetRecipeByIdAsync1(int id)与SQL Server完全一样地工作.因此，在Dapper/DynamicParameters/MySql.Data与MySQL中的存储过程进行交互的方式上肯定有一些东西.

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

我的食谱课:

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; }
}

这是我在MySQL中的recipes表:

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

现在，我将在DAL中使用该方法的各种版本(为方便起见，在此已对其进行编号):

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>

堆栈跟踪:

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

推荐答案

这看起来像是Oracle的MySQL Connector/NET(也称为MySql.Data)中的错误.它看起来像我在该错误数据库中不熟悉的任何错误；它可能需要作为新的问题提交. (臭虫75301 看起来很相似，但并不清楚是同一问题. )

This looks like a bug in Oracle's MySQL Connector/NET (aka MySql.Data). It doesn't look like any bug that I'm familiar with in that bug database; it might need to be filed as a new issue. (Bug 75301 looks similar but it's not immediately obvious that it's the same issue.)

我建议切换到 MySqlConnector ；它是MySQL的备用ADO.NET库，与Dapper和修复了MySQL Connector/NET中的许多已知错误. MySqlConnector还具有真正的异步I/O支持，在连接器中未实现 /网;如果您想在代码中使用QueryAsync，这将很重要.

I would recommend switching to MySqlConnector; it's an alternate ADO.NET library for MySQL that has great compatibility with Dapper and fixes many known bugs in MySQL Connector/NET. MySqlConnector also has true async I/O support, which is not implemented in Connector/NET; this will be important if you want to use QueryAsync in your code.

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

If you want to keep using Oracle's MySQL Connector/NET, you may be able to work around the problem by adding CheckParameters=false to your connection string. Note that this could be a breaking change to your code; if you set the setting to false, you'll have to manually ensure that the parameters added to each CommandType.StoredProcedure MySqlCommand are in the exact same order as the database (because MySql.Data will no longer fix them up for you).

更新:查看了Connector/NET源代码后，看来您的数据库中包含一些意外数据.以下两个查询中的任何一个都会产生行吗?如果是，NULL是哪个值?

Update: After looking at the Connector/NET source code, it appears that your database has some data it's not expecting. Does either of the following two queries produce rows? If so, which value(s) are 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 Server(MySQL，MariaDB，Amazon Aurora)以及哪个版本?

What MySQL Server are you using (MySQL, MariaDB, Amazon Aurora) and which version?

这篇关于使用MySqlCommand执行存储过程时出现SqlNullValueException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！