DataGridView/Entity Framework common operations (Part 2)

Article
1/17/2024

Introduction

This is a continuation of part 1 in this series which should be read before continuing with this part of the series if new to working with Entity Framework in Window Form projects.

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 classes for reading data

Since this demonstration works with a main table and several related tables a special class is needed to provide properties to display in the related tables as the main table for instance for contact only has the primary key of the customer and this goes for country and contact type.

Below shows relationships, note for Countries the primary key id is setup as a read-only property were another way to handle this is by using property mapping.

Figure 1

To provide notification of data changing in the DataGridView this class which implements INotifyPropertyChanged Interface.

Imports System.ComponentModel
Imports System.ComponentModel.DataAnnotations
Imports System.Runtime.CompilerServices
 
Public Class  CustomerEntity
    Inherits BaseEntity
    Implements INotifyPropertyChanged
 
    Private _customerIdentifier1 As Integer
    Private _companyName1 As String
    Private _contactIdentifier1 As Integer?
    Private _firstName1 As String
    Private _lastName1 As String
    Private _contactTypeIdentifier1 As Integer
    Private _contactTitle1 As String
    Private _address1 As String
    Private _city1 As String
    Private _postalCode1 As String
    Private _countryIdentifier1 As Integer?
    Private _countyName1 As String
 
    Public Property  CustomerIdentifier() As Integer
        Get
            Return _customerIdentifier1
        End Get
        Set
            _customerIdentifier1 = Value
            OnPropertyChanged()
        End Set
    End Property
    <Required>
    Public Property  CompanyName() As  String
        Get
            Return _companyName1
        End Get
        Set
            _companyName1 = Value
            OnPropertyChanged()
        End Set
    End Property
 
    Public Property  ContactIdentifier() As Integer?
        Get
            Return _contactIdentifier1
        End Get
        Set
            _contactIdentifier1 = Value
            OnPropertyChanged()
        End Set
    End Property
 
    Public Property  FirstName() As  String
        Get
            Return _firstName1
        End Get
        Set
            _firstName1 = Value
            OnPropertyChanged()
        End Set
    End Property
 
    Public Property  LastName() As  String
        Get
            Return _lastName1
        End Get
        Set
            _lastName1 = Value
            OnPropertyChanged()
        End Set
    End Property
 
    Public ReadOnly  Property ContactName() As String
        Get
            Return $"{FirstName} {LastName}"
        End Get
    End Property
 
    Public Property  ContactTypeIdentifier() As Integer
        Get
            Return _contactTypeIdentifier1
        End Get
        Set
            _contactTypeIdentifier1 = Value
            OnPropertyChanged()
        End Set
    End Property
 
    Public Property  ContactTitle() As  String
        Get
            Return _contactTitle1
        End Get
        Set
            _contactTitle1 = Value
            OnPropertyChanged()
        End Set
    End Property
 
    Public Property  Street() As  String
        Get
            Return _address1
        End Get
        Set
            _address1 = Value
            OnPropertyChanged()
        End Set
    End Property
 
    Public Property  City() As  String
        Get
            Return _city1
        End Get
        Set
            _city1 = Value
            OnPropertyChanged()
        End Set
    End Property
 
    Public Property  PostalCode() As  String
        Get
            Return _postalCode1
        End Get
        Set
            _postalCode1 = Value
            OnPropertyChanged()
        End Set
    End Property
 
    Public Property  CountryIdentifier() As Integer?
        Get
            Return _countryIdentifier1
        End Get
        Set
            _countryIdentifier1 = Value
            OnPropertyChanged()
        End Set
    End Property
 
    Public Property  CountryName() As  String
        Get
            Return _countyName1
        End Get
        Set
            _countyName1 = Value
            OnPropertyChanged()
        End Set
    End Property
 
    Public Event  PropertyChanged As  PropertyChangedEventHandler _
        Implements INotifyPropertyChanged.PropertyChanged
 
    Protected Overridable  Sub OnPropertyChanged(
        <CallerMemberName> Optional  memberName As  String = Nothing)
 
        RaiseEvent PropertyChanged(Me, New  PropertyChangedEventArgs(memberName))
 
    End Sub
 
End Class

In turn the CustomerEntity class is used in the following static property to retrieve customer and related data from the database.

Imports System.ComponentModel.DataAnnotations.Schema
Imports System.Linq.Expressions
 
Partial Public  Class Customer
    <NotMapped>
    Public Property  FirstName As  String
    <NotMapped>
    Public Property  LastName As  String
    Public Shared  ReadOnly Property  Projection() As  Expression(Of Func(Of Customer, CustomerEntity))
 
        Get
            Return Function(customer) New  CustomerEntity() With {
                .CustomerIdentifier = customer.CustomerIdentifier,
                .CompanyName = customer.CompanyName,
                .Street = customer.Street,
                .City = customer.City,
                .PostalCode = customer.PostalCode,
                .ContactTypeIdentifier = customer.ContactTypeIdentifier.Value,
                .ContactTitle = customer.ContactType.ContactTitle,
                .CountryName = customer.Country.CountryName,
                .FirstName = customer.Contact.FirstName,
                .LastName = customer.Contact.LastName,
                .ContactIdentifier = CInt(customer.ContactIdentifier),
                .CountryIdentifier = customer.CountryIdentifier}
        End Get
 
    End Property
End Class

In the form the following method reads in data asynchronously. Note when working with a large amount of rows and columns of data this allows the user interface to remain responsive.

Public Async Function AllCustomersAsync() As Task(Of List(Of CustomerEntity))
 
    Return Await Task.Run(
        Async Function()
            Dim customerItemsList As List(Of CustomerEntity) =
                    Await Context.Customers.Select(Customer.Projection).ToListAsync()
            Return customerItemsList.OrderBy(Function(customer) customer.CompanyName).ToList()
        End Function)
 
End Function

Setup

Create the database in SQL-Server using the following script Jump .
Clone the repository.
Do a restore NuGet packages
Build and run the project the front end project Jump which uses this back end project Jump .

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.

Share via

DataGridView/Entity Framework common operations (Part 2)

Introduction

Base classes for reading data

Setup

Summary

See also

Additional resources