Walkthrough: Mapping an Entity to Stored Procedures

Article
03/06/2009

This topic shows how to use the ADO.NET Entity Data Model Designer (Entity Designer) to map the insert, update, and delete operations of an entity type to stored procedures. Insert, update, and delete operations of an entity type can use SQL statements that are automatically generated by the system (the default), or they can use stored procedures that are specified by the developer. The application code you use to create, update, and delete entities is the same whether or not you use stored procedures to update the database.

In this walkthrough, you will map two entity types to stored procedures by modifying the Entity Data Model (EDM) used in the CourseManager application (for more information, see the Prerequisites section, later in this topic). You will also write code that will insert, update, and delete entity types.

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 mapping two entity types to stored procedures.

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.

Mapping the Person Entity to Stored Procedures

When you map the insert operation of an entity to a stored procedure, if the server creates the primary key value for the inserted row, you must map this value back to the entity's key property. In this example, the InsertPerson stored procedure returns the newly created primary key as part of the stored procedure's result set. The primary key is mapped to the entity key (PersonID) using the <Add Result Bindings> feature of the Entity Designer.

To map the Person entity to stored procedures

Open the CourseManager solution in Visual Studio.
In Solution Explorer, double-click the School.edmx file.

The School.edmx file opens in the ADO.NET Entity Data Model Designer (Entity Designer).
Right-click the Person entity type and select Stored Procedure Mapping.

The stored procedure mappings appear in the Mapping Details window.
Click <Select Insert Function>.

The field becomes a drop-down list of the stored procedures included in the EDM.
Select InsertPerson from the drop-down list.

Default mappings between stored procedure parameters and entity properties appear. Note that arrows indicate the mapping direction: Property values are supplied to stored procedure parameters.
Click <Add Result Binding>.

The field becomes editable.
Replace <Add Result Binding> with NewPersonID, the name of the parameter returned by the InsertPerson stored procedure. Press Enter.

By default, NewPersonID is mapped to the entity key PersonID. Note that an arrow indicates the direction of the mapping: The value of the result column is supplied to the property.
Click <Select Update Function> and select UpdatePerson from the resulting drop-down list.

Default mappings between stored procedure parameters and entity properties appear.
Click <Select Delete Function> and select DeletePerson from the resulting drop-down list.

Default mappings between stored procedure parameters and entity properties appear.

The insert, update, and delete operations of the Person entity type are now mapped to stored procedures.

Mapping the OfficeAssignment Entity to Stored Procedures

If the entity type at one end of a one-to-one association is mapped to stored procedures, the entity at the other end of the association must also be mapped to stored procedures. In this example, we map the OfficeAssignment entity type to stored procedures because it has a one-to-one association with the Person entity type. In this mapping we use the Use Original Value option on the update operation to enable a convenient way to check concurrency in our application code.

To map the OfficeAssignment entity to stored procedures

Right-click the OfficeAssignment entity type and select Stored Procedure Mapping.

The stored procedure mappings appear in the Mapping Details window.
Click <Select Insert Function> and select InsertOfficeAssignment from the resulting drop-down list.

Default mappings between stored procedure parameters and entity properties appear.
Click <Add Result Binding>.

The field becomes editable.
Type Timestamp in place of <Add Result Binding>.
Click the empty field in the Property/Value column next to Timestamp.

The field becomes a drop-down list of properties to which we can map the value that is returned by the InsertOfficeAssignment stored procedure.
Select Timestamp from the drop-down list.
Click <Select Update Function> and select UpdateOfficeAssignment from the resulting drop-down list.

Default mappings between stored procedure parameters and entity properties appear. Checkboxes appear in the Use Original Value column next to each mapped property.
Click the empty field in the Property column that corresponds to the OrigTimestamp parameter, and select Timestamp from the resulting drop-down list.

The Entity Designer did not make this the default mapping because the parameter name did not exactly match the property name.
Check the box in the Use Original Value column that corresponds to the Timestamp property.

When an update is attempted, the value of the Timestamp property that was originally read from the database will be used when writing data back to the database. If the value does not match the value in the database, an OptimisticConcurrencyException will be thrown.
Click <Add Result Binding>.

The field becomes editable.
Replace <Add Result Binding> with Timestamp.
Click the empty field in the Property/Value column next to Timestamp.

The field becomes a drop-down list of properties to which we can map the result column that is returned by the UpdateOfficeAssignment stored procedure.
Select Timestamp from the drop-down list.
Click <Select Delete Function> and select DeleteOfficeAssignment from the resulting drop-down list.

Default mappings between stored procedure parameters and entity properties appear.

The insert, update, and delete operations of the OfficeAssignment entity type are now mapped to stored procedures.

Constructing the User Interface

Next, you will add two forms to the CourseManager application. One form provides an interface for viewing and updating instructor information. The other form provides an interface for viewing and updating office assignments.

To construct the user interface

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

The Add New Item dialog box appears.
Select Windows Form, set the name of the form to InstructorViewer.vb or InstructorViewer.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 InstructorViewer and the text is set to InstructorViewer.
Drag a DataGridView control from the Toolbox to the form and set its Name to instructorGridView in the Properties window.
Drag a Button control from the Toolbox to the form. Set its Name to updateInstructor and its Text to Update Instructor.
Drag another Button control from the Toolbox to the form. Set its Name to viewOffices and its Text to View Offices.
Right-click the CourseManager project in the Solution Explorer, point to Add, and select New Item.

The Add New Item dialog box appears.
Select Windows Form, set the name of the form to OfficeViewer.vb or OfficeViewer.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 OfficeViewer and the text is set to OfficeViewer.
Drag a ComboBox control from the Toolbox to the form and set its Name to instructorList.
Drag a TextBox control from the Toolbox to the form and set its Name to officeLocation.
Drag a Button control from the Toolbox to the form. Set its Name to updateOffice and its Text to Update Office.
In the Solution Explorer, double click CourseViewer.vb or CourseViewer.cs.

The design view of the CourseViewer form appears.
Drag a Button control from the Toolbox to the form.
In the Properties window, set the Name property of the Button to viewInstructors and set the Text property to View Instructors.
Double click the viewInstructors Button control.

The code-behind file for the CourseViewer form opens.

Add the following code to the viewInstructors_Click event handler:

Dim instructorViewer As New InstructorViewer()
instructorViewer.Visible = True

InstructorViewer instructorViewer = new InstructorViewer();
instructorViewer.Visible = true;

Return to the design view of the InstructorViewer form.
Double click the viewOffices Button control.

The code-behind file for Form2 opens.

Add the following code to the viewOffices_Click event handler:

Dim officeViewer As New OfficeViewer()
officeViewer.Visible = True

OfficeViewer officeViewer = new OfficeViewer();
officeViewer.Visible = true;

The user interface is now complete.

Viewing and Updating Instructor Information

In this procedure, you will add code to the InstructorViewer form that allows you to view and update instructor information. More specifically, the code does the following:

Binds the DataGridView to a query that returns information about Person types that are instructors. For more information about binding objects to controls, see Binding Objects to Controls (Entity Framework).
Saves any changes (inserts, updates, or deletions) in the DataGridView control to the database.
Uses the stored procedures we mapped earlier to writes data to the database when SaveChanges() is called in the updateInstructor_Click event handler.

To view and update instructor information

With the InstructorViewer form open in the form designer, double-click the InstructorViewer form.

The code-behind file for the InstructorViewer form opens.

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;

Add a property to the InstructorViewer 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;

In the InstructorViewer_Load event handler, add code to initialize the object context and set the data source for the DataGridView control to a query that returns all Person types that do not have a null HireDate.

' Initialize the ObjectContext.
schoolContext = New SchoolEntities()
Dim instructorQuery As ObjectQuery(Of Person) = _
    schoolContext.Person.Include("OfficeAssignment") _
    .Where("it.HireDate is not null") _
    .OrderBy("it.LastName")
instructorGridView.DataSource = instructorQuery _
    .Execute(MergeOption.OverwriteChanges)
instructorGridView.Columns("EnrollmentDate").Visible = False

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

    // Initialize schoolContext.
    schoolContext = new SchoolEntities();

    // Define the query to retrieve instructors.
    ObjectQuery<Person> instructorQuery = schoolContext.Person
        .Include("OfficeAssignment")
        .Where("it.HireDate is not null")
        .OrderBy("it.LastName");

    // Execute and bind the instructorList control to the query.
    instructorGridView.DataSource = instructorQuery.
        Execute(MergeOption.OverwriteChanges);
    instructorGridView.Columns["EnrollmentDate"].Visible = false;

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

Return to the design view of the InstructorViewer form and double-click the updateInstructor Button control.

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

Add code to the updateInstructor_Click event handler that saves any changes made to instructor information in the instructorGridView DataGridView control.

Dim numChanges As Integer
' Save object changes to the database, display a 
' message, and refresh the form.
numChanges = schoolContext.SaveChanges()
MessageBox.Show(numChanges.ToString() + _
    " change(s) saved to the database.")
Me.Refresh()

int numChanges;
// Save object changes to the database, display a 
// message, and refresh the form.
numChanges = schoolContext.SaveChanges();
MessageBox.Show(numChanges.ToString() +
    " change(s) saved to the database.");
this.Refresh();

Press Ctrl + F5 to run the application. Instructor information can now be viewed and updated by clicking View Instructors, making changes in table that appears, and clicking Update Instructor.

Viewing and Updating Office Information

In this procedure, you will add code to the OfficeViewer form that allows you to view and update office assignment information. More specifically, the code does the following:

Binds the ComboBox to a query that returns instructor information.
Displays office location information for the selected instructor in the TextBox.
Uses the stored procedures we mapped earlier to write data to the database when SaveChanges() is called in the updateOffice_Click event handler.

To view and update office information

With the OfficeViewer form open in the form designer, double-click the OfficeViewer form.

The code-behind file for the OfficeViewer form opens.

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;

Add a property to the OfficeViewer 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;

Add the following method to the form:

Private Sub ExecuteInstructorQuery()
    ' Define the query to retrieve instructors.
    Dim instructorQuery As ObjectQuery(Of Person) = _
        schoolContext.Person.Include("OfficeAssignment"). _
        Where("it.HireDate is not null").OrderBy("it.LastName")

    'Execute and bind the instructorList control to the query.
    'Using MergeOption.OverwriteChanges overwrites local data
    'with data from the database.
    instructorList.DisplayMember = "LastName"
    instructorList.DataSource = instructorQuery _
        .Execute(MergeOption.OverwriteChanges)
End Sub

private void ExecuteInstructorQuery()
{
    // Define the query to retrieve instructors.
    ObjectQuery<Person> instructorQuery = schoolContext.Person
        .Include("OfficeAssignment")
        .Where("it.HireDate is not null")
        .OrderBy("it.LastName");

    //Execute and bind the instructorList control to the query.
    //Using MergeOption.OverwriteChanges overwrites local data
    //with data from the database.
    instructorList.DisplayMember = "LastName";
    instructorList.DataSource = instructorQuery
        .Execute(MergeOption.OverwriteChanges);
}

This method executes a query that returns instructor information and binds the results to the instructorList ComboBox control.

In the OfficeViewer_Load event handler, add code to initialize the object context and call a method that binds the ComboBox control to a query that returns all Person types that do not have a null HireDate.
```
schoolContext = New SchoolEntities()
ExecuteInstructorQuery()
```
```
schoolContext = new SchoolEntities();
ExecuteInstructorQuery();
```
Return to the design view of the OfficeViewer form and double-click the instructorList ComboBox control.

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

Add code to the event handler that displays the selected instructor's office location in the ListBox control and disables the updateOffice Button control. This control will be enabled when a change has been made to a selected office location.

Dim instructor As Person = CType(Me.instructorList _
    .SelectedItem(), Person)

If Not instructor.OfficeAssignment Is Nothing Then
    Me.officeLocation.Text = instructor _
        .OfficeAssignment.Location.ToString()
Else
    Me.officeLocation.Text = ""
End If

' Disable the updateOffice button until a change
' has been made to the office location.
updateOffice.Enabled = False

'forceChanges.Enabled = False

Person instructor = (Person)this.instructorList.
    SelectedItem;

if (instructor.OfficeAssignment != null)
{
    this.officeLocation.Text = instructor.
        OfficeAssignment.Location.ToString();
}
else
{
    this.officeLocation.Text = "";
}

// Disable the updateOffice button until a change
// has been made to the office location.
updateOffice.Enabled = false;

//forceChanges.Enabled = false;

Return to the design view of the OfficeViewer form and double-click the updateOffice Button control.

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

Add code that saves any changes made to office information in the officeLocation TextBox control:

Try
    Dim numChanges As Integer
    Dim currentInstructor As Person = CType(Me.instructorList _
        .SelectedItem(), Person)
    If Me.officeLocation.Text <> String.Empty Then
        If Not currentInstructor.OfficeAssignment Is Nothing Then
            currentInstructor.OfficeAssignment.Location() = _
                Me.officeLocation.Text
        Else
            Dim temp(8) As Byte
            currentInstructor.OfficeAssignment = _
                OfficeAssignment.CreateOfficeAssignment( _
                currentInstructor.PersonID, _
                Me.officeLocation.Text, temp)
        End If
    Else
        schoolContext.DeleteObject(currentInstructor. _
                                   OfficeAssignment)
    End If
    numChanges = schoolContext.SaveChanges()
    MessageBox.Show(numChanges.ToString() _
            + " change(s) saved to the database.")
Catch oce As OptimisticConcurrencyException
    MessageBox.Show(oce.Message + " The conflict " & _
            "occurred on " & oce.StateEntries(0).Entity _
            .ToString() & "with key value " & _
            oce.StateEntries(0).EntityKey.EntityKeyValues(0) _
            .Value)

    'forceChanges.Enabled = True
Catch ue As UpdateException
    MessageBox.Show(ue.Message & " Click OK to retrieve " _
            & "the latest data from the database.")
    ExecuteInstructorQuery()
    Me.Refresh()
Finally
    ' Disable the updateOffice button until another
    ' change has been made to the location.
    updateOffice.Enabled = False
End Try

try
{
    int numChanges;
    Person currentInstructor = (Person)this.instructorList.
        SelectedItem;
    if (this.officeLocation.Text != string.Empty)
    {
        if (currentInstructor.OfficeAssignment != null)
        {
            currentInstructor.OfficeAssignment.Location
                = this.officeLocation.Text;
        }
        else
        {
            currentInstructor.OfficeAssignment
                = OfficeAssignment.CreateOfficeAssignment(
                currentInstructor.PersonID, this.officeLocation.Text,
                new byte[8]);
        }
    }
    else
    {
        schoolContext.DeleteObject(currentInstructor
            .OfficeAssignment);
    }
    numChanges = schoolContext.SaveChanges();
    MessageBox.Show(numChanges.ToString() +
        " change(s) saved to the database.");
}
catch (OptimisticConcurrencyException oce)
{
    MessageBox.Show(oce.Message + " The conflict "
        + "occurred on " + oce.StateEntries[0].Entity
        + " with key value " + oce.StateEntries[0].
        EntityKey.EntityKeyValues[0].Value);

    //forceChanges.Enabled = true;
}
catch (UpdateException ue)
{
    MessageBox.Show(ue.Message + " Click OK to retrieve "
        + "the latest data from the database.");
    ExecuteInstructorQuery();
    this.Refresh();
}
finally
{
    // Disable the updateOffice button until another
    // change has been made to the location.
    updateOffice.Enabled = false;
}

Return to the design view of the OfficeViewer form and double-click the officeLocation TextBox control.

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

Add code to enable the updateOffice Button control when a change has been made to the selected office location:

' Enable the udateOffice button when there is a change
' to write to the database.
updateOffice.Enabled = True

// Enable the udateOffice button when there is a change
// to write to the database.
updateOffice.Enabled = true;

The application is now complete. Press Ctrl+F5 to run the application. You can now view and update office information in the OfficeViewer form.

Handling Concurrency Conflicts

In this procedure, you will add code to the Office Viewer form that forces client changes to the database after a concurrency conflict occurs.

To handle concurrency conflicts

Double-click InstructorViewer.vb or InstructorViewer.cs in the Solution Explorer.

The form opens in the form designer.
Double-click the View Offices button.

The code-behind file for the InstructorViewer form opens.

Add the following code to the viewOffices_Click event handler so that two OfficeViewer forms will be loaded when the View Offices button is clicked.

Dim officeViewer2 As New OfficeViewer()
officeViewer2.Text = "Demonstrate Conflict"
officeViewer2.Visible = True

OfficeViewer officeViewer2 = new OfficeViewer();
officeViewer2.Text = "Demonstrate Conflict";
officeViewer2.Visible = true;

Double-click OfficeViewer.vb or OfficeViewer.cs in the Solution Explorer.

The form opens in the form designer.
Drag a Button control from the Toolbox to the form. Set its Name to forceChanges and its Text to Force Changes.
Double click the Force Changes button.

The code-behind file for the Office Viewer form opens.

Add the following code to the forceChanges_Click event handler so that changes on the client will be forced to the server or data bound to the instructorList ComboBox control will be refreshed from the database.

Dim numChanges As Integer
Dim currentInstructor As Person = CType(Me.instructorList _
    .SelectedItem(), Person)
Try
    currentInstructor.OfficeAssignment.Location = _
        Me.officeLocation.Text
    ' Using RefreshMode.ClientWins disables the
    ' optimistic concurrency check.
    schoolContext.Refresh(RefreshMode.ClientWins, _
                currentInstructor.OfficeAssignment)
    numChanges = schoolContext.SaveChanges()
    MessageBox.Show(numChanges.ToString() + _
            " change(s) saved to the database.")

    'forceChanges.Enabled = False
Catch ioe As InvalidOperationException
    MessageBox.Show(ioe.Message + " Click OK to retrieve " _
            + "the latest data from the database.")
    ExecuteInstructorQuery()
    Me.Refresh()
End Try

int numChanges;
Person currentInstructor = (Person)this.instructorList
    .SelectedItem;
try
{
    currentInstructor.OfficeAssignment.Location
                = this.officeLocation.Text;

    // Using RefreshMode.ClientWins disables the
    // optimistic concurrency check.
    schoolContext.Refresh(RefreshMode.ClientWins,
            currentInstructor.OfficeAssignment);
    numChanges = schoolContext.SaveChanges();
    MessageBox.Show(numChanges.ToString() +
        " change(s) saved to the database.");

    //forceChanges.Enabled = false;
}
catch (InvalidOperationException ioe)
{
    MessageBox.Show(ioe.Message + " Click OK to retrieve "
        + "the latest data from the database.");
    ExecuteInstructorQuery();
    this.Refresh();
}

Uncomment the forceChanges = False (Visual Basic) or forceChanges = false; (C#) line of code in the instructorList_SelectedIndexChanged event handler so that the Force Changes button will be disabled when a new instructor is selected.
Uncomment the forceChanges = True (Visual Basic) or forceChanges = true; (C#) line of code in the updateOffice_Click event handler so that the Force Changes button will be enabled when a concurrency conflict occurs.
Uncomment the forceChanges = False (Visual Basic) or forceChanges = false; (C#) line of code in the forceChanges_Click event handler so that the Force Changes button will be disabled after changes have been forced to the database.

To see the application handle a concurrency conflict, run the application (press Ctrl+F5), click View Instructors, and then click View Offices. Update an office location in the Office Viewer form, then attempt to update the same office location in the other Demonstrate Conflict form. A message box will appear notifying you of the concurrency conflict. To force the changes from the Demonstrate Conflict form to the database, click Force Changes.

Code Listing

This section contains final versions of the code-behind files for the InstructorViewer and OfficeViewer forms.

Imports System.Data.Objects
Imports System.Data.Objects.DataClasses
Public Class InstructorViewer

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

    Private Sub viewOffices_Click(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles viewOffices.Click
        Dim officeViewer As New OfficeViewer()
        officeViewer.Visible = True

        Dim officeViewer2 As New OfficeViewer()
        officeViewer2.Text = "Demonstrate Conflict"
        officeViewer2.Visible = True
    End Sub

    Private Sub InstructorViewer_Load(ByVal sender As System.Object, _
                    ByVal e As System.EventArgs) Handles MyBase.Load
        ' Initialize the ObjectContext.
        schoolContext = New SchoolEntities()
        Dim instructorQuery As ObjectQuery(Of Person) = _
            schoolContext.Person.Include("OfficeAssignment") _
            .Where("it.HireDate is not null") _
            .OrderBy("it.LastName")
        instructorGridView.DataSource = instructorQuery _
            .Execute(MergeOption.OverwriteChanges)
        instructorGridView.Columns("EnrollmentDate").Visible = False

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

    Private Sub updateInstructor_Click(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles updateInstructor.Click
        Dim numChanges As Integer
        ' Save object changes to the database, display a 
        ' message, and refresh the form.
        numChanges = schoolContext.SaveChanges()
        MessageBox.Show(numChanges.ToString() + _
            " change(s) saved to the database.")
        Me.Refresh()
    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 InstructorViewer : Form
    {
        // Create an ObjectContext instance based on SchoolEntity.
        private SchoolEntities schoolContext;

        public InstructorViewer()
        {
            InitializeComponent();
        }

        private void viewOffices_Click(object sender, EventArgs e)
        {
            OfficeViewer officeViewer = new OfficeViewer();
            officeViewer.Visible = true;

            OfficeViewer officeViewer2 = new OfficeViewer();
            officeViewer2.Text = "Demonstrate Conflict";
            officeViewer2.Visible = true;
        }

        private void InstructorViewer_Load(object sender, EventArgs e)
        {
            // Initialize schoolContext.
            schoolContext = new SchoolEntities();

            // Define the query to retrieve instructors.
            ObjectQuery<Person> instructorQuery = schoolContext.Person
                .Include("OfficeAssignment")
                .Where("it.HireDate is not null")
                .OrderBy("it.LastName");

            // Execute and bind the instructorList control to the query.
            instructorGridView.DataSource = instructorQuery.
                Execute(MergeOption.OverwriteChanges);
            instructorGridView.Columns["EnrollmentDate"].Visible = false;

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

        private void updateInstructor_Click(object sender, EventArgs e)
        {
            int numChanges;
            // Save object changes to the database, display a 
            // message, and refresh the form.
            numChanges = schoolContext.SaveChanges();
            MessageBox.Show(numChanges.ToString() +
                " change(s) saved to the database.");
            this.Refresh();
        }
    }
}

Imports System.Data.Objects
Imports System.Data.Objects.DataClasses
Public Class OfficeViewer

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

    Private Sub OfficeViewer_Load(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles MyBase.Load
        schoolContext = New SchoolEntities()
        ExecuteInstructorQuery()
    End Sub

    Private Sub instructorList_SelectedIndexChanged(ByVal sender As  _
                System.Object, ByVal e As System.EventArgs) Handles _
                instructorList.SelectedIndexChanged
        Dim instructor As Person = CType(Me.instructorList _
            .SelectedItem(), Person)

        If Not instructor.OfficeAssignment Is Nothing Then
            Me.officeLocation.Text = instructor _
                .OfficeAssignment.Location.ToString()
        Else
            Me.officeLocation.Text = ""
        End If

        ' Disable the updateOffice button until a change
        ' has been made to the office location.
        updateOffice.Enabled = False

        'forceChanges.Enabled = False
    End Sub

    Private Sub updateOffice_Click(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles updateOffice.Click
        Try
            Dim numChanges As Integer
            Dim currentInstructor As Person = CType(Me.instructorList _
                .SelectedItem(), Person)
            If Me.officeLocation.Text <> String.Empty Then
                If Not currentInstructor.OfficeAssignment Is Nothing Then
                    currentInstructor.OfficeAssignment.Location() = _
                        Me.officeLocation.Text
                Else
                    Dim temp(8) As Byte
                    currentInstructor.OfficeAssignment = _
                        OfficeAssignment.CreateOfficeAssignment( _
                        currentInstructor.PersonID, _
                        Me.officeLocation.Text, temp)
                End If
            Else
                schoolContext.DeleteObject(currentInstructor. _
                                           OfficeAssignment)
            End If
            numChanges = schoolContext.SaveChanges()
            MessageBox.Show(numChanges.ToString() _
                    + " change(s) saved to the database.")
        Catch oce As OptimisticConcurrencyException
            MessageBox.Show(oce.Message + " The conflict " & _
                    "occurred on " & oce.StateEntries(0).Entity _
                    .ToString() & "with key value " & _
                    oce.StateEntries(0).EntityKey.EntityKeyValues(0) _
                    .Value)

            'forceChanges.Enabled = True
        Catch ue As UpdateException
            MessageBox.Show(ue.Message & " Click OK to retrieve " _
                    & "the latest data from the database.")
            ExecuteInstructorQuery()
            Me.Refresh()
        Finally
            ' Disable the updateOffice button until another
            ' change has been made to the location.
            updateOffice.Enabled = False
        End Try
    End Sub

    Private Sub officeLocation_TextChanged(ByVal sender As  _
                System.Object, ByVal e As System.EventArgs) _
                Handles officeLocation.TextChanged
        ' Enable the udateOffice button when there is a change
        ' to write to the database.
        updateOffice.Enabled = True
    End Sub

    Private Sub forceChanges_Click(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles forceChanges.Click
        Dim numChanges As Integer
        Dim currentInstructor As Person = CType(Me.instructorList _
            .SelectedItem(), Person)
        Try
            currentInstructor.OfficeAssignment.Location = _
                Me.officeLocation.Text
            ' Using RefreshMode.ClientWins disables the
            ' optimistic concurrency check.
            schoolContext.Refresh(RefreshMode.ClientWins, _
                        currentInstructor.OfficeAssignment)
            numChanges = schoolContext.SaveChanges()
            MessageBox.Show(numChanges.ToString() + _
                    " change(s) saved to the database.")

            'forceChanges.Enabled = False
        Catch ioe As InvalidOperationException
            MessageBox.Show(ioe.Message + " Click OK to retrieve " _
                    + "the latest data from the database.")
            ExecuteInstructorQuery()
            Me.Refresh()
        End Try
    End Sub

    Private Sub ExecuteInstructorQuery()
        ' Define the query to retrieve instructors.
        Dim instructorQuery As ObjectQuery(Of Person) = _
            schoolContext.Person.Include("OfficeAssignment"). _
            Where("it.HireDate is not null").OrderBy("it.LastName")

        'Execute and bind the instructorList control to the query.
        'Using MergeOption.OverwriteChanges overwrites local data
        'with data from the database.
        instructorList.DisplayMember = "LastName"
        instructorList.DataSource = instructorQuery _
            .Execute(MergeOption.OverwriteChanges)
    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 OfficeViewer : Form
    {
        // Create an ObjectContext instance based on SchoolEntity.
        private SchoolEntities schoolContext;

        public OfficeViewer()
        {
            InitializeComponent();
        }

        private void OfficeViewer_Load(object sender, EventArgs e)
        {
            schoolContext = new SchoolEntities();
            ExecuteInstructorQuery();
        }

        private void instructorList_SelectedIndexChanged(object sender, 
        EventArgs e)
        {
            Person instructor = (Person)this.instructorList.
                SelectedItem;

            if (instructor.OfficeAssignment != null)
            {
                this.officeLocation.Text = instructor.
                    OfficeAssignment.Location.ToString();
            }
            else
            {
                this.officeLocation.Text = "";
            }

            // Disable the updateOffice button until a change
            // has been made to the office location.
            updateOffice.Enabled = false;

            //forceChanges.Enabled = false;
        }

        private void updateOffice_Click(object sender, EventArgs e)
        {
            try
            {
                int numChanges;
                Person currentInstructor = (Person)this.instructorList.
                    SelectedItem;
                if (this.officeLocation.Text != string.Empty)
                {
                    if (currentInstructor.OfficeAssignment != null)
                    {
                        currentInstructor.OfficeAssignment.Location
                            = this.officeLocation.Text;
                    }
                    else
                    {
                        currentInstructor.OfficeAssignment
                            = OfficeAssignment.CreateOfficeAssignment(
                            currentInstructor.PersonID, this.officeLocation.Text,
                            new byte[8]);
                    }
                }
                else
                {
                    schoolContext.DeleteObject(currentInstructor
                        .OfficeAssignment);
                }
                numChanges = schoolContext.SaveChanges();
                MessageBox.Show(numChanges.ToString() +
                    " change(s) saved to the database.");
            }
            catch (OptimisticConcurrencyException oce)
            {
                MessageBox.Show(oce.Message + " The conflict "
                    + "occurred on " + oce.StateEntries[0].Entity
                    + " with key value " + oce.StateEntries[0].
                    EntityKey.EntityKeyValues[0].Value);

                //forceChanges.Enabled = true;
            }
            catch (UpdateException ue)
            {
                MessageBox.Show(ue.Message + " Click OK to retrieve "
                    + "the latest data from the database.");
                ExecuteInstructorQuery();
                this.Refresh();
            }
            finally
            {
                // Disable the updateOffice button until another
                // change has been made to the location.
                updateOffice.Enabled = false;
            }
        }

        private void officeLocation_TextChanged(object sender, EventArgs e)
        {
            // Enable the udateOffice button when there is a change
            // to write to the database.
            updateOffice.Enabled = true;
        }

        private void forceChanges_Click(object sender, EventArgs e)
        {
            int numChanges;
            Person currentInstructor = (Person)this.instructorList
                .SelectedItem;
            try
            {
                currentInstructor.OfficeAssignment.Location
                            = this.officeLocation.Text;

                // Using RefreshMode.ClientWins disables the
                // optimistic concurrency check.
                schoolContext.Refresh(RefreshMode.ClientWins,
                        currentInstructor.OfficeAssignment);
                numChanges = schoolContext.SaveChanges();
                MessageBox.Show(numChanges.ToString() +
                    " change(s) saved to the database.");

                //forceChanges.Enabled = false;
            }
            catch (InvalidOperationException ioe)
            {
                MessageBox.Show(ioe.Message + " Click OK to retrieve "
                    + "the latest data from the database.");
                ExecuteInstructorQuery();
                this.Refresh();
            }
        }

        private void ExecuteInstructorQuery()
        {
            // Define the query to retrieve instructors.
            ObjectQuery<Person> instructorQuery = schoolContext.Person
                .Include("OfficeAssignment")
                .Where("it.HireDate is not null")
                .OrderBy("it.LastName");

            //Execute and bind the instructorList control to the query.
            //Using MergeOption.OverwriteChanges overwrites local data
            //with data from the database.
            instructorList.DisplayMember = "LastName";
            instructorList.DataSource = instructorQuery
                .Execute(MergeOption.OverwriteChanges);
        }
    }
}

Next Steps

You have successfully mapped the insert, update, and delete operations of an entity to stored procedures. 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).

Share via