using stored procedure in entity framework

Question

I am using asp net mvc 5 and C  with Entity Framework    I have model and domain classes for function    now I need to use stored procedure     which I am struggling at the movement    I am following code first existing database and I have stored procedure written there  My question is how I can call that stored procedure in my web application    Stored procedure   ALTER PROCEDURE  dbo   GetFunctionByID        FunctionId INT   AS BEGIN     SELECT        FROM Functions As Fun     WHERE Function ID    FunctionId END   Domain class    public class Functions        public Functions                    public int Function ID   get  set        public string Title   get  set        public int Hierarchy level   get  set        Function model    Table  Functions    public class App Functions       public App Functions                     Key      public int Function ID   get  set          StringLength 50        Required      public string Title   get  set         public int Hierarchy level   get  set          public virtual ICollection lt App Controllers gt  App Controllers   get  set          BaseContext   public class BaseContext lt TContext gt    DbContext where TContext   DbContext       static BaseContext                 Database SetInitializer lt TContext gt  null              protected BaseContext             base  name   ApplicationDbConnection               Function context   public class FunctionsContext   BaseContext lt FunctionsContext gt        public DbSet lt App Functions gt  Functions   get  set

User · Answer

Mindless passenger has a project that allows you to call a stored proc from entity frame work like this....

using (testentities te = new testentities())
{
    //-------------------------------------------------------------
    // Simple stored proc
    //-------------------------------------------------------------
    var parms1 = new testone() { inparm = "abcd" };
    var results1 = te.CallStoredProc<testone>(te.testoneproc, parms1);
    var r1 = results1.ToList<TestOneResultSet>();
}

... and I am working on a stored procedure framework (here) which you can call like in one of my test methods shown below...

[TestClass]
public class TenantDataBasedTests : BaseIntegrationTest
{
    [TestMethod]
    public void GetTenantForName_ReturnsOneRecord()
    {
        // ARRANGE
        const int expectedCount = 1;
        const string expectedName = "Me";

        // Build the paraemeters object
        var parameters = new GetTenantForTenantNameParameters
        {
            TenantName = expectedName
        };

        // get an instance of the stored procedure passing the parameters
        var procedure = new GetTenantForTenantNameProcedure(parameters);

        // Initialise the procedure name and schema from procedure attributes
        procedure.InitializeFromAttributes();

        // Add some tenants to context so we have something for the procedure to return!
        AddTenentsToContext(Context);

        // ACT
        // Get the results by calling the stored procedure from the context extention method 
        var results = Context.ExecuteStoredProcedure(procedure);

        // ASSERT
        Assert.AreEqual(expectedCount, results.Count);
    }
}

internal class GetTenantForTenantNameParameters
{
    [Name("TenantName")]
    [Size(100)]
    [ParameterDbType(SqlDbType.VarChar)]
    public string TenantName { get; set; }
}

[Schema("app")]
[Name("Tenant_GetForTenantName")]
internal class GetTenantForTenantNameProcedure
    : StoredProcedureBase<TenantResultRow, GetTenantForTenantNameParameters>
{
    public GetTenantForTenantNameProcedure(
        GetTenantForTenantNameParameters parameters)
        : base(parameters)
    {
    }
}

If either of those two approaches are any good?

User · Answer

Simple  Just instantiate your entity  set it to an object and pass it to your view in your controller     Entity     VehicleInfoEntities db   new VehicleInfoEntities      Stored Procedure      dbo prcGetMakes       or  you can add any parameters in your stored procedure inside the brackets        dbo prcGetMakes  BMW       Controller  public class HomeController   Controller       VehicleInfoEntities db   new VehicleInfoEntities         public ActionResult Index                 var makes   db prcGetMakes null            return View makes

User · Answer

Add some tenants to context so we have something for the procedure to return          AddTenentsToContext Context           ACT        Get the results by calling the stored procedure from the context extention method      var results   Context ExecuteStoredProcedure procedure           ASSERT     Assert AreEqual expectedCount  results Count

User · Answer

After importing stored procedure  you can create object of stored procedure pass the parameter like function  using  var entity   new FunctionsContext         var DBdata   entity GetFunctionByID 5  ToList lt Functions gt         or you can also use SqlQuery  using  var entity   new FunctionsContext          var Parameter   new SqlParameter                        ParameterName    FunctionId                        Value   5                     var DBdata   entity Database SqlQuery lt Course gt   exec GetFunctionByID  FunctionId    Parameter  ToList lt Functions gt

User · Answer

You need to create a model class that contains all stored procedure properties like below  Also because Entity Framework model class needs primary key  you can create a fake key by using Guid   public class GetFunctionByID        Key      public Guid  GetFunctionByID   get  set            All the other properties      then register the GetFunctionByID model class in your DbContext   public class FunctionsContext   BaseContext lt FunctionsContext gt        public DbSet lt App Functions gt  Functions   get  set        public DbSet lt GetFunctionByID gt  GetFunctionByIds  get set       When you call your stored procedure  just see below    var functionId   yourIdParameter  var result    db Database SqlQuery lt GetFunctionByID gt   GetFunctionByID  FunctionId   new SqlParameter   FunctionId   functionId   ToList

User · Answer

You can call a stored procedure using SqlQuery  See here      Prepare the query var query   context Functions SqlQuery       EXEC  dbo   GetFunctionByID   p1        new SqlParameter  p1   200        add NoTracking   if required     Fetch the results var result   query ToList

[asp.net-mvc] using stored procedure in entity framework

Examples related to asp.net-mvc

Examples related to entity-framework

Examples related to stored-procedures

Examples related to repository-pattern