CODEWITHSUNDEEP
asp.net-mvc-4
Venka Yeluri
Venka Yeluri

Reputation: 45

How to use MS Sql Stored Procedure in Asp.Net MVC4

Im using VS2012, MVC4 C#, SQL 2008 R2

I have a stored procedure which returns a list contains two computed columns. In my SP I have two parameters which I need to pass from View, these parameters will be generated from javascript on page load and stored in HiddenFields.

I have followed this Post, when I created a reference like

Rest Context = new Rest();
List<Rest> RT = Context.ExecuteStoreQuery......

I get an error.

Could someone tell me the right way to call a stored procedure and pass values from View.

Upvotes: 0

Views: 5977

Answers (1)

Venka Yeluri
Venka Yeluri

Reputation: 45

This is how I got o/p with Model, View and Controller

In my Model class, I have two .cs files, DefaultConnection.cs and Rest.cs

public class DefaultConnection : DbContext
{
    public DbSet<Rest> Restart { get; set; }
}

In Rest.cs

public class Rest
{
    //These two properties are columns in Stored Procedure, Id -PK, 
    //Distance - Computed column in SP
    public int Id { get; set; }   
    public double Distance { get; set; }
}

In Controller

Public ActionResult Get_Data()
{
    DefaultConnection Context = new DefaultConnection();
    IEnumerable<Rest> results = Context.Database.SqlQuery<Rest>
        ("Your SP @para1 = xyz, @para2 = abc") //If you have parameters or ("Just SP")
        .ToList();
    return View(results);
}

In View

@using MVC.Models
@model IEnumerable<Rest>

@{
     ViewBag.Title = "GetData";
 }

 <h2>GetData</h2>

 @foreach (Rest rt in @Model)
 {
   <table>
       <tr>
           <td>@rt.Id</td><td></td>
           <td>@rt.Distance</td>
        </tr>
   </table>

 }

Upvotes: 1

Related Questions