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 - An Overview

In previous post we briefly touched upon entity framework. Today, we will start a multi-part series on Entity Framework. In this series we will start with an overview about entity framework, then walk through creating a sample project using entity framework. We will then move on to using LINQ and Lambda Expressions.

Consider a Sales Order System with your standard normalized database. Your database schema will look something like this...

While this is a simple subset of a normalized database, imagine if you want to know which sales person sold most number of items and to which customer? You will have to write SQL query similar to this...

SELECT C.FirstName,C.LastName,Count(O.OrderID)As TotalOrders,S.FirstName,S.LastName
FROM Customers C INNER JOIN Orders O ON C.CustomerID=O.CustomerID
INNER JOIN SalesPerson S ON O.SalesPersonID=S.SalesPersonID
Group By C.FirstName,C.LastName,S.FirstName,S.LastName

This is fairly complex query for what you want to accomplish. Add the order dates or the total items per order and you can imagine how complex it will get. What if we create a view that groups this information together and you can then write a query against this view.

Although views are logical abstractions and can abstract many of the data mapping complexities, views are limited in what they can do. Further, if your database is being used for multiple applications, you may be creating multiple views and database management may become an arduous task.

Entity framework allows for abstracting the data mapping to create logical schema commonly called entity data mapping (EDM). Entities also allow for inheritance and hierarchy, giving you object relationship hierarchy as well as abstracting some of the complexities. The underlying relationships between the tables and attributes are managed in XML files, but you don't have to know the intricate details of the XML layout and schema. The logical objects will have to be mapped to the actual tables in the database obviously, but a developer doesn't have to worry about these details; framework takes care of the mappings between the logical abstraction layer and the underlying database. As far a business logic is concerned, it works with the entity data model as opposed to the database.

Entity Framework also provides facility to insert and update data without having to write an insert and update SQL queries. Further, the data is persisted in memory and any changes are tracked to ensure only changed records are updated.

Since most developers are familiar with SQL scripts, entity framework provides a very similar language to SQL that allows you to work with the EDM just as you would with database tables.

For example, if you created an EDM that wraps all the information that you need (as defined in the above query) in one object called "SalesOrderHistory" you can run a query against this object in the following fashion...

SELECT SO.CustomerName,SO.SalesPerson,Count(SO.OrderID) FROM
SalesOrderHistory AS SO Group By SO.CustomerName, SO.SalesPerson

This is very similar to SQL, but you must use alias for the entity names.

In subsequent posts we will dig deeper in this topic and see an example in action, so be sure to check back soon.

Thank you.