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.
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
The following code executes the
GetDepartmentName stored procedure that returns the name of the department in the output parameter. To execute this code example:
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
Update the School model from the database by adding
GetDepartmentNamestored procedure. For more information, see How to: Update an .edmx File when the Database Changes.
GetDepartmentNamestored procedure and specify None as a return type of this stored procedure. For more information, see How to: Import a Stored Procedure.