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, another here — zed
– zed, Commented Jul 29, 2015 at 20:13

Harshit Gindra · Accepted Answer · 2015-07-29 21:07:45Z

1

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.

answered Jul 29, 2015 at 21:07

Harshit Gindra

3654 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to call stored procedure with MVC and Entity Framework

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related