Windows Forms: Entity Framework Core Reverse Engineering databases

Article
1/17/2024

Introduction

Working with Entity Framework 6 classic in windows form project there are four method to work with data, from a designer to reverse engineer a database, start with a blank slate in a designer or a set of classes which would be use to perform migrations for generating a database and tables or reverse engineer using a wizard. In all of these options, a DbContext and classes for representing data reside in the base folder of a project. Each of these options may be done visually with helpers within Visual Studio via Add new item. To see how a Visual Studio solution looks like in a Windows Form solution see the following TechNet Wiki article and for ASP.NET 5 see the following TechNet Wiki article.

With Entity Framework Core, reverse engineering is performed using scaffolding using NuGet Package Manager Console with no user interface like with Entity Framework classic. To generate a DbContext and models to represent database developers need to write out a command with several switches that can be time-consuming and incorrect from mistyping various parts of the PowerShell command.

The focus of this article is to provide a tool to assist with creating a command to reverse engineer a database without the need to write out a script.

Reverse engineering

Reverse engineering is the process of scaffolding entity type classes and a DbContext class based on a database schema. It can be performed using the Scaffold-DbContext command of the EF Core Package Manager Console (PMC) tools or the dotnet ef dbcontext scaffold command of the .NET Command-line Interface (CLI) tools. In this article, the PMC will be used, not the CLI tools method.

The following shows a simple command to create a DbContext and classes to represent data in a SQL-Server database.

Scaffold-DbContext 
    "Server=.\SQLEXPRESS;Database=DateTimeDatabase;Trusted_Connection=True;"
    -Provider Microsoft.EntityFrameworkCore.SqlServer 
    -t "Events","TimeTable"

Breaking down this command, the first argument indicates a server and database on the server following by - t which list the tables to scaffold. A DbContext and two classes are generated in the root folder of the current project.

A robust example Which indicates where classes are placed, in this example the following switches are used, many have both a full name and a shortcut name, for example, -v is a shortcut name for -Verbose,

-OutputDir indicates the folder for classes which represent tables in a database. When working with multiple databases each database should be placed into different folders for separation of the models.
-ContextDir indicates the folder to place the DbContext class. If multiple databases are used, all DbContext classes are placed here with unique names.
-Context is the name of the DbContext class for the classes under the model folder (folder indicated under -OutputDir)
-Project is the project folder, in this case, a class project.
-StartupProject tells NuGet which project to target.
-Verbose output which executing the command
-Force for overwriting existing classes if they exist presently.

Scaffold-DbContext 
    "Server=.\SQLEXPRESS;Database=DateTimeDatabase;Trusted_Connection=True;"
    -Provider Microsoft.EntityFrameworkCore.SqlServer 
    -OutputDir DateModels 
    -Context DateTimeContext  
    -v 
    -f  
    -DataAnnotations  
    -project NorthWind.Data 
    -startupproject NorthWind.Data 
    -ContextDir Contexts 
    -t "Events","TimeTable"

Important
Running the command in a multi-project solution where there is a Windows Form and Class project generally the form project is set as the startup project. When running the command the scaffolding process will indicate the build failed as the startup project does not have the package Microsoft.EntityFrameworkCore.Tools installed. To fix this in Package Manager Console select the class project in the Default project dropdown then run the command again. Note there are switches to handle this for you which will be gone over later.

Using Entity Framework Tools

In short, write out the command using the following common parameters where if there are typos or incorrectly specifying a switch or missing a switch the command will need to be modified and executed again.

-Connection <String>	The connection string to the database. For ASP.NET Core 2.x projects, the value can be name=<name of connection string>. In that case the name comes from the configuration sources that are set up for the project. This is a positional parameter and is required.
-Provider <String>	The provider to use. Typically this is the name of the NuGet package, for example: Microsoft.EntityFrameworkCore.SqlServer. This is a positional parameter and is required.
-OutputDir <String>	The directory to put files in. Paths are relative to the project directory.
-ContextDir <String>	The directory to put the DbContext file in. Paths are relative to the project directory.
-Context <String>	The name of the DbContext class to generate.
-Schemas <String[]>	The schemas of tables to generate entity types for. If this parameter is omitted, all schemas are included.
-Tables <String[]>	The tables to generate entity types for. If this parameter is omitted, all tables are included.
-DataAnnotations	Use attributes to configure the model (where possible). If this parameter is omitted, only the fluent API is used.
-UseDatabaseNames	Use table and column names exactly as they appear in the database. If this parameter is omitted, database names are changed to more closely conform to C# name style conventions.
-Force	Overwrite existing files.

Requirements

The first step is to create a class project as placing classes for Entity Framework Core in a class project allows these classes to be used in multiple projects and when moving to a web project these classes have nothing to do with Windows Forms.

Once a class project has been created use NuGet Package Management Console in Visual Studio to add a reference to Microsoft.EntityFrameworkCore.Tools. This is followed by installing a data provider for your database. To learn which provider to use see Database Providers.

For this article SQL-Server database provider Microsoft.EntityFrameworkCore.SqlServer will be used. In the source code provided there is a class to store information about database providers.

SQL-Server ScaffoldDbContextHelper utility

Rather than writing out a command to scaffold a database, especially with many tables the following Windows Form project provides point and click for the majority of options for scaffolding databases.

This utility builds a scaffold command using form controls for SQL-Server. There are several parts currently for changing the data provider but are not intended to use as a separate project is partially done for Microsoft Access to perform scaffolding same as done with SQL-Server. Oracle and other databases that have Entity Framework Core providers can be scaffolded too.

Setting

Open the Visual Studio solution.
Set the project SqlServerScaffoldDbContextHelper as the startup project.
Open ApplicationSetting.json and set LastServerName to your SQL-Server instance name.
Build the solution.

Creating a scaffold command

Run the project, given the server name used in ApplicationSettings.json was accessible the left most ListBox will be populated with databases on the server.

Double click a database that populates the CheckedListBox with table names, each table is checked. When generating models those checked tables will be scaffolded, uncheck any that should not be scaffolded.

Note when running the command later on if a table does not have a primary key Entity Framework will not create a class for that table.

Next step, select the project where scaffolding will be performed, note the text (optional) which means this could be left out yet this is an important aspect of scaffolding, otherwise the startup project will be the startup project which if Entity Framework Tools is not installed the process errors out.

Clicking the button above ask for a Visual Studio solution to work against. Any solution can be selected via an OpenFileDialog, in the screenshot below the current solution has been selected.

Double click the project to house the context and model. Next type in a name for the models, if excluded the models will be placed in the root folder for the project selected above. This is followed by typing in a folder name for the context, if left empty the context is placed in the root folder of the project selected above. Next, provide a context name.

The selections here are to not place models or contexts in the root folder.

These selections are fine for working with one database, if other databases are to be used the Models folder, in this case, would be NorthModels, Contexts folder would keep this name as many context classes may be placed into this folder.

Although the average project would not have one then perhaps two databases, if there are more than using the logic above, provides a nice separation of databases. In the screenshot below there are several databases where all were generated using this utility.

Once a database has been selected, tables in the database selected followed by providing a project, folder names and context name for the first run the checkboxes can be left as is. Clicking the Generate button creates a command to scaffold against your options into a TextBox. Click the button to the right of the TextBox to copy the command to the Windows Clipboard.

If the project is in the same solution then open up the NuGet package manager console. Run the following command to install the Entity Framework Core tools.

Install-Package Microsoft.EntityFrameworkCore.Tools -Version 2.2.6

Followed by

Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 2.2.6

Now enter the command from the TextBox into the package manager console and press enter. As code is being generated the results are shown in the IDE Output window along with visually seeing folders and classes being generated in the selected project. Once completed building this project to ensure everything works as expected.

Using the tool standalone

To access the tool for any project

From Visual Studio's menu select Tools -> External Tools
Click the Add button
Click the button for Command
Traverse to the folder for this tool and select the executable
Optionally reposition via the Move Up/Down buttons
Press the OK button.

Set SolutionFileDialog1.InitialDirectory (as in the Shown event of the main form) to a common root folder e.g. If all your Visual Studio solutions were under C:\DotNet this is the folder to set.

public MainForm()
{
    InitializeComponent();
    Shown += Form1_Shown;
 
    ListBoxSearchTextBox.TextChanged += ListBoxSearchTextBox_TextChanged;
    SolutionFileDialog1.InitialDirectory = AppDomain.CurrentDomain.BaseDirectory.CurrentSolutionFolder();
    ServerNameTextBox.Text = _scaffoldBuilder.ServerName;
 
    ReadApplicationSettingsFile();
}

Microsoft Access ScaffoldDbContextHelper utility

This utility creates a DbContext and models for Microsoft Access databases using a NuGet package EntityFrameworkCore.Jet. Unlike SQL-Server which requires a server and tables from a database here, a database and tables are required. The rest is the same as the SQL-Server utility.

One caveat, after the DbContext has been created a reference will be needed for the Jet provider.

using EntityFrameworkCore.Jet;

Learning points

There are both tools and Visual Studio extensions for scaffolding out on the web and Visual Studio Marketplace to choose from while this utility provides a simple solution that may easily be modified if needed.

What can be learned here is how to write efficient and solid code by taking time to follow code flow along with the understanding that code within forms must be there while code that does not have to be within a form has been broken out to classes. When a person begins their journey writing code for Windows Forms most will write the majority of code in their forms then later learn parts of code in a form are needed in another form or that they need to access variables and/or properties in another form which leads to global variables or wrongly exposing properties within a form.

Examples

Using asynchronous code

Rather than write code in a form to obtain available SQL-Servers for the current computer, place the method in a class that provides a central location for other forms to use. Since GetDataSources will freeze up an application leaving the user thinking the application (their a utility or any application written) has crashed executing GetDataSources in a Task means the application is still responsive.

/// <summary> 
/// Determine if SQL-Server is available 
/// </summary> 
/// <returns></returns> 
public async Task<DataTable> SqlServerInstances()
{
    mHasException = false;
 
    try
    {
        return await Task.Run(() =>
        {
            var sqlDataSourceEnumeratorInstance = SqlDataSourceEnumerator.Instance;
            DataTable dt = sqlDataSourceEnumeratorInstance.GetDataSources();
            return dt;
        });
    }
    catch (Exception ex)
    {
        mHasException = true;
        mLastException = ex;
    }
 
    return new  DataTable();
}

The caller, a button Click event shown below demonstrates how to properly call the method above by using async keyword and await operator along with ConfigureAwait(false) so that all code following this line will not be prematurely executed. Another important aspect writing asynchronous code is interacting with form controls, in this case a TextBox, for a in-depth understanding read the following Microsoft article Calling Synchronous Methods Asynchronously.

private async void ServerButton_Click(object sender, EventArgs e)
{
    var columnName = _scaffoldBuilder.ServerName.ToLower().Contains("sqlexpress") ? "InstanceName"  : "ServerName";
 
    var ops = new  SqlServerUtilities();
    var serverDataTable = await ops.SqlServerInstances().ConfigureAwait(false);
    var serverNameList = serverDataTable.AsEnumerable()
        .Where(row => !string.IsNullOrWhiteSpace(row.Field<string>(columnName)))
        .Select(row => row.Field<string>(columnName)).ToList();
 
    var serverForm = new  ServersForm(serverNameList);
    if (serverForm.ShowDialog() == DialogResult.OK)
    {
        if (string.IsNullOrWhiteSpace(serverForm.ServerName)) return;
 
        ServerNameTextBox.Invoke(serverForm.ServerName == "SQLEXPRESS"
            ? new  Action(() => ServerNameTextBox.Text = $@".\{serverForm.ServerName}")
            : new  Action(() => ServerNameTextBox.Text = $"{serverForm.ServerName}"));
        SaveApplicationSettings();
    }
}

Not everything needs to be store in databases or project settings

A common practice is to store information needed at runtime is to store this information in a database table, application/project settings or a JSON file. Selecting the proper choice should be done examining what fits best for a given set of data and if these settings should be changeable at runtime. In the case of getting data providers that drive scaffolding a simple class will do rather than conventional methods mentioned prior.

The following class will be used to select from in a ListBox where ListBox.DataSource will be set to a list of DatabaseProvider. The ToString method is overridden so that DisplayMember needs not to be set for the ListBox and also when debugging the list the name property shows up rather than the type.

namespace CommonLibrary
{
    public class  DatabaseProvider
    {
        public string  Name { get; set; }
        public string  Type { get; set; }
        public override  string ToString()
        {
            return Name;
        }
    }
}

The list is hard coded as there is never a need to add or modify a provider at runtime for this utility. For other utilities and apps that may not be the case which is why the decision to use this or another logical container needs to be considered.

External resources

EF Core Tools & Extensions

Summary

In this article, tools have been presented to create a scaffolding script for Microsoft SQL-Server and Microsoft Access databases for Entity Framework Core to use in Window Forms projects. There are both tools and Visual Studio extensions to perform scaffolding while these tools are basic, more than enough to creates DbContext and models for projects along with the ability to modify the tools presented while other tools and extensions may not have source code or have extremely complex source code not easy to modify. These tools provide a step into starting out with Entity Framework Core in Windows Forms projects.

Source code

The following repository contains code for this article. Currently, SQL-Server builder is fully working while Microsoft Access builder is a work in progress and will be adding a builder for Oracle down the road.

Share via