how to run stored procedure in Entity framework

Question

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

try this: http://www.entityframeworktutorial.net/stored-procedure-in-entity-framework.aspx — Daniel Tshuva, May 29 '17 at 09:16
Possible duplicate of [using stored procedure in entity framework](https://stackoverflow.com/questions/20970416/using-stored-procedure-in-entity-framework) — Stephen Byrne, May 29 '17 at 09:18
@DanielTshuva It is using a model which I don't want to use. i directly want to return that data in json and print it in table — , May 29 '17 at 09:19
@DanielTshuva no...... actually stored procedure is sending multiple columns but I want to return in JSON form so that ajax call can execute them. — , May 29 '17 at 09:28
if so you need to get data from the stored procedure into some model and then return the model to the client - you will get the model in json format — Daniel Tshuva, May 29 '17 at 09:32
@DanielTshuva Actually I want like there is method.... please see the edit there is a method "DropDown which is returning data from the database and taking a parameter which contains a query that is coming from ajax" i just want to execute the query and print the result on HTML screen — , May 29 '17 at 09:42

Saket Kumar · Accepted Answer · 2017-05-29T12:57:03.550

7

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.

edited May 29 '17 at 12:57

answered May 29 '17 at 10:33

Saket Kumar

4,363
4
32
55

can I pass my query parameter inside SqlQuery() method ..... as my sp is accepting the parameter and executing it. it is also showing error – May 29 '17 at 10:55
In my example shown above, @query is the parameter of stored procedure. What error are you getting after using above code? – Saket Kumar May 29 '17 at 11:05
It is not accepting Functions ... unknown "Functions" – May 29 '17 at 12:13
@sandeepgupta: I have updated the answer. Please take a look and verify. – Saket Kumar May 29 '17 at 12:58
1

1. Showing result = -1 2. Showing: - The SqlParameter is already contained by another SqlParameterCollection. 3. I don't want to use model I directly want to use json result to generate table – May 30 '17 at 05:13

Kewin Björk Nielsen · Answer 2 · 2017-05-29T10:51:16.010

0

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

edited May 29 '17 at 10:51

answered May 29 '17 at 10:27

Kewin Björk Nielsen

647
6
23

The data reader returned by the store data provider does not have enough columns for the query requested – May 29 '17 at 10:41
it is showing error in "var result = context.SP_DynamicCtrl(query);" System.InvalidOperationException: 'The specified cast from a materialized 'System.Int64' type to the 'System.String' type is not valid.' – May 30 '17 at 05:01
try with a public static string – Kewin Björk Nielsen May 30 '17 at 06:09
the result is returning casting issue – May 30 '17 at 06:12

how to run stored procedure in Entity framework

2 Answers2