DataGridView/Entity Framework common operations (Part 2 A)
Introduction
This is a continuation of part 2 working with Entity Framework in a DataGridView.
Here the lesson to having a DataGridView populated with data read from a main table, customers along with related tables for contact, contact type and country which provides immediate updating capabilities when editing cells which mimics the behavior found when working with Microsoft Access data.
Unlike working with conventional DataSet and DataTable containers there is more work involved and by following along with this article and included code samples the logic needed to provide the same functionality will become easy to implement in any project.
Base class
The following class NorthWindOperations has a read-only access to the DbContext and NorthWindOperations is declared at form level so that the DbContext change tracker can work against data read in during the life time of the application lifespan. Also included are methods to read all required tables (see figure 1 diagram).
Form code
Two private objects are setup where a custom BindingList, SortableBindingList is used to permit column header click sorting in the DataGridView with the customer data as it's DataSource which is strong typed as a List(Of CustomerEntity). The second is a BindingSource which uses the SortableBindingList as it's DataSource.
In the Shown event of the form data is read into the SortableBindingList then the BindingSource.DataSource is set to the SortableBindingList which in turn becomes the DataSource of the DataGridView.
There are two DataGridViewComboBox columns, one for ContactType and one for Country. See the following segment of code of form shown for how these columns are setup. When implementing combo box columns in a DataGridView by novice, hobbyist or amateur developers the tendency is to setup up combo box columns incorrectly along with a improper column and table setup in a database so study both the code and data relations to use combo box columns in selected software solutions.
At the end of form shown the following code attempts to find a customer loaded into the BindingList, if found will move to the row found in the DataGridView. There is also another example later for finding a customer by company name via starts with using case insensitive method.
FindCompanyByContactName extension.
Dim entity = _customerView.FindCompanyByContactName("Catherine", "Dewey")
If entity IsNot Nothing Then
_customerBindingSource.Position = entity.RowIndex
End If
Implementation for updates
This code handles the majority for detection of data changed in the DataGridView accept for contact first and last name which will be examined below. Both combo box data updates are by foreign keys in the customer class.
Customer table updates
- First using e.propertyDescriptor ensure it's not null/Nothing. If null/nothing do not continue as this will throw a run time exception.
- Determine if the change type is "ItemChanged", if this is the case read the current customer from the database then attach it to the DbContext and set it's current values to the values read from the current row in the DataGridView.
- Next validation is performed against rules in the model obtained from the database table. If there are issues e.g. a field can not be empty, present the violation to the user along with resetting values back to the original values. Note to keep this simple the idea of reverting to a prior version is outside the scope of this article as this would make learning the basics more difficult and some will not care for this.
- If validation passes changes are saved.
Notes:
- Validation code is contained in the following class project included in source code which can be used in any project as the code is generic, not tied to any context or model.
- An alternate to applying validation in the event is to perform validation in an override of SaveChanges in the DbContext.
Private Sub _customerView_ListChanged(sender As Object, e As ListChangedEventArgs) _
Handles _customerView.ListChanged
If e.PropertyDescriptor IsNot Nothing Then
'
' Get current customer in current DataGridView row
'
Dim currentCustomer As CustomerEntity =
_customerView.CurrentCustomer(_customerBindingSource.Position)
'
' Assert if there are changes to the current row in the DataGridView
'
If e.ListChangedType = ListChangedType.ItemChanged Then
'
' Get data residing in the database table
'
Dim customerItem = operations.Context.Customers.
FirstOrDefault(
Function(customer) customer.CustomerIdentifier =
currentCustomer.CustomerIdentifier)
If customerItem IsNot Nothing Then
Dim customerEntry = operations.Context.Entry(customerItem)
customerEntry.CurrentValues.SetValues(currentCustomer)
'
' Setup validation on current row data
'
Dim validation = ValidationHelper.ValidateEntity(customerItem)
'
' If there are validation error present them to the user
'
If validation.HasViolations Then
Dim errorItems = String.Join(
Environment.NewLine,
validation.
ErrorItemList().
Select(Function(containerItem) containerItem.ErrorMessage).
ToArray())
MessageBox.Show(errorItems & Environment.NewLine & "Customer has been reset!!!",
"Corrections needed")
'
' Read current values from database
'
Dim originalCustomer = operations.CustomerFirstOrDefault(customerItem.CustomerIdentifier)
'
' reset current item both in Customer object and CustomerEntity object
' (there are other ways to deal with this but are dependent on business logic)
'
customerEntry.CurrentValues.SetValues(originalCustomer)
_customerView.Item(_customerBindingSource.Position) = originalCustomer
Else
operations.Context.SaveChanges()
End If
End If
End If
End If
End Sub
Contact table updates
this is performed in CellValueChanged event of the DataGridView.
- First assertion is done to see if the DataGridView has data as this event is raised while data is not yet fully loaded.
- Next an assertion is performed to see if the column is for the first or last name of the contact associated with the current customer.
- If this is first or last name of the contact values are set and changes are saved to the database.
Private Sub DataGridView1_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) _
Handles CustomersDataGridView.CellValueChanged
'
' Don't fire when loading the DataGridView, wait until the DataGridView has data
'
If CustomersDataGridView.DataSource IsNot Nothing Then
'
' If first or last name save changes, no check to see if there are actual
' changes.
'
If ContactColumNames.Contains(CustomersDataGridView.Columns(e.ColumnIndex).Name) Then
'
' Get current customer for the Contact key, find the Contact
' and update first or last name
'
Dim customer As CustomerEntity = _customerView.
CurrentCustomer(_customerBindingSource.Position)
Dim contact = operations.Context.Contacts.
FirstOrDefault(Function(c) c.ContactIdentifier =
CInt(customer.ContactIdentifier))
If CustomersDataGridView.Columns(e.ColumnIndex).Name = "FirstNameColumn" Then
contact.FirstName = CStr(CustomersDataGridView.Rows(e.RowIndex).
Cells("FirstNameColumn").Value)
End If
If CustomersDataGridView.Columns(e.ColumnIndex).Name = "LastNameColumn" Then
contact.LastName = CStr(CustomersDataGridView.Rows(e.RowIndex).
Cells("LastNameColumn").Value)
End If
operations.Context.SaveChanges()
End If
End If
End Sub
Setup
- Create the database in SQL-Server using the following script.
- Clone the repository.
- Do a restore NuGet packages
- Build and run the project the front end project which uses this back end project.
Summary
In this part of the series loading data into a DataGridView with functionality to perform immediate updates have been shown. In the next part of this series adding new data and removing current data will be shown along with various ways to filter data.
See Also
VB.NET Entity Framework: Wiki portal
DataGridView/Entity Framework common operations (Part 1)
Source code
The following GitHub repository contains projects to explore what has been presented along with other projects belonging to other TechNet articles.