How to call stored procedure with MVC and Entity Framework

Question

I am learning entity framework and MVC. I have the following old code using SQL Adapter to connect to database and call a stored procedure to fetch data.
How should I refactor it properly using Entity Framework? Thanks.

DataSet ds = new DataSet();
string connstr = ConfigurationManager.AppSettings["isr1DSN"];

using (SqlConnection con = new SqlConnection(connstr))
{
    con.Open();
    using (SqlCommand cmd = new SqlCommand("uspGetAssetUses", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@BaseCode", SqlDbType.VarChar, 5).Value = baseCode;
        cmd.Parameters.Add("@Scope", SqlDbType.VarChar, 6).Value = scopeID;
        cmd.Parameters.Add("@SortColumn", SqlDbType.VarChar, 20).Value = field;
        cmd.Parameters.Add("@Direction", SqlDbType.VarChar, 4).Value = direction;
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            sda.Fill(ds);
        }
    }
}

It's been covered several times, a sample [here](http://stackoverflow.com/a/4260465/1548894), another [here](http://stackoverflow.com/a/20973919/1548894) — zed, Jul 29 '15 at 20:13

score 1 · Answer 1 · answered Jul 29 '15 at 21:07

Accessing storedProcedure is same as accessing the model class of the table. Assuming that you're taking the database to code approach which will create all the class files for you(also for Stored Procedure)

public ActionList FetchData(){
using (var context = new DBEntities()){
var results= context.uspGetAssetUses("Pass all your parameters serially in sequence").toList();
return View(results);
}
}

The stored procedure class should have the getters and setters of column names which are returned by the stored procedure.

namespace Models.uspGetAssetUses
{
    using System;

public partial class uspGetAssetUses
{
    public int BaseCode { get; set; }
    public string Scope { get; set; }
    public string Sort { get; set; }
    public string direction { get; set; }
    }
}

I hope this suffice your question.

How to call stored procedure with MVC and Entity Framework

1 Answers1