CODEWITHSUNDEEP
c#entity-frameworkentity-framework-core
Anjali
Anjali

Reputation: 2698

The SqlParameterCollection only accepts non-null Parameter type objects

I am trying to use stored procedures in Entity Framework Core. When executing a stored procedure, I am passing two input parameters and one output parameter. I keep getting this error:

The SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameter objects.

This is my code:

public string InsertCardsData(DateTime RecordingStartDate, DateTime RecordingEndDate)
{
        try
        {
            // DateTime DRecEndDate = Convert.ToDateTime(RecordingEndDate);
            DateTime DRecEndDate = RecordingEndDate.AddDays(1);

            var RecordStartDate = new SqlParameter
            {
                ParameterName = "RecordingStartDate",
                Value = RecordingStartDate,
                Direction = ParameterDirection.Input
            };

            var RecordEndDate = new SqlParameter
            {
                ParameterName = "RecordingEndDate",
                Value = DRecEndDate,
                Direction = ParameterDirection.Input
            };

            var RecordCount = new SqlParameter
            {
                ParameterName = "RecLoadCount",
                Direction = ParameterDirection.Output
            };

            var SQL = "Exec Recload_InsertPrimeExtract @RecordingStartDate, @RecordingEndDate, @RecLoadCount OUT";
            var result = _context.Database.ExecuteSqlRaw(SQL, RecordStartDate, RecordEndDate, RecordCount);
            var RecordCountValue = RecordCount.Value.ToString();

            return RecordCountValue;
        }
        catch (Exception ex)
        {
            return "";
        }
}

I will put a a meaningful catch statement, but right now, I am putting a breakpoints in catch statement and the above error occurs.

Any help will be highly appreciated.

Upvotes: 3

Views: 6549

Answers (1)

TheGeneral
TheGeneral

Reputation: 81493

Most likely your issues is caused due to a breaking change in EF Core 3, by declaring using System.Data.SqlClient when it should beusing Microsoft.Data.SqlClient

Why

Microsoft.Data.SqlClient is the flagship data access driver for SQL Server going forward, and System.Data.SqlClient no longer be the focus of development. Some important features, such as Always Encrypted, are only available on Microsoft.Data.SqlClient.

Further More

If your code takes a direct dependency on System.Data.SqlClient, you must change it to reference Microsoft.Data.SqlClient instead; as the two packages maintain a very high degree of API compatibility, this should only be a simple package and namespace change.

Related issues on github

Upvotes: 13

Related Questions