question

krisrajz avatar image
0 Votes"
krisrajz asked AgaveJoe commented

EF Core 6 calling SP with params

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

dotnet-entity-framework-core
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is CreateDataAdapter? Its input parameters are a DbConnection and the stored procedure name (string). I think you are missing the command parameters which would explain the error.

0 Votes 0 ·

0 Answers