MySQL ADO.NET Developer Guide - Entity Framework Winform App

This article provides a guide for using Entity Framework (EF) Core to create a Windows Forms App. With the help of the CData ADO.NET Provider for MySQL, this application will create a form that lists the Employees from a MySQL instance and displays the Customers associated with the selected Employee.

NOTE: While this article refers to MySQL data, the principles can be applied to any of the 270+ data sources CData supports.

Prerequisites

This guide requires the following parameters:

• Visual Studio 2022
• .NET 8.0
• CData ADO.NET Provider for MySQL
• Sample MySQL Database

Guide

1. Create the application
   1. Open Visual Studio and create a new “Windows Forms App” project.
   2. Name your project “WinFormsAppMySQL”, so that the namespaces will match if you copy and paste code from this guide, and then select “Next”.
   3. On the next page, select “.NET 8.0 (Long-term support)” and then click “Create”.
2. Install Entity Framework and add references to the required assemblies
   1. Use the NuGet Package Manager to add the packages listed below to your project:
      1. Microsoft.EntityFrameworkCore
      2. Microsoft.EntityFrameworkCore.Relational
      3. Microsoft.EntityFrameworkCore.Tools
   2. Add a reference to System.Data.CData.MySQL.dll, located in the lib -> net8.0 subfolder in the installation directory (ex. "C:\Program Files\CData\CData ADO.NET Provider for MySQL 2024\").
   3. Add a reference to CData.EntityFrameworkCore.MySQL.dll, located in the lib -> net8.0 -> EFCORE80 subfolder in the installation directory.
   4. Add the included licensing file (.lic) to your project. You can do this by right-clicking the project in the Solution Explorer and then navigating to Add -> Existing Item -> System.Data.CData.MySQL.lic (located in the lib -> netstandard2.0 subfolder in your installation directory). Finally, right-click the licensing file, and set the Copy to Output Directory property to Copy if newer.
   5. Build the project to complete the setup for using EF Core.
3. Reverse Engineering (Scaffolding) the data model

   Scaffolding is performed using the Package Manager Console (PMC), so open the PMC and use the following command to scaffold the schema into your Models folder. This command automatically constructs classes for all tables/views available. It also creates a context class that extends DbContext and exposes the DbSet properties that represent the tables in the data source:

   Scaffold-DbContext "Authscheme=Password;Server=YourServer;Port=YourPort;User=YourUser;Password=YourPassword;Database=YourDatabase;Tables=customers,employees;" CData.EntityFrameworkCore.MySQL -OutputDir Models -Context MySQLContext

   Note: We set the Tables property to restrict the number of tables we connect to and model. This will only model the Customer and Employee tables from the MySQL connection instead of every available table and view.

   Read more about the CData MySQL ADO.NET Provider connection properties on the CData Documentation page: CData ADO.NET Provider for MySQL Documentation

4. Add controls to the form

   Open the Form Design and from the “Toolbox” on the left of the Server Explorer, add a DataGridView and a ComboBox to your form.

   
5. Data binding

   ComboBox for Employees:

   1. Select the ComboBox added and open the properties. Select the “DataSource” property then click the DropDown on the right, go to the bottom, and choose “Add new Object Data Source...”
   2. Choose “Employee” and "Customer" to create an object data source for Employees and Customers and click OK.
   3. Now the “DataSource” property drop-down contains the object data source we just created. Expand Other Data Sources, then Project Data Sources, and choose Employee.

   DataGridView for Customers:

   1. Select the tiny button at the top-right corner of the DataGridView added, open the drop-down for “Choose Data Source”, expand Other Data Sources, then Project Data Sources, and choose Customer.

   Note: If no data source types appear here, then make sure that the table classes and context class have been added to the project and the solution has been built.

6. Configure what is displayed
   1. Select the ComboBox added and open the properties. Select the “DisplayMember” property and set it to “LastName”. Then, select the “ValueMember” property and set it to “EmployeeNumber”. Setting these properties will display the name for each Employee, but when an Employee is selected, the value grabbed is the Employee's ID.
   2. Select the tiny button at the top-right corner of the DataGridView added and select “Edit Columns…”
   3. By default, the DataGridView will create a column for every column in your table, so we can remove unnecessary columns here. Remove every column except for the CustomerNumber, CustomerName, and SalesRepEmployeeNumber columns.
7. Load all Employees
   1. Open the form's code by right-clicking on the file and choosing “View Code”.
   2. Add a private field to hold the DbContext for the session and add an override for the OnLoad method. The full code should now look like this: using Microsoft.EntityFrameworkCore; using WinFormsAppMySQL.Models; namespace WinFormsAppMySQL { public partial class Form1 : Form { private MySQLContext? dbContext; public Form1() { InitializeComponent(); } protected override void OnLoad(EventArgs e) { base.OnLoad(e); this.dbContext = new MySQLContext(); this.dbContext.Employees.Load(); this.employeeBindingSource.DataSource = dbContext.Employees.ToList(); } } }
      This code does the following:
      • Created an instance of the MySQLContext that will be used to load employees displayed by the ComboBox.
      • The Load extension method is used to load all the employees from the MySQL connection into the DbContext.
      • The employeeBindingSource.DataSource property is initialized to the employees that are being tracked by the DbContext. This is done by calling .ToList() on the Employees DbSet property. ToList() exposes this data as a list in the ComboBox.
8. Populate the Customers
   1. In the designer for the form, select the ComboBox for Employees.
   2. In the Properties for the ComboBox, choose the events (the lightning button), and double-click the SelectedValueChanged event. This will create a stub in the form's code for an event to be fired whenever the employee selection changes.
   3. Fill in the code for the SelectionValueChanged event: private void comboBox1_SelectedValueChanged(object sender, EventArgs e) { string selectedEmployeeId = Convert.ToString(comboBox1.SelectedValue); this.dbContext = new MySQLContext(); this.dbContext.Customers .Where(x => x.SalesRepEmployeeNumber == selectedEmployeeId) .Load(); this.customerBindingSource.DataSource = dbContext.Customers.Local.ToBindingList(); }
      This code does the following:
      • Converted the selected value to an int to match the data type for the SalesRepEmployeeNumber column.
      • Created an instance of the MySQLContext that will be used to load customers displayed by the DataGridView.
      • The Load extension method is used to load the customers for the given employee from the MySQL connection into the DbContext.
      • The customerBindingSource.DataSource property is initialized to the customers that are being tracked by the DbContext. This is done by calling Local.ToBindingList() on the Customers DbSet property. Local provides access to a local view of the tracked customers, with events hooked up to ensure the local data stays in sync with the displayed data, and vice versa. ToBindingList() exposes this data as an IBindingList, which is understood by Windows Forms data binding.
9. Run the application

   With everything set up, you can now run the application that lists the Employees from your MySQL instance and displays the Customers associated with the selected Employee. To do this, first, build the solution then use the green play button to start the application. This should open the application, and you can click through the Employees to view the Customers associated with each Employee, as shown below:

   

Unlock seamless data connectivity in your .NET applications

Are you looking to unlock the full potential of your MySQL data? The CData MySQL ADO.NET Provider can seamlessly integrate your .NET applications with your MySQL data. With the CData ADO.NET Provider for MySQL Community License, you get free-forever libraries to access your data in personal .NET projects, all through familiar SQL. Request a license and start building MySQL-connected apps today!