PostgreSQL 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 PostgreSQL, this application will create a form that lists the Lego Inventories from a PostgreSQL instance containing Lego Inventory data and displays the Inventory Parts associated with the selected Lego Inventory.

NOTE: While this article refers to PostgreSQL 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 PostgreSQL
• Sample PostgreSQL Database

Guide

1. Create the application
   1. Open Visual Studio and create a new “Windows Forms App” project.
   2. Name your project “WinFormsAppPostgreSQL”, 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.PostgreSQL.dll, located in the lib -> net8.0 subfolder in the installation directory (ex. "C:\Program Files\CData\CData ADO.NET Provider for PostgreSQL 2024\").
   3. Add a reference to CData.EntityFrameworkCore.PostgreSQL.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.PostgreSQL.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=;Port=;User=;Password=;Database=; " CData.EntityFrameworkCore.PostgreSQL -Tables lego_sets, lego_inventories -OutputDir Models -Context PostgreSQLContext

   Note: We set the Tables property to restrict the number of tables we connect to and model. This will only model the Lego Set and Lego Inventory tables from the PostgreSQL connection instead of every available table and view. Also, be sure to add QueryPassthrough=false to the connection properties in the PostgreSQLContext.cs file generated.

   Read more about the CData PostgreSQL ADO.NET Provider connection properties on the CData Documentation page: CData PostgreSQL ADO.NET Provider 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 Lego Sets:

   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 “LegoInventory” and "LegoSet" to create an object data source for Lego Inventory and Lego Sets 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 LegoSet.

   DataGridView for Lego Inventories:

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

   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 “SetNum”. Then, select the “ValueMember” property and set it to “Id”. Setting these properties will display the Set Number for each Inventory, but when an Inventory is selected, the value grabbed is the Inventory 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.
7. Load all Lego Sets
   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 WinFormsAppPostgreSQL.Models; namespace WinFormsAppPostgreSQL { public partial class Form1 : Form { private PostgreSQLContext? dbContext; public Form1() { InitializeComponent(); } protected override void OnLoad(EventArgs e) { base.OnLoad(e); this.dbContext = new PostgreSQLContext(); this.dbContext.LegoInventories.Load(); this.legoInventoryBindingSource.DataSource = dbContext.LegoInventories.ToList(); } } }
      This code does the following:
      • Created an instance of the PostgreSQLContext that will be used to load Lego sets displayed by the ComboBox.
      • The Load extension method is used to load all the Lego sets from the PostgreSQL connection into the DbContext.
      • The legoInventoryBindingSource.DataSource property is initialized to the Lego sets that are being tracked by the DbContext. This is done by calling .ToList() on the LegoInventories DbSet property. ToList() exposes this data as a list in the ComboBox.
8. Populate the Lego Sets
   1. In the designer for the form, select the ComboBox for Lego Sets.
   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 Lego Set selection changes.
   3. Fill in the code for the SelectionValueChanged event: private void comboBox1_SelectedValueChanged(object sender, EventArgs e) { string selectedSetNum = Convert.ToString(comboBox1.SelectedValue); this.dbContext = new PostgreSQLContext(); this.dbContext.LegoInventories .Where(x => x.SetNum == selectedSetNum) .Load(); this.legoInventoryBindingSource.DataSource = dbContext.LegoInventories.Local.ToBindingList(); }
      This code does the following:
      • Converted the selected value to an int to match the data type for the InventoryId column.
      • Created an instance of the PostgreSQLContext that will be used to load Lego inventories displayed by the DataGridView.
      • The Load extension method is used to load the Lego sets for the given Inventory from the PostgreSQL connection into the DbContext.
      • The legosetBindingSource.DataSource property is initialized to the Lego sets that are being tracked by the DbContext. This is done by calling Local.ToBindingList() on the LegoSet DbSet property. Local provides access to a local view of the tracked inventories, 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 Lego Sets from your PostgreSQL instance and displays the Lego Inventory associated with the selected Lego Set. 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 Lego Sets to view the Lego Inventories associated with each Lego Set, as shown below:

   

Unlock seamless data connectivity in your .NET applications

Are you looking to unlock the full potential of your PostgreSQL data? The CData PostgreSQL ADO.NET Provider can seamlessly integrate your .NET applications with your PostgreSQL data. With the CData ADO.NET Provider for PostgreSQL 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 PostgreSQL-connected apps today!