Entity Framework - Adding / Updating Records

In previous post we reviewed Entity Framework and how you can use Linq to entity to get the data from the database and bind it to a control.


In this post we will use the same project but go one step further - insert and update data using entity framework. Although this is rather simple example, you can still see the power of entity framework and how easy it is to perform database operations without writing much code.


In this example, instead of using read only text fields to load the address, we will load the addresses in a gridview which will allow us to add another address or edit an existing address.


I am using the same project from the previous post, so I already have entity framework objects configured. I created a new form with two controls - Combobox and a Gridview. When you run this project, combobox will load all the customers and selecting a name will load the selected customer's address(es) in the gridview

.
Running the Project

My grid is editable, so I can update an existing address or enter a new address. Once I modify an existing address or add a new address and click on Update, the code behind this button will save my changes and give me a confirmation message.

Let's review 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);
            }

        }
    }
}

Notice the code under "saveChanges_Click" method. orders.SaveChanges() is all it takes to save your changes - whether modifications to an existing address or adding new addresses.


For this to work, be sure your tables have a primary key. Without a primary key, this will not work. Also, you may get an error if your entity is mapped to a customized query or a view rather than a table.


Entity Framework allows you to map your database schema and have the framework generate the plumbing for you. But for this to truly work, you must have proper keys/referential integrity in your database tables and relationships between them.


Thank you.

Entity Framework - Part II

In previous post we created a simple winform project and then generated an entity framework data model (EDM) using a small Orders database. Today, we will use the same project and query the data using entity framework from the underlying database and bind it to an element. If you haven't reviewed previous post, I strongly suggest you do so now.

In this example, we will use some of the built in methods such as List to get a list of customers and bind it to the dropdown list.

We will also use Linq to entities to get a list of addresses that are associated with the selected customer and load the address in read only text fields.

Example - 

using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Objects;
using System.Data.Objects.DataClasses;

namespace EntiryFramework
{
    public partial class Main : Form
    {
        private OrdersEntities orders;
        public Main()
        {
            InitializeComponent();
        }

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

        private void Main_Load(object sender, EventArgs e)
        {

            //Initialize the ObjectContext
            orders = new OrdersEntities();

            //Load all customers 

            List<Customer> customerList = orders.Customers.ToList();
            try
            {
                //bind to the database
                this.customerList.DisplayMember = "LastName";
                this.customerList.ValueMember = "CustomerID";
                this.customerList.DataSource = customerList;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }



        }

        private void customerList_SelectedIndexChanged(object sender, EventArgs e)
        {
            //lets get the addresses that are associated with the selected customer
            int customerID = (int)this.customerList.SelectedValue;
           
            IQueryable<CustomerAddress> customerAddress = orders.Customers
                .Where(d => d.CustomerID == customerID)
                .SelectMany(d => d.CustomerAddresses);
            foreach (CustomerAddress address in customerAddress)
            {
                txtAddress1.Text = address.Address1;
                txtAddress2.Text = address.Address2;
                txtCity.Text = address.City;
                txtState.Text = address.State;
                txtZip.Text = address.ZipCode;
            }
           
        }
    }
}

When you run this application, you will see something like this and as you change the customer name in the drop down list, the address will change.

In this example I used the List() method to load all the customers from the database and then used Linq to entities to filter the list of addresses for the selected customer. I could have used Linq to entities to load all customers, doing something like this...

var customerQuery = from d in orders.Customers
                                  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);
            }

In the next post we will update a customer using entity framework.

Thank you.