EF Core 6 calling SP with params
Rajarajacholan Krishnamurthy
21
Reputation points
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