Walkthrough: Mapping an Entity to Stored Procedures

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

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 (in the conceptual model) to stored procedures (in the storage model) by modifying the .edmx file 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.

Cc716679.note(en-us,VS.110).gifNote:
If you do not map all three of the insert, update, or delete operations of a entity type to stored procedures, the unmapped operations will fail if executed at runtime and an UpdateException will be thrown.

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

Cc716679.note(en-us,VS.110).gifNote:
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.

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.

Cc716679.note(en-us,VS.110).gifNote:
If you map an insert, update, or delete operation to a stored procedure that returns an integer-valued output parameter, the Rows Affected Parameter checkbox is enabled. If the checkbox is selected for a parameter and the value returned is zero when the operation is called, an OptimisticConcurrencyException will be thrown.

To map the Person entity to stored procedures

  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).

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

    The stored procedure mappings appear in the Mapping Details window.

  4. Click <Select Insert Function>.

    The field becomes a drop-down list of the stored procedures in the storage model that can be mapped to entity types in the conceptual model.

  5. 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.

  6. Click <Add Result Binding>.

    The field becomes editable.

  7. Type 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.

  8. Click <Select Update Function> and select UpdatePerson from the resulting drop-down list.

    Default mappings between stored procedure parameters and entity properties appear.

  9. 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.

In this example, we map the OfficeAssignment entity type to stored procedures. 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

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

    The stored procedure mappings appear in the Mapping Details window.

  2. Click <Select Insert Function> and select InsertOfficeAssignment from the resulting drop-down list.

    Default mappings between stored procedure parameters and entity properties appear.

  3. Click <Add Result Binding>.

    The field becomes editable.

  4. Type Timestamp.

  5. 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.

  6. Select Timestamp from the drop-down list.

  7. 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.

  8. 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.

  9. 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.

  10. Click <Add Result Binding>.

    The field becomes editable.

  11. Replace <Add Result Binding> with Timestamp.

  12. 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.

  13. Select Timestamp from the drop-down list.

  14. 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.

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

  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 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.

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

  4. Drag a Button control from the Toolbox to the form. Set its Name to updateInstructor and its Text to Update Instructor.

  5. Drag another Button control from the Toolbox to the form. Set its Name to viewOffices and its Text to View Offices.

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

    The Add New Item dialog box appears.

  7. 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.

  8. Drag a ComboBox control from the Toolbox to the form and set its Name to instructorList.

  9. Drag a TextBox control from the Toolbox to the form and set its Name to officeLocation.

  10. Drag a Button control from the Toolbox to the form. Set its Name to updateOffice and its Text to Update Office.

  11. In the Solution Explorer, double click CourseViewer.vb or CourseViewer.cs.

    The design view of the CourseViewer form appears.

  12. Drag a Button control from the Toolbox to the form.

  13. In the Properties window, set the Name property of the Button to viewInstructors and set the Text property to View Instructors.

  14. Double click the viewInstructors Button control.

    The code-behind file for the CourseViewer form opens.

  15. Add the following code to the viewInstructors_Click event handler:

    InstructorViewer instructorViewer = new InstructorViewer();
    instructorViewer.Visible = true;
    
    
  16. Return to the design view of the InstructorViewer form.

  17. Double click the viewOffices Button control.

    The code-behind file for Form2 opens.

  18. Add the following code to the viewOffices_Click event handler:

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

The user interface is now complete.

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.

  • 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

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

    The code-behind file for the InstructorViewer form opens.

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

    using System.Data.Objects;
    using System.Data.Objects.DataClasses;
    
    
  3. Add a property to the InstructorViewer class that represents the object context:

    // Create an ObjectContext instance based on SchoolEntity.
    private SchoolEntities schoolContext;
    
    
  4. 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 schoolContext.
    schoolContext = new SchoolEntities();
    
    // Define the query to retrieve instructors.
    ObjectQuery<Person> instructorQuery = schoolContext.People
        .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;
    instructorGridView.Columns["OfficeAssignment"].Visible = false;
    instructorGridView.Columns["StudentGrades"].Visible = false;
    instructorGridView.Columns["Courses"].Visible = false;
    
    
  5. 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.

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

    // Save object changes to the database, display a 
    // message, and refresh the form.
    schoolContext.SaveChanges();
    MessageBox.Show("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.

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

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

    The code-behind file for the OfficeViewer form opens.

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

    using System.Data.Objects;
    using System.Data.Objects.DataClasses;
    
    
  3. Add a property to the OfficeViewer class that represents the object context:

    // Create an ObjectContext instance based on SchoolEntity.
    private SchoolEntities schoolContext;
    
    
  4. Add the following method to the form:

    private void ExecuteInstructorQuery()
    {
        // Define the query to retrieve instructors.
        ObjectQuery<Person> instructorQuery = schoolContext.People
            .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.DataSource = instructorQuery
            .Execute(MergeOption.OverwriteChanges);
        instructorList.DisplayMember = "LastName";
    }
    
    

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

  5. 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();
    
    
  6. 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.

  7. 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.

    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;
    
    
  8. 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.

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

    try
    {
        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);
        }
        schoolContext.SaveChanges();
        MessageBox.Show("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;
    }
    
    
  10. 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.

  11. 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;
    
    

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

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

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

    The form opens in the form designer.

  2. Double-click the View Offices button.

    The code-behind file for the InstructorViewer form opens.

  3. 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.

    OfficeViewer officeViewer2 = new OfficeViewer();
    officeViewer2.Text = "Demonstrate Conflict";
    officeViewer2.Visible = true;
    
    
  4. Double-click OfficeViewer.vb or OfficeViewer.cs in the Solution Explorer.

    The form opens in the form designer.

  5. Drag a Button control from the Toolbox to the form. Set its Name to forceChanges and its Text to Force Changes.

  6. Double click the Force Changes button.

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

  7. 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.

    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);
        schoolContext.SaveChanges();
        MessageBox.Show("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();
    }
    
    
  8. 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.

  9. 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.

  10. 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.

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

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.People
                .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;
            instructorGridView.Columns["OfficeAssignment"].Visible = false;
            instructorGridView.Columns["StudentGrades"].Visible = false;
            instructorGridView.Columns["Courses"].Visible = false;
        }

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

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
            {
                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);
                }
                schoolContext.SaveChanges();
                MessageBox.Show("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)
        {
            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);
                schoolContext.SaveChanges();
                MessageBox.Show("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.People
                .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.DataSource = instructorQuery
                .Execute(MergeOption.OverwriteChanges);
            instructorList.DisplayMember = "LastName";
        }
    }
}

You have successfully mapped the insert, update, and delete operations of an entity to stored procedures. For more information about how to build applications that use the Entity Framework, see ADO.NET Entity Framework.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft