WPF Dapper and MySQL a Simple Example

Post Views: 2,196

Table of Contents

WPF Dapper and MySQL a Simple Example

In this project we will create a simple WPF application which contains a DataGrid which is populated from a MySQL database. This is a halfway house between fully defining the connection and queries to using Entity Framework which is a magic black box. Dapper manages some of the magic but still allows us to manually define some aspects, removing some of the risk to a black box solution.

NuGet Packages

For this project we will need two packages;

Dapper
MySql.Data

MySQL Database, Tables, and User

I’m using MariaDB Community Server for this example, which can be downloaded from the following link. After completing the installation i created a new Database called ‘testdb‘ and then created a table called ‘testtable‘. The table has two attributes ‘ID‘ (Primary Key, Not NULL, Auto Increment) and ‘Name‘ (Varchar(50), Not NULL), with a few rows of data.

To access the data I also created a new user with Select, Delete, Insert and Update permissions for only the new table. Each set of tables for an application must have a new user with limited permissions, to maintain security and stop cross application access to the data.

https://mariadb.com/products/community-server/

WPF Application

Within Visual Studio create a new WPF Application in .Net Core 5.0.

Data Model

The model represents the data structure of the database table, this is sometimes referred to as the Data Transport Object (DTO) and can contain mapping from the database table attributes to the property’s within the DTO. Although it’s highly recommended that the DTO should be a one to one mapping of the database table, to keep things simple. The model can be identical to the DTO, and is in this case, normally it represents the data model that best fits the data across multiple applications or at the enterprise level. There is one more type of model which is the View Model which contains the business logic for the model within a specific applications architecture.

Our Model / DTO is very simple with two property’s, that will be used to read / write data to / from the database table.

//DataModel.cs

namespace WPF_Dapper
{
    public class DataModel
    {
        public int ID { get; set; }
        public string Name { get; set; }
    }
}

app.config

The app.config is not a part of the standard WPF project, however it can be created by inserting a new JSON file. This file will be used to store the connection string for the application. This way it can be edited manually once the application is compiled allowing it to be manually configured based on the environment.

//app.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="testtable" connectionString="Server=localhost;Database=testdb;uid=UserName;pwd=Password"/>
  </connectionStrings>
</configuration>

Helper

The Helper class allows us to recover the connection string based on its name.

//Helper.cs

using System.Configuration;

namespace WPF_Dapper
{
    public static class Helper
    {
        public static string Connection(string name)
        {
            return ConfigurationManager.ConnectionStrings[name].ConnectionString;
        }
    }
}

Queries

In this case I’m using a static class to hold all of the queries for the project.

//Queries.cs

using System.Text;

namespace WPF_Dapper
{
    public static class Querys
    {
        public static string GetAllNames()
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("Select * from testtable");

            return stringBuilder.ToString();
        }
    }
}

DataAccess

Now for the main meet and potatoes of the project, Data Access, here I where we need to import via NuGet Dapper, and MySql.Data. Dapper provides a set of API such as Query QueryAsyn, Execute ,ExecuteAsyn etc, that can take in our raw SQL Query and return a collection of objects in the case of Query.

//Dataaccess.cs

using System.Collections.Generic;
using System.Data;
using System.Linq;
using Dapper;
using MySql.Data.MySqlClient;

namespace WPF_Dapper
{
    public interface IDataAccess
    {
        List<DataModel> GetDataModel(string connectionName);
    }
    public class DataAccess : IDataAccess
    {
        public List<DataModel> GetDataModel(string connectionName)
        {
            using (IDbConnection connection = new MySqlConnection(Helper.Connection(connectionName)))
            {
                return connection.Query<DataModel>(Querys.GetAllNames()).ToList();
            }
        }
    }
}

MainWindow.xaml

We can now set up our Data Grid, and bind it to an Observable collection which implements INotifyCollectionChanged, and INotifyPropertyChanged which are required when binding a property.

//MainWindow.xaml

<Window x:Class="WPF_Dapper.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:WPF_Dapper"
        mc:Ignorable="d"
        Title="MainWindow" Height="450" Width="800">
    <Grid>
        <DataGrid ItemsSource="{Binding DataModels}" AutoGenerateColumns="False" 
                  Height="217" HorizontalAlignment="Left" 
                  Margin="73,33,0,0" Name="dataGrid1" 
                  VerticalAlignment="Top" Width="364">
            <DataGrid.Columns>
                <DataGridTextColumn Header="ID" Width="40" Binding="{Binding ID}"></DataGridTextColumn>
                <DataGridTextColumn Header="FirstName" Width="100" Binding="{Binding Name}"></DataGridTextColumn>
            </DataGrid.Columns>
        </DataGrid>
    </Grid>
</Window>

MainWindow.xaml.cs

I’m not being a purist here as far as MVVM, I just wanted to keep it simple in this case and illustrate the usage of Dapper and MySQL.Data. So in the Main Window we will set the Data Context to this and call the Data Access object passing in the name of the connection to be used.

//MainWindow.xaml.cs

using System.Collections.ObjectModel;
using System.Windows;

namespace WPF_Dapper
{
    public partial class MainWindow : Window
    {
        private ObservableCollection<DataModel> _dataModels;
        public ObservableCollection<DataModel> DataModels
        {
            get { return _dataModels; }
            set { _dataModels = value; }
        }
        
        public MainWindow()
        {
            InitializeComponent();
            DataContext = this;
            TestConnection("testtable");
        }

        private void TestConnection(string connectionName)
        {
            IDataAccess db = new DataAccess();
            DataModels = new ObservableCollection<DataModel>(db.GetDataModel(connectionName));
        }
    }
}

Result

Below we can see the simple result.

Stored Procedure

We can also create stored procedures within the database, instead of having to recompile the application every time we want to make a change to a query.

Calling the Stored Procedure from the Code

To call the stored procedure we need to tell the Query command that it’s calling a stored procedure by specifying the command type as Stored Procedure.

//DataAccess.cs

using System.Collections.Generic;
using System.Data;
using System.Linq;
using Dapper;
using MySql.Data.MySqlClient;

namespace WPF_Dapper
{
    public interface IDataAccess
    {
        List<DataModel> GetDataModel(string connectionName);
    }
    public class DataAccess : IDataAccess
    {
        public List<DataModel> GetDataModel(string connectionName)
        {
            using (IDbConnection connection = new MySqlConnection(Helper.Connection(connectionName)))
            {
                return connection.Query<DataModel>(Querys.dboGetAllNames(), commandType: CommandType.StoredProcedure).ToList();
            }
        }
    }
}

Then in the Query’s class we will add a query an additional query which is just the name of the stored procedure. This is preferable as we can easily update the query in the database without rebuilding and distributing the application.

//Querys.cs

using System.Text;

namespace WPF_Dapper
{
    public static class Querys
    {
        public static string GetAllNames()
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("Select * from testtable");

            return stringBuilder.ToString();
        }

        public static string dboGetAllNames()
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("ProcGetAllNames");

            return stringBuilder.ToString();
        }
    }
}

Calling Stored Procedure with Parameters

The new stored procedure takes in a VARCHAR(50) parameter ‘Param1‘.

To call the stored procedure we need to tell the Query command that it’s calling a stored procedure by specifying the command type as Stored Procedure. Also the new stored procedure needs a parameter value this is passed as a generic class that’s constructed on the fly.

//DataAccess.cs

using System.Collections.Generic;
using System.Data;
using System.Linq;
using Dapper;
using MySql.Data.MySqlClient;

namespace WPF_Dapper
{
    public interface IDataAccess
    {
        List<DataModel> GetDataModel(string connectionName);
    }
    public class DataAccess : IDataAccess
    {
        public List<DataModel> GetDataModel(string connectionName)
        {
            using (IDbConnection connection = new MySqlConnection(Helper.Connection(connectionName)))
            {
                var values = new { Param1="Gary" };
                return connection.Query<DataModel>(Querys.dboGetAllNamesLike(), values, commandType: CommandType.StoredProcedure).ToList();
            }
        }
    }
}

Again in the Query’s class we need to just pass in the name of the stored procedure.

//Query.cs

using System.Text;

namespace WPF_Dapper
{
    public static class Querys
    {
        public static string GetAllNames()
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("Select * from testtable");

            return stringBuilder.ToString();
        }

        public static string dboGetAllNames()
        {
            StringBuilder stringBuilder = new StringBuilder("ProcGetAllNames");
            return stringBuilder.ToString();
        }

        public static string dboGetAllNamesLike()
        {
            StringBuilder stringBuilder = new StringBuilder("ProcGetAllNamesLike");
            return stringBuilder.ToString();
        }
    }
}

Here we can see the result where the filtered data from the stored procedure is displayed.