C# : how to retrieve records into stored procedure using Entity Framework Core?

Question

I have created a new ASP.NET Core 2.1 web application with Angular

Visual Studio creates a project that uses the Entity Framework Core with ASP.NET Core MVC.

I have this problem: I have to read records from a stored procedure:

CREATE PROCEDURE GetEmployees
    (@PageIndex INT,
     @PageSize INT)
AS
BEGIN
    SELECT * 
    FROM employee 
    ORDER BY id 
        OFFSET @PageSize * (@PageIndex - 1) ROWS 
        FETCH NEXT @PageSize ROWS ONLY;

    SELECT COUNT(*) AS totalCount 
    FROM employee;
END

I found a question that almost solves my question, but unfortunately there is still something that does not work.

This is my code:

namespace Angular.Controllers
{
    //[Produces("application/json")]
    [Route("api/Employees")]
    public class EmployeesController : Controller
    {
        private readonly ApplicationDbContext _context;

        public EmployeesController(ApplicationDbContext context)
        {
            _context = context;
        }

        // GET: api/Employees/pageIndex/1/pageSize/1
        [HttpGet("pageIndex/{pageIndex}/pageSize/{pageSize}")]
        public Task<IActionResult> GetEmployeeP([FromRoute] int pageIndex, int pageSize)
        {
            SqlParameter pageIndexParam = new SqlParameter("@PageIndex", SqlDbType.Int);
            pageIndexParam.Value = pageIndex;

            SqlParameter pageSizeParam = new SqlParameter("@pageSize", SqlDbType.Int);
            pageSizeParam.Value = pageSize;

            // SqlParameter pageIndexParam = new SqlParameter("@PageIndex", pageIndex);
            // SqlParameter pageSizeParam = new SqlParameter("@pageSize", pageSize);

            var cmd = _context.Database.GetDbConnection().CreateCommand();
            cmd.CommandText = "GetEmployees"; // The name of the stored procedure
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            // the 2 parameters to be passed to the procedure
            var param = cmd.CreateParameter();
            param.ParameterName = "@PageIndex";
            param.Value = pageIndexParam;
            cmd.Parameters.Add(param);

            var param2 = cmd.CreateParameter();
            param2.ParameterName = "@pageSize";
            param2.Value = pageSizeParam;
            cmd.Parameters.Add(param2);

            try
            {
                // connection.Open();
                // _context.Database.GetDbConnection().Open(); // it crashes after this line
                _context.Database.OpenConnection(); // it crashes after this line
                var dr = cmd.ExecuteReader(); // ArgumentException: No mapping exists from object type System.Data.SqlClient.SqlParameter to a known managed provider native type.
                List<Employee> listEmp = new List<Employee>();

                while (dr.Read())
                {
                    // Is there a faster way to read the whole record?
                    // Or should I write a line for each field?
                    Employee emp = new Employee();
                    emp.ID = System.Int32.Parse(dr["id"].ToString());
                    emp.Fname = dr["FName"].ToString();
                    emp.email = dr["email"].ToString();
                    emp.Lname = dr["LName"].ToString();
                    listEmp.Add(emp);

                    dr.NextResult();
                }

                return Ok(listEmp);
            }
            catch (Exception ex)
            {
                // how can I return an error in json format?
                throw;
            }
        }
    }
}

The problem is in the line where the script ExecuteReader:

ArgumentException: No mapping exists from object type System.Data.SqlClient.SqlParameter to a known managed provider native type.

I use Microsoft SQL Server In the Startup.cs file, I configured the connection in this way:

services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer("Server=localhost\\SQLEXPRESS;Database=master;Trusted_Connection=True;"));

Can you help me?

Maybe I managed to pass the parameters correctly (see my solution, in the answer) but I can not extract the records

What error do you get? Perhaps the connection is already open? — DavidG, Sep 25 '18 at 12:49
ArgumentException: No mapping exists from object type System.Data.SqlClient.SqlParameter to a known managed provider native type. — Janka, Sep 25 '18 at 13:00
When creating your `SqlParameter` objects, try specifying the data type and setting the value later. For example: `var pageIndexParam = new SqlParameter("@PageIndex", SqlDbType.Int); pageIndexParam.Value = pageIndex;` — DavidG, Sep 25 '18 at 13:04
Same error? Different error? You need to learn to stop saying "it doesn't work" and give more details or nobody can help you. — DavidG, Sep 25 '18 at 13:30
I think you have to check data provider and connection string. — karunakar bhogyari, Sep 25 '18 at 13:43
Microsoft SQL Server Into Startup.cs file i have configured it in this mode: services.AddDbContext(options => options.UseSqlServer("Server=localhost\\SQLEXPRESS;Database=master;Trusted_Connection=True;")); — Janka, Sep 25 '18 at 13:44
using (var context = new SampleContext()) { var books = context.Books.FromSql("SELECT BookId, Title, AuthorId, Isbn FROM Books").ToList(); } refer this — Vivek Nuna, Sep 25 '18 at 15:13

Mohan Singh · Answer 1 · 2018-09-26T06:03:36.120

I am using Entity Framework Core in my current project and using following approach to get data through stored procedure.

First of all Create a Model with Same number and name of properties in your Stored procedure.

lets assume you are only selecting EmployeeName ,EmployeeId through your stored procedure.

Model.

 public class EmployeeModel
  {
    public string EmployeeName { get; set; }
    public int EmployeeId{ get; set; }
  }

Go to your Db Context file and register your custom model.

 public partial class ApplicationDbContext: DbContext
 {
 public virtual DbSet<EmployeeModel> EmployeeModel{ get; set; }
 protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<EmployeeModel>(entity =>
        { 
        });
        }
    }

Your Controller

 [HttpGet("pageIndex/{pageIndex}/pageSize/{pageSize}")]
    public Task<IActionResult> GetEmployeeP([FromRoute] int pageIndex, int pageSize)
    {
    List<EmployeeModel> employeeList = new List<EmployeeModel>();
    employeeList = _context.EmployeeModel.FromSql("GetEmployees 
    @PageIndex,@PageSize", 
    new SqlParameter("@PageIndex", pageIndex),
    new SqlParameter("@PageSize", pageSize)).ToList();
    return Ok(employeeList);
    }

score 0 · Answer 2 · answered Sep 25 '18 at 14:33

0

Based on what @bricelam answered here, can you try this?

     public Task<IActionResult> GetEmployeeP([FromRoute] int pageIndex, int pageSize)
    {
        var cmd = _context.Database.GetDbConnection().CreateCommand();
        cmd.CommandText = "GetEmployees";
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
        cmd.Parameters.AddWithValue("@PageSize", pageSize);

        try
        {
          //...
        }
   }

Let me know if anything happens.

answered Sep 25 '18 at 14:33

Hugo

11
5

It doesn't compile: CS1061 C# does not contain a definition for and no extension method accepting a first argument of type could be found (are you missing a using directive or an assembly reference?) See 2 lines before try – Janka Sep 25 '18 at 14:57
AddWithValue doesn't exists – Janka Sep 25 '18 at 14:58
Please never use `AddWithValue`, it is awful and potentially dangerous. https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ – DavidG Sep 25 '18 at 15:34

score 0 · Answer 3 · answered Sep 25 '18 at 15:33

Partially solved riddle:

    // GET: api/Employees/pageIndex/1/pageSize/1
    [HttpGet("pageIndex/{pageIndex}/pageSize/{pageSize}")]
    public async Task<IActionResult> GetEmployeeP([FromRoute] int pageIndex, int pageSize)
    {
        _context.Database.OpenConnection();

        var cmd = _context.Database.GetDbConnection().CreateCommand();
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        /*
         * it's the same, it also works with these lines
                    SqlParameter pageIndexParam = new SqlParameter("PageIndex", pageIndex);
                    SqlParameter pageSizeParam = new SqlParameter("PageSize", pageSize);
                    cmd.Parameters.Add(pageIndexParam);
                    cmd.Parameters.Add(pageSizeParam);
        */

        cmd.CommandText = "GetEmployees"; // The name of the stored procedure

        // the 2 parameters to be passed to the procedure
        var param = cmd.CreateParameter();
        param.ParameterName = "@PageIndex";
        param.Value = pageIndex;
        cmd.Parameters.Add(param);

        var param2 = cmd.CreateParameter();
        param2.ParameterName = "@PageSize";
        param2.Value = pageSize;
        cmd.Parameters.Add(param2);




        try
        {
            System.Data.Common.DbDataReader dr = cmd.ExecuteReader();
            List<Employee> listEmp = new List<Employee>();

            while (dr.Read())
            {
                // Is there a faster way to read the whole record?
                // Or should I write a line for each field?
                Employee emp = new Employee();
                //emp.ID = System.Int32.Parse(dr["id"].ToString());
                emp.ID = 1; //  it crashes after this line
                emp.Fname = dr["FName"].ToString(); // System.IndexOutOfRangeException: FName

                /*
                 * it doesn't see the columns :-(
                                    emp.email = dr["email"].ToString();
                                    emp.Lname = dr["LName"].ToString();
                */
                listEmp.Add(emp);
                dr.NextResult();
            }
            return Ok(listEmp);
        }
        catch (Exception ex)
        {
            // how can I return an error in json format?
            throw;
        }



    }

Now it crashes later When it tries to read the record

emp.Fname = dr["FName"].ToString();

System.IndexOutOfRangeException: FName

it doesn't see the columns :-(

Check if FName column exists in result? – Vivek Nuna Sep 25 '18 at 15:46 — Vivek Nuna, Sep 25 '18 at 15:46
You just mousehover on dr and see what it contains? – Vivek Nuna Sep 25 '18 at 17:42 — Vivek Nuna, Sep 25 '18 at 17:42

C# : how to retrieve records into stored procedure using Entity Framework Core?

3 Answers3