How to call procedure using Asp.net MVC with Entity Framework

Question

How to call procedure using Asp.net MVC with Entity Framework

Navigation

#1 by (3 votes)

4

In an asp.net mvc application, using ADO.NET with entity framework and mysql , I have a procedure , which returns a listing.

CREATE PROCEDURE SP_CATEGORIAS()
BEGIN
    select * 
      from (select a.id,
                   @rownum:=@rownum+1 as rownum,
                   case when ifnull(a.categoria_id,0) = 0
                        then a.id
                        else a.categoria_id
                   end as grupo,
                   ifnull((select b.categoria_id
                             from categoria b, (select @rownum:=0) r
                            where b.id = a.categoria_id
                              and @rownum = 1), 0) || a.categoria_id || a.id as ordem,
                    a.nome
               from categoria a) as x
      order by grupo, rownum;
END

How do I call this procedure in my asp.net code?

entity-framework asp.net-mvc

asked by anonymous 07.08.2015 / 17:55

1 answer

verify that multiple fields are created in the database How to add data to a list whenever a p: hotkey is used?

score 3 · Accepted Answer

You first have a template that represents the data returned by the procedure:

public class Categoria
{
    [Column(Name = "id", Order = 1)]
    public int ID { get; set; }

    [Column(Name = "rownum", Order = 2)]
    public int RowNumber { get; set; }

    [Column(Name = "grupo", Order = 3)]
    public int Grupo { get; set; }

    [Column(Name = "ordem", Order = 4)]
    public int Ordem { get; set; }

    [Column(Name = "nome", Order = 5)]
    public string Nome { get; set; }
}

Then you can come call your procedure as follows:

using(var context = new DataBaseContext())
{
    var resultado = context.Database.SqlQuery<Categoria>("SP_CATEGORIAS").ToList();
}

Note

I'm wondering if this stored procedure is from MySQL, if it were in SQL Server would suggest removing the sp_ prefix from it.