By Toxic


2014-01-07 11:26:53 8 Comments

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<App_Controllers> App_Controllers { get; set; }*/
}

BaseContext:

public class BaseContext<TContext> : DbContext where TContext : DbContext
{
    static BaseContext()
    {
        Database.SetInitializer<TContext>(null);
    }

    protected BaseContext()
        : base("name = ApplicationDbConnection")
    { }
}

Function context:

public class FunctionsContext : BaseContext<FunctionsContext>
{
    public DbSet<App_Functions> Functions { get; set; }
}

6 comments

@Jason Ebersey 2018-01-03 21:18:56

Simple. Just instantiate your entity, set it to an object and pass it to your view in your controller.

enter image description here

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);
    }
}

@gsgsgs 2015-10-27 10:20:06

// 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);
}

@gsgsgs 2015-10-27 10:43:57

// 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++); }

@Fabio Turati 2015-10-27 10:45:35

If you want to change something, don't add a comment; click on "edit" instead, and apply the change directly.

@Dib 2015-08-30 08:22:48

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?

@qujck 2014-01-07 13:29:45

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();

@Toxic 2014-01-07 14:10:04

is this mean i don't need to create model for each store procedure and dbset in dbcontext??

@Toxic 2014-01-07 14:16:42

i am getting null result while debugging !!!

@qujck 2014-01-07 14:20:25

@toxic have you changed 200 to an id of a value in the database?

@qujck 2014-01-07 14:23:40

@toxic you will need an sp for each CRUD operation for each table. EF6 has much improved support for mapping between entities and stored procedures see here

@Toxic 2014-01-07 14:33:58

i am aware and have use id 2, against which i have record in database table!

@Guillaume RAYMOND 2015-06-16 12:15:12

with EF6 you can use context.Database.SqlQuery<TEntity>

@alireza amini 2015-07-24 07:54:08

thank's I LOVE YOU MAN you've helped me alot

@Antoine Pelletier 2016-06-13 17:54:08

This is the simple and direct way to do it. I love it.

@user2854731 2014-01-07 15:03:51

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<Functions>();
}

or you can also use SqlQuery

using (var entity = new FunctionsContext())
{
    var Parameter = new SqlParameter {
                     ParameterName = "FunctionId",
                     Value = 5
            };

    var DBdata = entity.Database.SqlQuery<Course>("exec GetFunctionByID @FunctionId ", Parameter).ToList<Functions>();
}

@Lin 2014-01-07 14:16:47

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<FunctionsContext>
{
    public DbSet<App_Functions> Functions { get; set; }
    public DbSet<GetFunctionByID> GetFunctionByIds {get;set;}
}

When you call your stored procedure, just see below:

var functionId = yourIdParameter;
var result =  db.Database.SqlQuery<GetFunctionByID>("GetFunctionByID @FunctionId", new SqlParameter("@FunctionId", functionId)).ToList());

@Toxic 2014-01-07 15:39:14

for some reason i am getting null value against result while debugging!

@Toxic 2014-01-07 16:11:18

many thanks lin it works....

@Lin 2014-01-07 16:11:53

I'm glad it helps.

@friggle 2014-05-14 22:46:07

This is not working for me. The fake key can't be named the same as the entity class, because it causes the error 'Member names cannot be teh same as their enclosing type'. If I leave out the key, I get a model validation error on execution because it doesn't have a key. If I name the fake key something else, it attempts to create a table.

@Chris Rosete 2015-10-01 15:45:48

How can I set the schema in my entity class? , my SP is not on dbo. I am having the error message: Could not find stored procedure 'xxx'

@seadrag0n 2015-12-21 08:32:44

@Lin i am getting error that the fake key is not present in the result set returned by the stored procedure

Related Questions

Sponsored Content

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

28 Answered Questions

[SOLVED] Fastest Way of Inserting in Entity Framework

5 Answered Questions

17 Answered Questions

[SOLVED] Entity Framework vs LINQ to SQL

22 Answered Questions

[SOLVED] Search text in stored procedure in SQL Server

25 Answered Questions

33 Answered Questions

1 Answered Questions

Entity Framework stored procedure single result set

1 Answered Questions

0 Answered Questions

Sponsored Content