Entity Framework - Using Stored Procedures

In previous post we discussed how you can use Entity Framework and use auto generated methods to perform CRUD operations. Although this may work for most simple applications, sometimes you have to have stored procedures either because database doesn't have well defined relationships and constraints or you need data dispersed in multiple tables. Similarly, you may want to use a stored procedure to insert/update multiple tables etc.

While not as straightforward as using EDM provided methods, you can still use stored procedures with entity framework. Today, we will use a SELECT stored procedure to get the results and bind it to our dropdown list just as we did in previous post.

In order to use stored procedures, first you will have to generate functions. As we reviewed in earlier post, when you generate EDM class, wizard gives you an option to select tables / views / stored procedures. Assuming you did, stored procedures are now part of the EDM class.

1. Double click on .edmx file to open it in design surface.
2. Right click anywhere on the surface then Add > Function Import

3. Name your function and select your stored procedure from the drop down.

4. If your stored procedure is not going to return anything (for example, inserting or updating a record) you can select None from Returns a Collection Of (None will return an integer value indicating whether script executed successfully or not). If your stored procedure is going to return a single value, you can select Scalar. But in most cases you will select Complex. If you select Complex you need to click on Get Column Information which will load all the columns returned by the stored procedure. Clicking on Create New Complex Type will create a new object where your stored proc. results will be loaded.

Now your stored procedure is wired up to be used in your code. The code below uses this stored procedure to return a collection and then we will bind this collection to drop down control.

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

        private void Main3_Load(object sender, EventArgs e)
        {
            orders = new OrdersEntities();
            List<SelectCustomers_Result> selectCustomers;
            selectCustomers = orders.SelectCustomers().ToList();
            
            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 = selectCustomers;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        
    }
}

When you run the project, you will see customers loaded in the drop down list

Don't worry about the grid view below. In next post we will use stored procedure to insert a new address and update an existing address.

Thank you.