Click here to Skip to main content
15,671,597 members
Articles / Desktop Programming / WPF
Article
Posted 22 Jul 2016

Stats

44.9K views
3.6K downloads
21 bookmarked

A master-detail view with Entity Framework

Rate me:
Please Sign up or sign in to vote.
4.67/5 (7 votes)
22 Jul 2016CPOL3 min read
In this article, I will create a master-detail view using the Entity Framework.

Table of contents

Introduction

In this article, I will discuss the usage of the Entity Framework to create a database connected WPF application. I created two example applications. The first example shows how you can create a master-detail view using the Entity Framework. This sample application connects to a SQL Server 2012 and retrieves customer, order and order details data from the Northwind sample database. The retrieved data is then displayed to the user based on the selected customer and order. The second sample application uses a custom table to store customer records. This application allows you to edit, delete and create new customers using the Entity Framework. Both sample applications are displayed below, the source code can be found on top of this article.

Image 1

Figure 1: The UI of the master-detail view.

Image 2

Figure 2: A simple CRUD application using Entity Framework.

Background

The Entity Framework allows you to communicate from your .net application to your database using object mapping. Using this approach, you can access tables in your database without writing a single SQL statement. Furthermore the Entity Framework allows you to use transactions, so that multiple database actions succeed or fail. In this article, I wrote two simple applications to illustrate the power of using the Entity Framework.

The master-details view

To add the ADO.NET Entity Data Model to your application, you need to take the following steps. Right click on your project, select Add new Item -> ADO.NET Entity Data Model -> Generate from Database. Next select a database and test the connection. After you have pressed next, the database tables are retrieved, you need to make a selection of the tables you are going to use. Finally click finish to import the Data Model. As can be seen in the figure below, I imported the Data Model of three tables, Customers, Orders and Order details. 

Image 3

Figure 3: Tables used in the master-details view. 

As can be seen in the figure above, a customer can have zero to many orders associated with it. Each order consists out of at least one order detail. The master-detail application is written using MVVM architecture. The MainView consists out of three datagrids. When the application is started, the topmost datagrid is populated with customers objects. When the applications user selects a customer, the orders associated with that customer are retrieved and displayed in the orders datagrid. Furthermore, when the applications user selects an order, the order details associated with that order are retrieved and displayed in the products datagrid. The code snippet below shows how the three datagrids in the view are populated.

C#
public class MainWindowViewModel
{
    private NorthwindEntities db = null;

    public MainWindowViewModel()
    {
        db = new NorthwindEntities();
        db.Configuration.LazyLoadingEnabled = false;
        db.Configuration.AutoDetectChangesEnabled = false;
        CustomersCollection = new ObservableCollection<Customers>(db.Customers);
        OrdersCollection = new ObservableCollection<Orders>();
        ProductCollection = new ObservableCollection<Order_Details>();
    }

    private ObservableCollection<Customers> customersCollection;
    public ObservableCollection<Customers> CustomersCollection
    {
        get
        {
            return customersCollection;
        }
        set
        {
            customersCollection = value;
            NotifyPropertyChanged();
        }
    }

    Customers selectedCustomers = null;
    public Customers SelectedCustomers
    {
        get { return selectedCustomers; }
        set
        {
            selectedCustomers = value;
            NotifyPropertyChanged();
            GetOrders(selectedCustomers.CustomerID);
            ProductCollection.Clear();
        }
    }

    private ObservableCollection<Orders> ordersCollection;
    public ObservableCollection<Orders> OrdersCollection
    {
        get
        {
            return ordersCollection;
        }
        set
        {
            ordersCollection = value;
            NotifyPropertyChanged();
        }
    }

    Orders selectedOrder = null;
    public Orders SelectedOrder
    {
        get { return selectedOrder; }
        set
        {
            selectedOrder = value;
            NotifyPropertyChanged();
            if (selectedOrder != null)
            {
                GetOrderDetails(selectedOrder.OrderID);
            }
        }
    }

    private ObservableCollection<Order_Details> productCollection;
    public ObservableCollection<Order_Details> ProductCollection
    {
        get
        {
            return productCollection;
        }
        set
        {
            productCollection = value;
            NotifyPropertyChanged();
        }
    }

    private void GetOrders(object CustomerID)
    {
        var query = (from order in db.Orders
                     where order.CustomerID == CustomerID
                     select order).ToList();

        OrdersCollection.Clear();
        foreach (Orders order in query)
        {
            OrdersCollection.Add(order);
        }
    }

    private void GetOrderDetails(object OrderID)
    {
        int orderID = int.Parse(OrderID.ToString());
        var query = (from orderDetails in db.Order_Details
                     where orderDetails.OrderID == orderID
                     select orderDetails).ToList();

        ProductCollection.Clear();

        foreach (Order_Details order in query)
        {
            ProductCollection.Add(order);
        }
    }

    public event PropertyChangedEventHandler PropertyChanged;
    private void NotifyPropertyChanged([CallerMemberName] String propertyName = "")
    {
        if (PropertyChanged != null)
        {
            PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }
    }
}

Code snippet 1: Code used to populate the master-details view. 

Simple CRUD application using Entity Framework

I created a simple CRUD application to illustrate the power of the Entity Framework. The Database table used to store the application data is created using the SQL syntax shown below.

SQL
use [ExampleDatabase];

CREATE TABLE Customers  
(  
 id int IDENTITY(1,1),  
 FirstName varchar (50),
 LastName varchar (50),
 Gender varchar (10),
 SSN varchar (20),
 PhoneNumber varchar (20),
 Email varchar (255)
);

INSERT Customers (FirstName, LastName, Gender, SSN, PhoneNumber, Email)  
VALUES ('Arnold', 'Schwarzenegger', 'Male', '428-20-1786', '202-555-0256', 'Arnold.Schwarzenegger@gmail.com');  
  
INSERT Customers (FirstName, LastName, Gender, SSN, PhoneNumber, Email)  
VALUES ('Emma', 'Watson', 'Female', '230-58-8128', '202-555-0189', 'Emma.Watson@yahoo.com');  

INSERT Customers (FirstName, LastName, Gender, SSN, PhoneNumber, Email)  
VALUES ('Mila', 'Kunis', 'Female', '678-34-3456', '342-456-324', 'MilaKunis@outlook.com'); 

Code snippet 2: SQL code used to to create sample data. 

Furthermore, I used a generic repository to access the database, and perform CRUD operations. The generic repository code is shown below.

C#
public class GenericRepository<T> : IGenericRepository<T> where T : class
{
    private ExampleDatabaseEntities db = null;
    private DbSet<T> table = null;

    public GenericRepository()
    {
        this.db = new ExampleDatabaseEntities();
        table = db.Set<T>();
    }

    public GenericRepository(ExampleDatabaseEntities db)
    {
        this.db = db;
        table = db.Set<T>();
    }

    public IEnumerable<T> SelectAll()
    {
        return table.ToList();
    }

    public T SelectByID(object id)
    {
        return table.Find(id);
    }

    public void Insert(T obj)
    {
        table.Add(obj);
    }

    public void Update(T obj)
    {
        table.Attach(obj);
        db.Entry(obj).State = EntityState.Modified;
    }

    public void Delete(object id)
    {
        T existing = table.Find(id);
        table.Remove(existing);
    }

    public void Save()
    {
        db.SaveChanges();
    }
}

Code snippet 3: The generic repository that supports CRUD operations. 

The CRUD operations are hardcoded. As you can see in the code below, the CRUD operations can be performed without using any SQL statement, thanks to the Entity Framework.  

C#
private IGenericRepository<Customers> repository = null;
public MainWindow()
{
    InitializeComponent();
    this.repository = new GenericRepository<Customers>();
    CustomersCollection = new ObservableCollection<Customers>(this.repository.SelectAll());
    this.DataContext = this;
}

private void InsertHandler(object sender, RoutedEventArgs e)
{
    Customers aCustomer = new Customers() 
    {
        FirstName = "Mohamed",
        LastName = "Kalmoua",
        PhoneNumber="0654654098",
        Email ="Mohamed.Kalmoua@gmail.com"
    }; 
    repository.Insert(aCustomer);
    repository.Save();
    UpdateCollection();
}

private void DeleteHandler(object sender, RoutedEventArgs e)
{
    repository.Delete(SelectedCustomer.id);
    repository.Save();
    UpdateCollection();
}

private void UpdateHandler(object sender, RoutedEventArgs e)
{
    SelectedCustomer.FirstName = SelectedCustomer.FirstName + "_Updated";
    SelectedCustomer.LastName = SelectedCustomer.LastName + "_Updated";
    repository.Update(SelectedCustomer);
    repository.Save();
    UpdateCollection();
}

private void ReadHandler(object sender, RoutedEventArgs e)
{
    string customer = SelectedCustomer.FirstName + Environment.NewLine +
        SelectedCustomer.LastName + Environment.NewLine +
        SelectedCustomer.SSN + Environment.NewLine;

    System.Windows.MessageBox.Show(customer);
}

Code snippet 4: Using the generic repository. 

Points of Interest

When using the Entity Framework, several settings (such as lazyloading, AutoDetectChangesEnabled)  can influence the performance of your application negatively.

References

History

  • July 23, 2016: Version 1.0.0.0 - Published the article

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Netherlands Netherlands
Mohamed Kalmoua is a Microsoft Certified Solutions Developer (MCSD) with over a decade of programming experience. He creates software for the Windows platform using C#, WPF, ASP.NET Core, SQL and C++. Mohamed also loves to build websites using Wordpress and Google analytics.

Comments and Discussions

 
QuestionDatabase fot Master-Detail? Pin
Member 1475458825-Feb-20 19:49
Member 1475458825-Feb-20 19:49 
QuestionNo Sample can be downloaded Pin
Member 1138346329-Jul-16 20:26
Member 1138346329-Jul-16 20:26 
AnswerRe: No Sample can be downloaded Pin
Mohamed Kalmoua31-Jul-16 3:35
Mohamed Kalmoua31-Jul-16 3:35 
Questioncan't get your samples running Pin
Mr.PoorEnglish22-Jul-16 18:17
Mr.PoorEnglish22-Jul-16 18:17 
Since I have no appropriate DB, which fits to your Entity-Model, is see no way to get your samples running.
Your instructions unfortunately give no help in such case.

What can I do?
AnswerRe: can't get your samples running Pin
Mohamed Kalmoua23-Jul-16 2:57
Mohamed Kalmoua23-Jul-16 2:57 
GeneralRe: can't get your samples running Pin
Mr.PoorEnglish23-Jul-16 3:32
Mr.PoorEnglish23-Jul-16 3:32 
GeneralRe: can't get your samples running Pin
Mohamed Kalmoua23-Jul-16 4:10
Mohamed Kalmoua23-Jul-16 4:10 
GeneralRe: can't get your samples running Pin
Mr.PoorEnglish23-Jul-16 5:08
Mr.PoorEnglish23-Jul-16 5:08 
GeneralRe: can't get your samples running Pin
Mohamed Kalmoua23-Jul-16 5:32
Mohamed Kalmoua23-Jul-16 5:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.