Share via

Walkthrough: Retrieving Entity Types with a Stored Procedure

  • Article

This topic shows how to use a stored procedure to retrieve a collection of entity types. In this walkthrough you will use the ADO.NET Entity Data Model Designer (Entity Designer) to import a stored procedure and create a Function Import that returns a collection of entity types.

Including a stored procedure in your Entity Data Model (EDM) allows you to call the stored procedure from your application code. When a stored procedure is added to the conceptual model, it is called a Function Import. A Function Import can return collections of simple types or entity types, or no value.

Note

For a Function Import to return an EntityType, the columns returned by the corresponding stored procedure must exactly match the properties of the returned EntityType.

When the Entity Data Model Wizard generates an EDM from a database, it creates entries in the storage model for each stored procedure in the database. Corresponding entries are added to the conceptual model when a Function Import is created. For more information about creating a Function Import, see How to: Import a Stored Procedure.

Prerequisites

To complete this walkthrough, you must build the CourseManager application. For more information and instructions, see the Entity Framework Quickstart. After you build this application, you will modify its EDM by creating a Function Import based on the GetStudentGrades stored procedure.

Note

Because many of the walkthrough topics in this documentation use the CourseManager application as a starting point, we recommend that you use a copy of the CourseManager application for this walkthrough, instead of editing the original CourseManager code.

This walkthrough assumes that the reader has basic competency with Visual Studio, the .NET Framework, and programming in either Visual C# or Visual Basic.

Creating a Function Import

In this procedure you will create a Function Import based on the GetStudentGrades stored procedure that is included in the storage model of the CourseManager EDM.

To create a function import

  1. Open the CourseManager solution in Visual Studio.

  2. In Solution Explorer, double-click the School.edmx file.

    The School.edmx file opens in the ADO.NET Entity Data Model Designer (Entity Designer) and the Model Browser window opens.

  3. Expand the EntityContainer: SchoolEntities node in the Model Browser window.

    Entity Sets, Association Sets, and Function Imports folders are visible in the tree view.

  4. Right-click Function Imports and select Create Function Import.

    The New Function Import dialog box opens.

  5. Select GetStudentGrades from the Stored Procedure Name drop-down list.

  6. Type GetStudentGrades in the Function Import Name text box.

  7. Select CourseGrade from the Return Type drop-down list.

    Note

    We can set the return type to CourseGrade because the columns that are returned by the GetStudentGrades stored procedure (EnrollementID and Grade) exactly match the scalar properties of the CourseGrade entity type.

  8. Click OK.

    The GetStudentGrades Function Import is added to the EDM.

Constructing the User Interface

In this procedure you will add to the user interface of the CourseManager application so that grades for a selected student can be viewed.

To construct the user interface

  1. Right-click the CourseManager project in the Solution Explorer, point to Add, and select New Item.

    The Add New Item dialog box appears.

  2. Select Windows Form, set the name of the form to GradeViewer.vb or GradeViewer.cs, and click Add.

    A new form is added to the project and opens in the form designer. The name of the form is set to GradeViewer and the text is set to GradeViewer.

  3. Drag a ComboBox control from the Toolbox to the form and set its Name to studentList in the Properties window.

  4. Drag a DataGridView control from the Toolbox to the form and set its Name to gradeGridView in the Properties window.

  5. Double click the CourseViewer.vb or CourseViewer.cs file in Solution Explorer.

    The file opens in the form designer.

  6. Drag a Button control to the form. Set its Name to viewGrades and its Text to View Grades.

  7. Double click the viewGrades Button control.

    The viewGrades_Click event handler is added to the code-behind file.

  8. Add the following code to the viewGrades_Click event handler:

    Dim gradeViewer As New GradeViewer()
gradeViewer.Visible = True

    GradeViewer gradeViewer = new GradeViewer();
gradeViewer.Visible = true;

The user interface is now complete.

Retrieving Entity Types with a Stored Procedure

In this procedure you will add code that executes the Function Import that you previously created from the GetStudentGrades stored procedure. The code then binds the returned EntityType collection to a DataGridView control. For more information about binding objects to controls, see Binding Objects to Controls (Entity Framework).

To retrieve entity types with a stored procedure

  1. With the GradeViewer form open in the form designer, double-click the body of the form.

    The code-behind file for the GradeViewer form opens.

  2. Add the following using (C#) or Imports (Visual Basic) statements:

    Imports System.Data.Objects
Imports System.Data.Objects.DataClasses

    using System.Data.Objects;
using System.Data.Objects.DataClasses;

  3. Add a property to the GradeViewer class that represents the object context:

    ' Create an ObjectContext instance based on SchoolEntity.
Private schoolContext As SchoolEntities

    // Create an ObjectContext instance based on SchoolEntity.
private SchoolEntities schoolContext;

  4. In the GradeViewer_Load event handler, add the following code. This code initializes the object context and sets the data source for the ComboBox control to a query that returns all Person types that do not have a null EnrollmentDate.

    ' Initialize schoolContext.
schoolContext = New SchoolEntities()

' Define the query to retrieve students.
Dim studentQuery As ObjectQuery(Of Person) = schoolContext _
    .Person.Where("it.EnrollmentDate is not null") _
    .OrderBy("it.LastName")

' Execute and bind the studentList control to the query.
studentList.DisplayMember = "LastName"
studentList.DataSource = studentQuery _
    .Execute(MergeOption.OverwriteChanges)

    schoolContext = new SchoolEntities();

// Define the query to retrieve students.
ObjectQuery<Person> studentQuery = schoolContext.Person
    .Where("it.EnrollmentDate is not null")
    .OrderBy("it.LastName");

// Execute and bind the studentList control to the query.
studentList.DisplayMember = "LastName";
studentList.DataSource = studentQuery
    .Execute(MergeOption.OverwriteChanges);

  5. Return to the design view of the GradeViewer form and double-click the studentList ComboBox control.

    The studentList_SelectedIndexChanged event handler is added to the code-behind file.

  6. Add the following code to the studentList_SelectedIndexChanged event handler. This code executes the GetStudentGrades Function Import and binds the results to the DataGridView control when a new student is selected from the drop-down list.

        ' Get the selected student so we can use the
    ' PersonID in the function import call.
    Dim currentStudent As Person = CType(Me.studentList _
        .SelectedItem(), Person)

    ' Set the data source for the gradeGridView
    ' to the results returned by the GetStudentGrades
    ' Function Import.
    gradeGridView.DataSource = schoolContext _
        .GetStudentGrades(currentStudent.PersonID)

' Hide columns bound to navigation properties of CourseGrade.
    gradeGridView.Columns("Course").Visible = False
    gradeGridView.Columns("Person").Visible = False

        // Get the selected student so we can use the
    // PersonID in the function import call.
    Person currentStudent = (Person)this.studentList
        .SelectedItem;

    // Set the data source for the gradeGridView
    // to the results returned by the GetStudentGrades
    // Function Import.
    gradeGridView.DataSource = schoolContext
        .GetStudentGrades(currentStudent.PersonID);

// Hide columns bound to navigation properties of CourseGrade.
    gradeGridView.Columns["Course"].Visible = false;
    gradeGridView.Columns["Person"].Visible = false;

Press Ctrl + F5 to run the application. You can now view student grade information can by clicking View Grades and selecting a student from the drop-down list in the Grade Viewer form.

Code Listing

This section contains the final version of the code-behind file for the GradeViewer form.

Imports System.Data.Objects
Imports System.Data.Objects.DataClasses
Public Class GradeViewer
    ' Create an ObjectContext instance based on SchoolEntity.
    Private schoolContext As SchoolEntities

    Private Sub GradeViewer_Load(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles MyBase.Load
        ' Initialize schoolContext.
        schoolContext = New SchoolEntities()

        ' Define the query to retrieve students.
        Dim studentQuery As ObjectQuery(Of Person) = schoolContext _
            .Person.Where("it.EnrollmentDate is not null") _
            .OrderBy("it.LastName")

        ' Execute and bind the studentList control to the query.
        studentList.DisplayMember = "LastName"
        studentList.DataSource = studentQuery _
            .Execute(MergeOption.OverwriteChanges)

    End Sub

    Private Sub studentList_SelectedIndexChanged(ByVal sender As  _
        System.Object, ByVal e As System.EventArgs) Handles _
        studentList.SelectedIndexChanged
        ' Get the selected student so we can use the
        ' PersonID in the function import call.
        Dim currentStudent As Person = CType(Me.studentList _
            .SelectedItem(), Person)

        ' Set the data source for the gradeGridView
        ' to the results returned by the GetStudentGrades
        ' Function Import.
        gradeGridView.DataSource = schoolContext _
            .GetStudentGrades(currentStudent.PersonID)

    ' Hide columns bound to navigation properties of CourseGrade.
        gradeGridView.Columns("Course").Visible = False
        gradeGridView.Columns("Person").Visible = False
    End Sub
End Class

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Objects;
using System.Data.Objects.DataClasses;

namespace CourseManager
{
    public partial class GradeViewer : Form
    {
        // Create an ObjectContext instance based on SchoolEntity.
        private SchoolEntities schoolContext;

        public GradeViewer()
        {
            InitializeComponent();
        }

        private void GradeViewer_Load(object sender, EventArgs e)
        {
            schoolContext = new SchoolEntities();

            // Define the query to retrieve students.
            ObjectQuery<Person> studentQuery = schoolContext.Person
                .Where("it.EnrollmentDate is not null")
                .OrderBy("it.LastName");

            // Execute and bind the studentList control to the query.
            studentList.DisplayMember = "LastName";
            studentList.DataSource = studentQuery
                .Execute(MergeOption.OverwriteChanges);
        }

        private void studentList_SelectedIndexChanged(object sender, EventArgs e)
        {
            // Get the selected student so we can use the
            // PersonID in the function import call.
            Person currentStudent = (Person)this.studentList
                .SelectedItem;

            // Set the data source for the gradeGridView
            // to the results returned by the GetStudentGrades
            // Function Import.
            gradeGridView.DataSource = schoolContext
                .GetStudentGrades(currentStudent.PersonID);

        // Hide columns bound to navigation properties of CourseGrade.
            gradeGridView.Columns["Course"].Visible = false;
            gradeGridView.Columns["Person"].Visible = false;
        }
    }
}

Next Steps

You have successfully created a Function Import that retrieves a collection of entity types. For more information about support for stored procedures in the Entity Framework, see Stored Procedure Support (Entity Framework). For more information about how to build applications that use the Entity Framework, see Programming Guide (Entity Framework).

See Also

Other Resources

ADO.NET Entity Data Model Designer Scenarios
Entity Data Model Tools Tasks