Entity Framework - Using Stored Procedure to Insert/Update/Delete

In previous post, we talked about using SELECT stored procedure with entity framework to get the results. In this post we will discuss how you can use Insert / Update / Delete stored procedures with entity framework. There are two ways to use stored procedures - one with using the object context and calling the stored procedure, passing parameters. Second method is by associating your stored procedures with the entity, which will result in the entity data model automatically calling the stored procedures when you call "SaveChanges" method.

We will look at the same example as before.

1. Open the EDMX file and select the table you want to associate your stored procedures with, right click and select "Stored Procedure Mapping"

2. In the Mapping Details Pane, select the stored procedures for Insert, Update and Delete. Map any columns as necessary, although in most cases your columns should already be mapped appropriately. For Insert, if you are going to return the ID value back, map it to new field.

Note: For the model to work, you must associate all three functions i.e. Insert, Update and Delete.

Now, when you call .SaveChanges() method, any changes made to this table will automatically execute these stored procedures.

Here is the code.

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 EntiryFramework
{
    public partial class Main2 : Form
    {
        private OrdersEntities orders;
        public Main2()
        {
            InitializeComponent();
        }

        private void Main2_Load(object sender, EventArgs e)
        {
            orders = new OrdersEntities();
            var customerQuery = from d in orders.Customers.Include("CustomerAddresses")
                                orderby d.LastName
                                select d;
            try
            {
                // Bind the ComboBox control to the query, 
                // which is executed during data binding.
                // To prevent the query from being executed multiple times during binding, 
                // it is recommended to bind controls to the result of the Execute method. 
                this.customerList.DisplayMember = "LastName";
                this.customerList.ValueMember = "CustomerID";
                this.customerList.DataSource =
                ((ObjectQuery)customerQuery).Execute(MergeOption.AppendOnly);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        
        private void customerList_SelectedIndexChanged_1(object sender, EventArgs e)
        {
            try
            { //lets get the addresses that are associated with the selected customer
               Customer customer = (Customer)this.customerList.SelectedItem;

                Addresses.DataSource = customer.CustomerAddresses;
                Addresses.Columns["CustomerID"].Visible = false;
                Addresses.Columns["AddressID"].Visible = false;
                Addresses.Columns["Customer"].Visible = false;
                Addresses.AllowUserToDeleteRows = false;
                Addresses.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void Close_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void saveChanges_Click(object sender, EventArgs e)
        {
            try
            {

                // Save  changes to the database, 
                // display a message, and refresh the form.
                orders.SaveChanges();
                MessageBox.Show("Changes saved to the database.");
                this.Refresh();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
    }
}

Thank you.