EF Core 6 calling SP with params

Rajarajacholan Krishnamurthy 21 Reputation points
2022-02-21T10:18:56.543+00:00

After upgrading to EF Core 6, I came to know many of the methods which had been used previously have been deprecated. Trying to call stored proc with parameter and keep getting EF6 Procedure or function expects parameter which was not supplied error though I have supplied. I tried parameter with and without the prefix @. I am using Microsoft.Data.SqlClient namespace. Inspecting the command object shows the Properties collection is populated with the parameter I have passed. Any help would be appreciated much.

        DataTable dt = null;

        List<SqlParameter> parameters = new List<SqlParameter>();

        //var parameter = CreateParameter();
        var parameter = new SqlParameter ("@strSearchString",  InvoiceNumber);
        parameters.Add(parameter);

        ExecuteSP(DGESAContext, ref dt, "usp_dg_SearchProjByInvoiceOrProj", parameters);

    internal void ExecuteSP(DGESAContext context, ref System.Data.DataTable dataTable, string sp_name, List<SqlParameter> dbparams)
    {
        DataSet dataset = null;
        DbConnection conn = context.Database.GetDbConnection();

        using (var command = conn.CreateCommand())
        {
            command.CommandText = sp_name;
            command.CommandType = CommandType.StoredProcedure;

            if (dbparams != null)
            {
                foreach (var param in dbparams)
                {
                    command.Parameters.Add(param);
                }
            }

            context.Database.OpenConnection();
            using (DataAdapter adapter = CreateDataAdapter(conn, sp_name))
            {
                dataset = new DataSet();
                adapter.Fill(dataset);
                dataTable = dataset.Tables[0];
            }
            context.Database.CloseConnection();
        }
    }

Thanks

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
694 questions
{count} vote