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 CSV, this application will create an application that lists the Accounts from a CSV file and displays the Opportunities associated with the selected Account from another CSV file.

NOTE: While this article refers to CSV files, 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 CSV
• Sample CSV files (downloadable ZIP)

Guide

1. Create the application
   1. Open Visual Studio and create a new “Windows Forms App” project.
   2. Name your project “WinFormsAppCSV”, 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.CSV.dll, located in the lib -> net8.0 subfolder in the installation directory.
   3. Add a reference to CData.EntityFrameworkCore.CSV.dll, located in the lib -> net8.0 -> EFCORE80 subfolder in the installation directory.
   4. Build the project to complete the setup for using EF Core.
3. Add the CSV files (Account.csv & Opportunity.csv) to your project
   1. Right-click on your project in the Solution Explorer then select “Add” > “New Folder”. 
   2. Name the folder “CSVFiles”, then right-click the new folder and select “Add” > “Existing Item…”. 
   3. Add the Account.csv and Opportunity.csv file to your CSVFiles folder. 
4. 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 all tables and views from the schema into your Models folder. This command automatically constructs classes for the 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 "URI=YourURI; RowScanDepth=0;UseRowNumbers=True" CData.EntityFrameworkCore.CSV -OutputDir Models -Context CSVContext 
   Notes:
   • Set the “URI” connection property to the folder holding your CSV File, for example: “C:\Users\YourUser\source\repos\WinFormsAppCSV\WinFormsAppCSV\CSVFiles\Account.csv". 
   • We set the “RowScanDepth” property to “0” to ensure the entire CSV file is parsed when scanning rows to dynamically determine column names and data types for the tabular representation of the CSV file(s). Scanning the entire document may result in a longer request, but the column data types will be more accurate when reflecting the data model. 
   • Since the CSV file does not have a primary key, we set the UseRowNumbers to “True” which will create a new column with the name “RowNumber” that will be used as a key for the table. Entity Framework relies on primary keys to manage data operations, and any tables/views lacking a primary key will be excluded from the generated model. 

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

5. 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.

   
6. Data binding

   ComboBox for selecting an individual account from our Accounts CSV file:

   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 “AccountCsv” to create an object data source for Accounts 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 AccountCsv.

   DataGridView for displaying opportunities related to the selected account:

   1. Select the tiny button at the top-right corner of the DataGridView added, open the drop-down for “Choose Data Source”, and choose “Add new Object Data Source...”
   2. Choose “OpportunityCsv” to create an object data source for Opportunities and click OK.
   3. Now the “Choose Data Source” drop-down contains the object data source we just created. Expand Other Data Sources, then Project Data Sources, and choose OpportunityCsv.

   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.

7. Configure what is displayed
   1. Select the ComboBox added and open the properties. Select the “DisplayMember” property and set it to “Name”. Then, select the “ValueMember” property and set it to “Id”. Setting these properties will display the name for each Account, but when an Account is selected, the value grabbed is the Account’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 Id, Name, and AccountId columns.
8. Load all Accounts
   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 WinFormsAppCSV.Models; namespace WinFormsAppCSV { public partial class Form1 : Form { private CSVContext? dbContext; public Form1() { InitializeComponent(); } protected override void OnLoad(EventArgs e) { base.OnLoad(e); this.dbContext = new CSVContext(); this.dbContext.AccountCsvs.Load(); this.accountCsvBindingSource.DataSource = dbContext.AccountCsvs.ToList(); } } }
      This code does the following:
      • Created an instance of the CSVContext that will be used to load accounts displayed by the ComboBox.
      • The Load extension method is used to load all the accounts from the CSV connection into the DbContext.
      • The accountCsvBindingSource.DataSource property is initialized to the accounts that are being tracked by the DbContext. This is done by calling .ToList() on the AccountCsvs DbSet property. ToList() exposes this data as a list in the ComboBox.
9. Populate the Opportunities
   1. In the designer for the form, select the ComboBox for accounts.
   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 code for an event to be fired whenever the account selection changes.
   3. Fill in the code for the SelectionValueChanged event: private void comboBox1_SelectedValueChanged(object sender, EventArgs e) { string selectedAccountId = Convert.ToString(comboBox1.SelectedValue); this.dbContext = new CSVContext(); this.dbContext.OpportunityCsvs .Where(x => x.AccountId == selectedAccountId) .Load(); this.opportunityCsvBindingSource.DataSource = dbContext.OpportunityCsvs.Local.ToBindingList(); }
      This code has:
      • Converted the selected value to a string to match the data type for the AccountId column.
      • Created an instance of the CSVContext that will be used to load opportunities displayed by the DataGridView.
      • The Load extension method is used to load the opportunities for the given account from the CSV connection into the DbContext.
      • The opportunityCsvBindingSource.DataSource property is initialized to the opportunities that are being tracked by the DbContext. This is done by calling Local.ToBindingList() on the OpportunityCsvs DbSet property. Local provides access to a local view of the tracked opportunities, 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.
10. Run the application

    With everything set up, you can now run the application that lists the Accounts from your Accounts.csv file and displays the Opportunities associated with the selected Account. 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 Accounts to view the Opportunities associated with each Account, as shown below:

    

Free Community License for data developers

CData ADO.NET Providers further enhance the capabilities of Entity Framework by offering consistent connectivity to more than 270 data sources beyond traditional databases, including SaaS, NoSQL, and big data systems.

With the CData ADO.NET 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 data apps today!