Creating a SQL Database Context in a .NET WinForm Application with EntityFramework

#devexpress #winform #sql #dotnet

I would like to share my solution for creating a SQL database context in a .NET Framework WinForms application. When I was building a DevExpress C# WinForms application, I faced a dilemma. I needed a database context in the sign-up form before entering the main form.

DevExpress provides a sample project called MVVMExpenses, but it is configured with SQLite and excludes user repositories from the original database. So, I managed to find a SQL database context in .NET Framework 4.7.2 and SQL Server that all view models can safely see and use.

The MVVMExpenses project is already configured to use UnitOfWorkSource and IMyDbContextUnitOfWork in its view model instead of using DbContext directly. I don't want to change this unique and solid database working structure, so I decided to turn it into a SQL service.

First, I configured the app.config file to include the connection string and database provider for SQL Server. For this purpose, we can use the SqlClient and its provider.

<connectionStrings>
  <add name="SQLConnection" connectionString="Data Source=DESKTOP-PMO3106;Initial Catalog=logisticdb;User ID=sa;Password=123"/>
</connectionStrings>
<startup>
  <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8"/>
</startup>
<entityFramework>
  <providers>
    <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  </providers>
</entityFramework>
<system.data>
  <DbProviderFactories>
    <remove invariant="System.Data.SqlClient" />
    <add name="SQL Data Provider" invariant="System.Data.SqlClient" description=".NET Framework Data Provider for SQL" type="System.Data.SqlClient.SqlClientFactory, System.Data.SqlClient" />
  </DbProviderFactories>
</system.data>

This configuration specifies that we are using the local SQL Server instance with the database name "logisticdb" and the Entity Framework provider.

Next, we need to confirm if the driver file is loaded correctly. In the Program.cs file, we can add the following code:

static Assembly OnCurrentDomainAssemblyResolve(object sender, ResolveEventArgs args) {
  string partialName = DevExpress.Utils.AssemblyHelper.GetPartialName(args.Name).ToLower();
  if(partialName == "entityframework" || partialName == "system.data.sqlite" || partialName == "system.data.sqlite.ef6" || partialName == "entityframework.sqlserver") {
    string path = Tutorials.FilePathUtils.FindFilePath("Dll\\" + partialName + ".dll", false);
    if(!string.IsNullOrEmpty(path))
      return Assembly.LoadFrom(path);
  }
  return null;
}

Now, we can define the DbContext. We define a CreateConnection function to provide a connection to our DbContext. Here is an example implementation:

public class MyDbContext : DbContext {
  public MyDbContext() : base(CreateConnection(), true) { }
  public MyDbContext(DbConnection connection) : base(connection, true) { }

  static MyDbContext() {
    Database.SetInitializer<MyDbContext>(null);
  }

  public DbSet<Account> Accounts { get; set; }
  public DbSet<Category> Categories { get; set; }
  public DbSet<Transaction> Transactions { get; set; }
  public DbSet<User> Users { get; set; }

  static DbConnection CreateConnection() {
    var connection = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateConnection();
    connection.ConnectionString = "Data Source=DESKTOP-PMO3106;Initial Catalog=logisticdb;User ID=sa;Password=123";
    return connection;
  }
}

In this example, we have models called Account, Category, Transaction, and User. We have already created the corresponding tables in the SQL database, and now we need to create the models in our MVVM project.

[Table("USERLIST")]
public class User {
  [Key, Display(AutoGenerateField = false)]
  public long ID { get; set; }

  [Required, StringLength(30, MinimumLength = 5)]
  public string Username { get; set; }

  [Required, StringLength(30, MinimumLength = 5)]
  public string Password { get; set; }
}

In authentication service, we can use the DbContext directly to retrieve the total usernames:

internal static IEnumerable<string> GetUserNames() {
  using(var context = new MyDbContext()) {
    var users = context.Users.ToList();
    foreach(var user in users) {
      yield return user.Username;
    }
  }
}

There is no problem in using the original database working structure for ViewModel classes. Here is an example of a check function:

internal static bool Check(string login, string pwd) {
  IMyDbContextUnitOfWork work = UnitOfWorkSource.GetUnitOfWorkFactory().CreateUnitOfWork();
  var user = work.Users.FirstOrDefault(u => u.Username == login && u.Password == pwd);
  if (user != null) {
    return true;
  }
  return false;
}

While it is possible to use SqlConnection, SqlCommand, SqlDataReader to execute raw SQL statements for querying data or performing CRUD operations, it becomes more complex when dealing with multiple tables and relations. And the original solution using ContextViewModel, UnitOfWork, and DbContext in the MVVMExpenses project works correctly with the MS SQL Server.

If you are working with grid collection views and single data views for tables and require the same view and CRUD operations, it can save you a lot of time and cost, and DevExpress provides great UI/UX and total compatibility in selecting a database.

Github
Reference Doc
Rock your WinForms Apps with DevExpress MVVM - Youtube