The intended audience is for those developers that have been writing data centric solution in Visual Studio using SQL-Server with TableAdapter with a desire to use Entity Framework rather than former methods for working with databases, DataAdapter or using SqlClient data provider. Each of these options have advantages and disadvantages were many times the selection was made based on one or more factors such as “this option is easy for me not being a regular developer”, they used the first option found while the educated developer chooses smartly. For details on differences between conventional methods of accessing data see part one of this series Moving from ADO.NET to Entity Framework.
Time has brought new options for working with data, namely Entity Framework 6 classic or Entity Framework Core. The Entity Framework class families provide more power, streamline coding with more features than conventional methods of working with data in Windows Form solutions.
Even with Entity Framework providing better methods of working with data, developers working with Windows Forms tend to not a) look at Entity Framework, they are satisfied with their current methods of coding b) are unsure how to use Entity Framework in a Windows Form solution as most code samples on the Internet for Entity Framework are generally web based.
For those developers who are unsure about Entity Framework in Windows Forms solution this article will walk through reading, filtering, sorting, editing, deleting and adding data from a traditional Windows Form solution.
The database model below is a modified version of Microsoft’s North-Wind database. Originally North Wind tables were not the best design e.g. Customer table had a string for the primary key, contact information embedded in the customer table rather than broken out into child tables. For experienced developers they will see opportunities to farther refine the database model, that is fine but the point here is to work with a simple schema.
As this series progresses the database schema for Customer table will have more child tables which will use the same methods to work with data as the start of this series, just more complex in nature for proper normalization of data.
The following screenshot is how data is presented. Note there are two ComboBox columns utilized in the DataGridView were all rows show these ComboBoxes. In the code when running the sample application those ComboBox controls will not show as showing them simply clutters the screen. There are comments indicating to comment out two lines of code in form shown event of the main form to present the two ComboBoxes as shown below.
Before building the Visual Studio, solution use the script under the folder data scripts to create a database, tables along with populating the tables. Once the database has been completed note that there are cascading rules for delete operations between customer and contact tables so that when removing a customer the contact is removed. If there is no rule for cascade deletes attempting a delete would cause a runtime error. Note that in some cases the contact should not be removed, for these cases contacts may have a active field which is set to false but available, then used the active flag toggles to true.
When building the solution note there are several NuGet packages used, if they are not found then right-click on Solution Explorer and select restore NuGet packages then build the solution.
public
interface
IBaseEntity
{
int
Identifier {
get
; }
}
partial
class
Customer : IBaseEntity
Identifier
return
id;
GenderType : IBaseEntity
GenderIdentifier;
bool
AnnihilateData(List<
object
> mAnnihilateList)
mAnnihilateDataSuccessful =
false
;
using
(var destroyContext =
new
PersonEntities())
for
(
i = mAnnihilateList.Count - 1; i >= 0; i--)
try
var currentObject = mAnnihilateList[i];
var existingItem = destroyContext
.Set(currentObject.GetType())
.Find(((IBaseEntity)currentObject).Identifier);
if
(existingItem !=
null
)
var attachedEntry = destroyContext.Entry(existingItem);
attachedEntry.CurrentValues.SetValues(currentObject);
destroyContext.Set(existingItem.GetType()).Attach(existingItem);
destroyContext.Set(existingItem.GetType()).Remove(existingItem);
catch
(Exception)
// ignore nothing do to as the object was not added in properly.
else
var item = currentObject.GetType();
//catch and continue save what we can
var resultCount = destroyContext.SaveChanges();
var annihlationCount = mAnnihilateList.Count;
mAnnihilateDataSuccessful = (resultCount == annihlationCount);
// keep on going
finally
destroyContext.Dispose();
mAnnihilateDataSuccessful;
NorthWindAzureContext : DbContext
NorthWindAzureContext()
:
base
"name=NorthWindModel"
"data source=.\\SQLEXPRESS;initial catalog=NorthWindAzureForInserts;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"
[System.Diagnostics.CodeAnalysis.SuppressMessage(
"Microsoft.Usage"
,
"CA2214:DoNotCallOverridableMethodsInConstructors"
)]
Country()
Customers =
HashSet<Customer>();
[Key]
CountryIdentifier {
set
string
Name {
"CA2227:CollectionPropertiesShouldBeReadOnly"
virtual
ICollection<Customer> Customers {
CountryItem
? CountryIdentifier {
CountyName {
override
ToString()
CountyName;
List<CountryItem> GetCountries()
(var context =
NorthWindAzureContext())
// indicates not to load Customers
context.Configuration.LazyLoadingEnabled =
context.Countries.Select(country =>
CountryItem()
CountryIdentifier = country.CountryIdentifier,
CountyName = country.Name
}).ToList();
var countries = _operations.GetCountries();
countries.Insert(0,
CountryItem() {CountryIdentifier = 0, CountyName =
"Select"
});
ContactListBox
? ContactId {
Name;
namespace
NorthWindDataLibrary.Classes
/// <summary>
/// Container for displaying customers in a DataGridView
/// The Notification change via Bindable will be discussed
/// in a future article to this article.
/// </summary>
CustomerEntity : Bindable
CustomerIdentifier {
CompanyName
=> Get<
>();
=> Set(value);
? ContactIdentifier {
FirstName {
LastName {
ContactTypeIdentifier {
ContactTitle {
Address {
City {
PostalCode {
System.Collections.Generic;
System.Linq;
Operations
/// Context setup in AllCustomers method
private
NorthWindAzureContext _context;
/// Get all customers into a custom class suitable for viewing
/// in a DataGridView which in the form requires special attention
/// <param name="context"></param>
/// <returns></returns>
List<CustomerEntity> AllCustomers(NorthWindAzureContext context)
_context = context;
var customerData = (
from customer
in
context.Customers
join contactType
context.ContactTypes on customer.ContactTypeIdentifier
equals contactType.ContactTypeIdentifier
join contact
context.Contacts on customer.ContactId equals contact.ContactId
select
CustomerEntity
CustomerIdentifier = customer.CustomerIdentifier,
CompanyName = customer.CompanyName,
ContactIdentifier = customer.ContactId,
FirstName = contact.FirstName,
LastName = contact.LastName,
ContactTypeIdentifier = contactType.ContactTypeIdentifier,
ContactTitle = contactType.ContactTitle,
Address = customer.Address,
City = customer.City,
PostalCode = customer.PostalCode,
CountryIdentifier = customer.CountryIdentifier,
CountyName = customer.Country.Name
customerData;
/// Get all countries
/// Get all contact types
List<ContactType> GetContactTypes()
context.ContactTypes.ToList();
/// Get all known contacts
List<Contact> GetContacts()
context.Contacts.ToList();
/// Remove customer by primary key
/// <param name="pCustomerIdentifier"></param>
/// <remarks>
/// Discuss cascading delete rules
/// - how to check for child table usage
/// </remarks>
RemoveCustomer(
pCustomerIdentifier)
true
SELECT
Cust.CustomerIdentifier,
Cust.CompanyName,
Cust.ContactId,
Contacts.FirstName,
Contacts.LastName,
Cust.ContactTypeIdentifier,
CT.ContactTitle,
Cust.Address
AS
Street,
Cust.City,
Cust.PostalCode,
Cust.CountryIdentifier,
Countries.
Name
CountryName,
Cust.ModifiedDate
FROM
Customers
Cust
INNER
JOIN
ContactType
CT
ON
Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier
Contacts
Cust.ContactId = Contacts.ContactId
Countries
Cust.CountryIdentifier = Countries.CountryIdentifier
Before beginning to write code objectives need to be known, in this case data needs to be presented in a DataGridView control where several properties (country and contact details) will need a way to allow a user to change the current values, in this case a DataGridViewComboBox. Also, contact first and last name come from a child related table so this must be considered.
Another factor, provide the ability to sort/order columns in the DataGridView as simply assigning data to the DataGridView will not provide the ability to sort/order columns. In this case rather than rolling a in-house component to handle sorting a third party free component (with source code) will be utilize.
For editing properties that are not of type DataGridViewComboBox columns the following code is used. In EditingControlShowing event of the DataGridView DataGridViewComboBox logic is handled so a check is made at the start of the event to detect if the current cell is a DataGridViewComboBox by determining if the cell type is DataGridViewComboBoxEditingControl using a language extension method included in the source code. If not of type DataGridViewComboBoxEditingControl then exit the event, otherwise first determine which of the two DataGridViewComboBox is current then subscribe to one of two events,
In these two events the proper country identifier or proper ContactType identifier is set to the foreign key in the current customer's record, This must be done as when loading data there are joined tables that Entity Framework does not or can not track, Manually setting these identifier will mark the customer as dirty.
void
DataGridView1_EditingControlShowing(
sender, DataGridViewEditingControlShowingEventArgs e)
(!gridView.CurrentCell.IsComboBoxCell())
var comboBox = e.Control
as
ComboBox;
// ReSharper disable once PossibleNullReferenceException
comboBox.SelectionChangeCommitted -= SelectionChangeCommittedForCountryColumn;
comboBox.SelectionChangeCommitted -= SelectionChangeCommittedForContactTitleColumn;
(gridView.Columns[gridView.CurrentCell.ColumnIndex].Name ==
"CountyNameColumn"
comboBox.SelectionChangeCommitted += SelectionChangeCommittedForCountryColumn;
"ContactTitleColumn"
comboBox.SelectionChangeCommitted += SelectionChangeCommittedForContactTitleColumn;
/// Set backing customer field foreign key for Country for current customer record
/// <param name="sender"></param>
/// <param name="e"></param>
SelectionChangeCommittedForCountryColumn(
sender, EventArgs e)
var customer = _customersBindingSource.CurrentCustomerEntity();
var identifier = ((CountryItem)(((DataGridViewComboBoxEditingControl)sender).SelectedItem))
.CountryIdentifier;
customer.CountryIdentifier = identifier;
.FirstOrDefault(cust => cust.CustomerIdentifier == customer.CustomerIdentifier)
.CountryIdentifier = identifier;
/// Set backing customer field foreign key for contact title type for current customer record
SelectionChangeCommittedForContactTitleColumn(
var identifier = ((ContactType)(((DataGridViewComboBoxEditingControl)sender).SelectedItem))
.ContactTypeIdentifier;
customer.ContactTypeIdentifier = identifier;
.ContactTypeIdentifier = identifier;
var currentColumnName = gridView.CurrentCell.OwningColumn.Name;
var customerEntity = _customersBindingSource.CurrentCustomerEntity();
var customer = context.Customers
.FirstOrDefault(cust =>
cust.CustomerIdentifier == customerEntity.CustomerIdentifier);
context.Entry(customer).Property(gridView.CurrentCell.OwningColumn.DataPropertyName)
.CurrentValue = gridView.EditingControl.Text;
bindingNavigatorDeleteCustomer_Click(
var currentCustomer = _customersBindingSource.CurrentCustomerEntity();
(Question($
"Delete '{currentCustomer.CompanyName}'"
))
cust.CustomerIdentifier == currentCustomer.CustomerIdentifier);
context.Entry(customer).State = EntityState.Deleted;
_customersBindingSource.RemoveCurrent();
Clicking the save all button will save all changes back to the database then for this sample will show the count of changes to the IDE output window. Of course a Console.WriteLine would never be in a real application, this is here to show that SaveChanges returns how many items were saved.
Console.WriteLine(context.SaveChanges());
MainForm_Closing(
sender, CancelEventArgs e)
(context.ChangeTracker.HasChanges())
(!Question(
"There are pending changes, abort?"
e.Cancel =
Use asynchronous methods such as ToListAsync extension method will allow applications be remain responsive when dealing with large sets of data. Using asynchronous methods in a windows forms requires checking for cross thread violations.
In the following method ToListAsync is used.
async Task<List<CustomerEntity>> AllCustomersAsync(NorthWindAzureContext context)
var customerData = await (
}).ToListAsync();
async
Form1_Shown(
var result = await _operations.AllCustomersAsync(context)
.ConfigureAwait(
);
_customersView =
BindingListView<CustomerEntity>(result);
_customersBindingSource.DataSource = _customersView;
gridView.Invoke(
Action(() =>
gridView.DataSource = _customersBindingSource));
gridView.ExpandColumns()));
bindingNavigator1.Invoke(
bindingNavigator1.BindingSource =
_customersBindingSource));
SaveAllChangesButton_Click(
SaveAllChangesButtonAsync_Click(
Console.WriteLine(await context.SaveChangesAsync());
It’s commonplace for a database schema to change as requirements change for a project. For example, using the database for the code sample contact information is stored in a contact table and a contact type table. Now an additional requirement is to store the contact’s phone information and since a contact may have multiple phones (home, cell, work for instance) tables will need to be added to the database schema, device type e.g. desk or cell phone along if the phone number is active.
For those just starting out, add these tables to the database and run queries to ensure everything works as expected before moving on. The next step is to either create a new temporary model and perform the same steps as initially done to create the original model, once created copy those tables and DbSet definitions to the original model. For others there are two additional options, use code migrations or simply code the changes.
// perform work
context.Customers.Add(newCustomer);
context.SaveChanges();
context.Entry(newCustomer).State = EntityState.Added;
context.Entry(modifiedCustomer).State = EntityState.Modified;
var currentState = context.Entry(customer).State;
customer = context.Customers.Find(2);
context.Configuration.ValidateOnSaveEnabled =
var results = context.Customers.ToList()
.Where(cust => cust.CountryIdentifier == 9)
.Where(cust => cust.ContactTypeIdentifier == 12);
.Where(cust => cust.CountryIdentifier == 9 &&
cust.ContactTypeIdentifier == 12);
var results = context.Customers.AsQueryable();
results = results.Where(cust => cust.CountryIdentifier == 9);
results = results.Where(cust => cust.ContactTypeIdentifier == 12);
Customer GetFirstCustomer()
context.Customers.ToList().FirstOrDefault();
context.Customers.FirstOrDefault();
var example = context.Customers.ToList();
var example = context.Customers
.Include(cust => cust.Country)
.Include(cust => cust.Contact)
.ToList();
foreach
(var customer
example)
customer.Country.Customers =
context.Database.Log = Console.Write;
System;
System.IO;
ReadEditCustomerWithSpecialClass
FileLogger
static
Log(
logInfo)
File.AppendAllText(
Path.Combine(
AppDomain.CurrentDomain.BaseDirectory,
"context.log"
),
logInfo);
context.Database.Log = FileLogger.Log;
With Data Annotations attributes a developer can configure entity classes and properties ranging from changing a table's known name, marking a property as a primary key using [Key], marking a property as required using [Required] and a good deal more.
Many articles will talk about data annotations with web solutions as one of the primary uses besides declarat'ing a property for describing classes and properties in a class is for user interface notification of failed validation when modifying or adding a new record to the database. In the next article in this series validation will be discussed starting at the model level which will bubble up tp the windows form level.
It’s imperative to understand these attributes especially when working with code first methods for working with data as a developer can add an attribute in many cases rather than write client-side code. There are other benefits like being able to add properties to a class that will not be tracked by Entity Framework e.g. [NotMapped] were a common usage would be to concatenate two fields perhaps first and last name of a person to display in a ComboBox or ListBox.
[NotMapped]
FullName => $
"{FirstName} {LastName}"
[Required]
[StringLength(40)]
CompanyName {
[Required, StringLength(40)]
[Table(
"Customer"
Customer
Id {
AccountNumber {
[Required(ErrorMessage =
"{0} is required"
[StringLength(20, MinimumLength = 6)]
UserPassword {
[Compare(
"Password"
, ErrorMessage =
"Passwords do not match, please try again"
PasswordConfirmation {
[PasswordCheck(ErrorMessage =
"Must include a number and symbol in {0}"
PasswordCheck : ValidationAttribute
IsValid(
value)
var validPassword =
var reason =
.Empty;
password = (value ==
) ?
.Empty : value.ToString();
.IsNullOrWhiteSpace(password) || password.Length < 6)
reason =
"new password must be at least 6 characters long. "
Regex pattern =
Regex(
"((?=.*\\d)(?=.*[a-z])(?=.*[A-Z])(?=.*[@#$%]).{6,20})"
(!pattern.IsMatch(password))
reason +=
"Your new password must contain at least 1 symbol character and number."
validPassword =
(validPassword)
RestaurantDemo
System.ComponentModel.DataAnnotations;
System.ComponentModel.DataAnnotations.Schema;
System.Data.Entity.Spatial;
Guest
[System.Diagnostics.CodeAnalysis
.SuppressMessage(
Guest()
BreakfastOrders =
HashSet<BreakfastOrder>();
GuestIdentifier {
? RoomIdentifier {
ICollection<BreakfastOrder> BreakfastOrders {
Room Room {
$
List<Guest> TodayGuest()
RestaurantContext())
context.Guests.ToList();
Form1 : Form
Form1()
InitializeComponent();
Shown += Form1_Shown;
var ops =
Operations();
listBox1.DataSource = ops.TodayGuest();
/// If a guest has a breakfast order return it, if nothing
/// ordered indicate no order was placed.
/// <param name="guestIdentifier"></param>
GuestBreakfastOrderForToday(
guestIdentifier)
var today = Now.Date;
var results =
from orderItems
context.BreakfastOrderItems
join orderItem
context.BreakfastOrderItems on
orderItems.BreakfastItem.ItemIdentifier
equals orderItem.ItemIdentifier
where orderItem.BreakfastOrder.OrderDate == today &&
orderItem.GuestIdentifier == guestIdentifier
select orderItem
).ToList();
(results.Count >0)
var room = context.Guests
.FirstOrDefault(g => g.GuestIdentifier == guestIdentifier).Room;
.Join(Environment.NewLine, results
.Select(item => item.BreakfastItem.Item).ToArray()) +
"\n\nRoom: {room.RoomDesginator}{room.RoomNumber}"
"No order placed"
GetBreakfastOrderButton_Click(
var guest = (Guest) listBox1.SelectedItem;
var guestOrder = ops.GuestBreakfastOrderForToday(guest.GuestIdentifier);
// ReSharper disable once LocalizableElement
MessageBox.Show($
"{guest}\nOrder\n{guestOrder}"
where orderItem.BreakfastOrder.OrderDate == Now.Date