5

I want to know how to send parameters to stored procedure from entity framework? Thanks in advance.

Satish Nissankala
  • 141
  • 1
  • 3
  • 12

2 Answers2

6

First question is: for which version of the Entity Framework?? .NET 3.5? .NET 4 ?? Things have significantly changed (and improved!) in .NET 4.

And secondly: what do you want to do:

  • retrieve rows from the database

  • execute a stored proc without return value

  • map INSERT/UPDATE/DELETE operations on an entity to a stored proc??

These are three pretty different scenarios - so we need to know what you're going for.

Also: just search with Google (or Bing) - there are plenty of blog post and tutorials out there showing you how to do it - a quick list:

and literally thousands more ......

Update: ok, so you want to retrieve data from the database. In that case, your steps are:

  • go to your EF model (*.edmx file) in the designer
  • right-click and select Update Model from Database
  • pick the stored procedure you want to use and go through the wizard

enter image description here

This creates an entry for the stored procedure in your physical storage model. Next:

  • go to the Model Browser (see the above context menu? It's just below Update Model from Database), navigate to the Storage Model and find your procedure
  • right-click on that procedure

enter image description here

  • Select Add Function Import which imports the "function" (stored procedure) from the physical storage model into the conceptual model (your entity context class, basically).

enter image description here

Here, you have four choices:

  • your stored proc might not return anything (like in my sample) - then it's just a method on your context class that you can call that does something
  • your stored proc might return a collection of scalars, e.g. a list of INT values or something - pick the appropriate value in the dropdown
  • your stored proc might return entities from your model, e.g. complete Customer entities - in that case, select the last option and pick the entity you want to map to (your stored proc must return all columns for that entity, in this case)

OR:

  • your stored proc returns something - but neither just scalars (not just INT), nor an entity - in that case, you can pick the third option and define a new complex type (a class) that will hold your results returned from the stored procedure.

Whichever you do - basically EF will create a method on your object context class that you can call. Any parameters your stored proc requires will be parameters of that method so you can very easily pass in e.g. strings, ints etc.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • I am using .NET 4. My stored Procedure returns values form the data table on which I am querying. But I want to pass parameters to my stored procedure from my program. – Satish Nissankala Nov 30 '11 at 22:16
  • 1
    @marc_s You basically explained how to import an SP and work with what it returns. But you didn't really answer how to pass parameters in the code, which is the main question. – HelpASisterOut May 28 '14 at 09:33
  • @HelpASisterOut: the import basically results in a **method** on the context class of Entity Framework, so you'll even get intellisense to learn what parameters to pass, and what datatype they aer – marc_s May 28 '14 at 19:25
3

Another scenario is needing to call a stored procedure with multiple OUTPUT parameters. Below is a complete sample. 

public void MyStoredProc(int inputValue, out decimal outputValue1, out decimal outputValue2)
{
    var parameters = new[] { 
        new SqlParameter("@0", inputValue), 
        new SqlParameter("@1", SqlDbType.Decimal) { Direction = ParameterDirection.Output }, 
        new SqlParameter("@2", SqlDbType.Decimal) { Direction = ParameterDirection.Output } 
    };

    context.ExecuteStoreCommand("exec MyStoredProc @InParamName=@0, @OutParamName1=@1 output, @OutParamName2=@2 output", parameters);

    outputValue1 = (decimal)parameters[1].Value;
    outputValue2 = (decimal)parameters[2].Value;
}

Please note the Types used (decimal.) If another type is needed, remember to not only change it in the method argument list but also the SqlDbType.XXX.

Joshua
  • 4,099
  • 25
  • 37