Execute stored procedure in ASP.NET MVC and LINQ

Question

I'm very new to ASP.NET, so this may be a very basic question. Anyway, I want to execute a stored procedure on a server. I'm using, as in the heading mentioned, ASP.NET MVC. Currently it is possible to query the whole table:

Controller:

public JsonResult GetLaptopsJson()
{
    Laptop[] laptops = context.GetTable<Laptop>().ToArray();
    return Json(laptops, JsonRequestBehavior.AllowGet);
}

Javascript:

$.ajax({
            url: '/Home/GetLaptopsJson',
            contentType: 'application/html; charset=utf-8',
            type: 'GET',
            dataType: 'json' 
       })
       .success(function (result) {
            $('#search_result').empty();
            for (var i = 0; i <= (result.length - 1); i++)
            {
                add_result(result[i].Id, result[i].Price);
            }
        })
        .error(function (xhr, status) { 
             alert(status);

        });

Model:

[Table(Name = "Laptop")]
public class Laptop
{
    [Column(IsPrimaryKey = true)]
    public int Id { get; set; }
    [Column]
    public string Name { get; set; }
    [Column]
    public decimal Price { get; set; }
}

If I change the controller to following, it returns nothing:

var laptops = context.ExecuteQuery<Laptop>(@"exec [dbo].[sp_Select_Laptop]");

Also if I change the type from laptop to object, it returns nothing. This leads me to the fact, that I misunderstood something fundamental.

Thanks in advance guys!

EDIT1:

Stored procedure code:

CREATE PROC [dbo].[sp_Select_Laptop]
AS
    SET NOCOUNT ON

    SELECT 
        [ID], [Name], [Price]
    FROM 
        [dba].[dbo].[Laptop]
GO

Can you provide `[dbo].[sp_Select_Laptop]` code, also are you sure the SP itself returns needed values? — Alex Sikilinda, Aug 23 '16 at 11:29
yes, it returns the needed values. tested in the ssm-studio. — yesfabime, Aug 23 '16 at 11:33
Use `context.Database.SqlQuery(...)`, see [this question](http://stackoverflow.com/questions/4873607/how-to-use-dbcontext-database-sqlquerytelementsql-params-with-stored-proced) — Maarten, Aug 23 '16 at 11:38
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Aug 23 '16 at 11:40

score 0 · Accepted Answer · edited May 23 '17 at 11:51

0

Thanks to Maarten and this answer.

Following changes are necessary:

Controller:

DbContext dbcontext;
public HomeController()
{
    this.dbcontext = new DbContext(Connections.connection);
}

var laptops = dbcontext.Database.SqlQuery<Laptop>(@"exec [dbo].[sp_Select_Laptop]");

edited May 23 '17 at 11:51

Community

1
1

answered Aug 23 '16 at 12:10

yesfabime

814
1
12
27

Execute stored procedure in ASP.NET MVC and LINQ

1 Answers1