3

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.

4
  • Not your issues, but why are you doing this DateTime DRecEndDate = Convert.ToDateTime(RecordingEndDate) you are converting a datetime to a datetime ? Also why are you returning a string for a count which is obviously numeric Commented Mar 26, 2020 at 1:18
  • sorry, I fixed the above code. I was passing string before and then I changed to DateTime. Commented Mar 26, 2020 at 1:21
  • 1
    Are you declaring using System.Data.SqlClient or using Microsoft.Data.SqlClient;? Commented Mar 26, 2020 at 1:29
  • Try breaking it down to an minimal reproducible example. Where are your using statements? Commented Mar 26, 2020 at 1:30

1 Answer 1

13

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

Sign up to request clarification or add additional context in comments.

1 Comment

I was using System.Data.SQLClient. As soon as I changed to Microsoft.Data.SqlClient, my code started working.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.