How to: Execute a Query Using a Stored Procedure with In and Out Parameters

This topic provides two examples of how to execute a parameterized stored procedure with the Entity Framework. The first example takes one input parameter and returns a collection of entity objects. The second example takes one input parameter and one output parameter and returns a value in the output parameter. The examples in this topic are based on the School Model. To follow these examples, add the School Model to your project and configure your project to use the Entity Framework. For more information, see How to: Use the Entity Data Model Wizard.

Example

The following code executes a GetStudentGrades stored procedure where StudentId is a required parameter. To execute this code example, import the GetStudentGrades stored procedure and specify CourseGrade entities as a return type. For information on how to import a stored procedure, see How to: Import Stored Procedures.

' Specify the Student ID. 
Dim studentId As Integer = 2

Using context As New SchoolEntities()
    For Each grade As StudentGrade In context.GetStudentGrades(studentId)
        Console.WriteLine("StudentID: " & studentId)
        Console.WriteLine("Student grade: " & grade.Grade)


    Next
End Using

// Specify the Student ID.
int studentId = 2;

using (SchoolEntities context =
    new SchoolEntities())
{
    foreach (StudentGrade grade in
              context.GetStudentGrades(studentId))
    {
        Console.WriteLine("StudentID: " + studentId);
        Console.WriteLine("Student grade: " + grade.Grade);
    }
}

The following code executes the GetDepartmentName stored procedure that returns the name of the department in the output parameter. To execute this code example:

1. add the following procedure to the School database:

   CREATE PROCEDURE [dbo].[GetDepartmentName]
         @ID int,
         @Name nvarchar(50) OUTPUT
         AS
         SELECT @Name = Name FROM Department
         WHERE DepartmentID = @ID
   

2. Update the School model from the database by adding GetDepartmentName stored procedure. For more information, see How to: Update an .edmx File when the Database Changes.

3. Import the GetDepartmentName stored procedure and specify None as a return type of this stored procedure. For more information, see How to: Import a Stored Procedure.

Using context As New SchoolEntities()
    ' name is an output parameter. 
    Dim name As New ObjectParameter("Name", GetType(String))
    context.GetDepartmentName(1, name)

    Console.WriteLine(name.Value)
End Using

using (SchoolEntities context =
    new SchoolEntities())
{
    // name is an output parameter.
    ObjectParameter name = new ObjectParameter("Name", typeof(String));
    context.GetDepartmentName(1, name);
    Console.WriteLine(name.Value);

}