SqlParameterCollection仅接受非null的SqlParameter类型对象，而不接受Microsoft.Data.SqlClient中的SqlParameter对象 [英] The SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameter objects in Microsoft.Data.SqlClient

问题描述

我正在尝试使用这些名称空间之一

I'm trying to use one of these namespaces

//using Microsoft.Data.SqlClient;
using System.Data.SqlClient;

我的模特

public class Get_Data_Scholar{
    [Key]
    public Int32 ID_Transcript { get; set; }
    public string Year_Semester { get; set; }
    public string Period { get; set; }
    public string Status { get; set; }
}

我的控制器

        public JsonResult Get_GPA_Tuition (Int32 id)
        {
            var ID_NCS = new SqlParameter("@ID_NCS", id);
            List<Get_data> get_Data = new List<Models.Get_Data_Scholar>();
            get_Data = _applicationDbContext.Get_Data_Scholar.FromSql(
                            "EXEC [dbo].[Get_Data_Scholar]  @ID"

                            , id
                            ).ToList();

            return Json(get_Data );
        }

当我使用System.Data.SqlClient时，一切正常.但是，当我将其他名称空间更改为Microsoft.Data.SqlClient时，它会产生如下错误消息:

When I'm using System.Data.SqlClient, everything works well. But when I change different namespace to Microsoft.Data.SqlClient, it produce error message like this:

System.InvalidCastException HResult = 0x80004002 消息= SqlParameterCollection仅接受非空的SqlParameter类型 对象，而不是SqlParameter对象. Source = System.Data.SqlClient
StackTrace:位于 System.Data.SqlClient.SqlParameterCollection.ValidateType(对象 值)在System.Data.SqlClient.SqlParameterCollection.Add(Object 值) Microsoft.EntityFrameworkCore.Storage.Internal.DynamicRelationalParameter.AddDbParameter(DbCommand 命令，对象值) Microsoft.EntityFrameworkCore.Storage.Internal.CompositeRelationalParameter.AddDbParameter(DbCommand 命令，对象值) Microsoft.EntityFrameworkCore.Storage.Internal.RelationalParameterBase.AddDbParameter(DbCommand 命令，IReadOnlyDictionary 2 parameterValues) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.CreateCommand(IRelationalConnection connection, IReadOnlyDictionary 2 parameterValues)位于 Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection 连接，DbCommandMethod executeMethod，IReadOnlyDictionary 2 parameterValues) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary 2 parameterValues)位于 Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable 1.Enumerator.BufferlessMoveNext(Boolean buffer) at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func 3操作，Func 3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable 1.Enumerator.MoveNext() 在 Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.< _TrackEntities> d__17 2.MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor 1.EnumeratorExceptionInterceptor.MoveNext() 在System.Collections.Generic.List 1.AddEnumerable(IEnumerable 1 枚举)，位于System.Linq.Enumerable.ToList [TSource](IEnumerable`1 来源)

System.InvalidCastException HResult=0x80004002 Message=The SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameter objects. Source=System.Data.SqlClient
StackTrace: at System.Data.SqlClient.SqlParameterCollection.ValidateType(Object value) at System.Data.SqlClient.SqlParameterCollection.Add(Object value) at Microsoft.EntityFrameworkCore.Storage.Internal.DynamicRelationalParameter.AddDbParameter(DbCommand command, Object value) at Microsoft.EntityFrameworkCore.Storage.Internal.CompositeRelationalParameter.AddDbParameter(DbCommand command, Object value) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalParameterBase.AddDbParameter(DbCommand command, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.CreateCommand(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.BufferlessMoveNext(Boolean buffer) at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__172.MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext() at System.Collections.Generic.List1.AddEnumerable(IEnumerable1 enumerable) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

当我使用System.Data.SqlClient时，可以绕过SQL Server的null值返回查询，而不会出现任何错误消息. 但是，当更改为Microsoft.Data.SqlClient时，必须对SQL Server的返回查询进行特殊处理.因此，我该如何/克服此错误消息? 我知道这种问题是旧样式但是由于某些返回null列，如何处理non-null SqlParameter?

When I'm using System.Data.SqlClient, return query from SQL server with null value can be bypass without any error message. But when it changes to Microsoft.Data.SqlClient, return query from SQL Server must be treated with special treatment. So, How do I do/overcome this error message ? I knew this question kind of old style but how to treat non-null SqlParameterbecause of some return null column ?

我之所以要更改Microsoft.Data.SqlClient命名空间的原因是，在不久的将来，某些敏感列将使用AKV进行加密(

The reason I'm trying to change Microsoft.Data.SqlClient namespace because near in the future some sensitive column will be encrypted using AKV (AlwaysEncrypted).

推荐答案

如果我正确地理解了您的问题，那么您就是说，当sql参数的值是原义的null时，它就爆炸了，您不希望这发生.您需要检测null，而要使用System.DBNull.Value.

If I am understanding your question correctly you're saying that when the value of your sql param is the literal null, this blows up, and you do not want this to happen. You need to detect null and instead use System.DBNull.Value.

这篇关于SqlParameterCollection仅接受非null的SqlParameter类型对象，而不接受Microsoft.Data.SqlClient中的SqlParameter对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！