How can I use Entity Framework to provide parameters to a stored procedure?

entity-framework parameters stored-procedures

Question

How can I transfer arguments from Entity Framework to a stored procedure? I appreciate it.

1
5
11/30/2011 7:48:40 PM

Accepted Answer

For which version of the Entity Framework, is the first query? .NET 4?.NET 3.5? Significant (and positive) changes have occurred. in .NET 4.

Furthermore, what do you want to accomplish is:

  • take rows out of the database

  • a stored procedure without a return value

  • Map entity INSERT, UPDATE, and DELETE actions to a stored procedure?

We need to know what you're aiming at since these three scenarios are very distinct from one another.

Additionally, if you Google (or Bing) it, you can find plenty tutorials and blog posts that will teach you how to accomplish it. Here is a short list:

and many more, literally.

Update: Okay, so you want to access the database's data. In the situation, you should:

  • navigating to your EF model (*.edmx file)
  • right-click to chooseUpdate Model from Database
  • Select the desired saved process and follow the wizard's instructions.

enter image description here

The stored process now has an entry in your physical storage model. Next:

  • visit theModel Browser (Notice the context menu up top? It's just below.Update Model from Database then locate your method in the Storage Model.
  • Right-click that action

enter image description here

  • Select Add Function Import It brings the stored method (function) from the physical storage model into the conceptual model (your entity context class, basically).

enter image description here

You have four options here:

  • If your stored procedure doesn't return anything (as in my example), you may still perform operations by calling a method on your context class.
  • Pick the relevant option from the menu if your stored procedure returns a list of scalars, such as an array of INT values, for example.
  • Your model's stored procedure may return entities, for example, fullCustomer entities - in such case, choose the entity you wish to map to by choosing the last option (your stored proc must return all columns for that entity, in this case)

OR:

  • In the event that your stored procedure produces results that are neither simply scalars (not just INT), nor an entity, you may choose option 3 and construct a new complex type (a class) that will house your stored procedure results.

Whatever you choose to do, EF will essentially generate a callable method on your object context class. Any arguments that your stored procedure needs will be the parameters of that approach, so you can send in anything like strings, ints, etc. with ease.

6
12/1/2011 5:56:53 AM

Popular Answer

Calling a stored method with numerous OUTPUT arguments is a different case. Here is an exhaustive example.

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

Observe the utilized Types (decimal.) If a different type is required, remember to update both the method argument list and theSqlDbType.XXX .



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow