How to pass Parameters to Stored Procedure from Entity Framework?

Question

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

Answer 1

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:

• Entity Framework with .NET 3.5 and VS 2008 SP1
• Using a Stored Procedure in Entity Framework 4
• Call Stored Procedure from Entity Framework

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

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

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

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.

Answer 2

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.