CODEWITHSUNDEEP
c#jqueryasp.net-mvcentity-frameworkstored-procedures
user8051700
user8051700

Reputation:

how to run stored procedure in Entity framework

I have a Query in Database which is bringing another query in response using Json via Ajax. I have created the stored procedure which is accepting that query and bringing multiple columns.

I am not getting how to run stored procedure in entity framework.

Help needed.

Method:- 

public ActionResult DropDownn(string query)
    {
        using (DynamicDBEntities db = new DynamicDBEntities())
        {
            //var dbQuery = db.Database.SqlQuery<Cust_mas>(query).ToList();
            //return Json(courseList, JsonRequestBehavior.AllowGet);
        }
    }

SP:-
alter procedure [dbo].[SP_DynamicCtrl]

@query nvarchar(1000) 
As
begin
execute sp_executesql @query;
end

Upvotes: 3

Views: 10331

Answers (2)

Saket Kumar
Saket Kumar

Reputation: 4845

As per my understanding, you want to execute a stored procedure that run on multiple tables, and then return Json Data to View. You can actually do something like below:

Approach 1: (Using ExecuteSqlCommand)

SqlParameter param1 = new SqlParameter("@query", query);        
var result = db.Database.ExecuteSqlCommand("SP_DynamicCtrl @query", 
                              param1);

Approach 2: (Using Object Type on SqlQuery)

SqlParameter param1 = new SqlParameter("@query", query);
Var result = db.Database.SqlQuery<Object>("exec SP_DynamicCtrl @query", param1);

Approach 3: (Cleaner Approach)

1.) Create a model as per your return parameters from stored procedure, let's call it YourType class. 2.) Use the below code to call stored pocedure:

SqlParameter param1 = new SqlParameter("@query", query);
Var result = db.Database.SqlQuery<YourType>("exec SP_DynamicCtrl @query", param1);

After you get the result from above query, you can convert it to JSON befor returning in controller:

return Json(result, JsonRequestBehavior.AllowGet); //Typecast the result as per your need

Please modify code as per your need.

Upvotes: 7

Kewin Bj&#246;rk Nielsen
Kewin Bj&#246;rk Nielsen

Reputation: 679

if you have mapped it in the edmx try this

 public ActionResult DropDownn(string query)
    {
        using (DynamicDBEntities db = new DynamicDBEntities())
        {

       var result = context.SP_DynamicCtrl(query);
        return result.FirstOrDefault();
        }
    }

Upvotes: 0

Related Questions