Table of contents
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.
Figure 1: The UI of the master-detail view.
Figure 2: A simple CRUD application using Entity Framework.
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.
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.
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.
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.
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.
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.
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.
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.
When using the Entity Framework, several settings (such as lazyloading, AutoDetectChangesEnabled) can influence the performance of your application negatively.
- July 23, 2016: Version 1.0.0.0 - Published the article
