Create a console app with .Net Framework 4.7.2 with Dapper and datasource in MSSQL Server or PostgreSQL.

If you create this ancient app, you should only add Dapper and NPGSQL packages from Nuget. The support of MS SQL Server is already built in. The scenario for .Net Framework is from real life, several applications still require this framework.

Add PostgreSQL

Add Nuget packages npgsql and dapper to your project. Postgres is executed from Docker-container and it’s connection string us ‘postgres://postgres:postgrespw@localhost:49153’

MSSQL server is also run with docker, you can read about how to start it here (https://markimarta.com/sql/install-and-run-mssql-server-in-docker/). There is no need to add something for MS SQL Server to the solution.

For demo I will use only 1 class model

   public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string LastName { get; set; }
        public string MiddleName { get; set; }
        public bool IsActive { get; set; }
        public DateTime Birthdate { get; set; } 
    }

The demo is not about SOLID, it’s just a demo!

Create a class Postgres in the file ‘Postgres.cs’

using Dapper;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Linq;

namespace DapperExample
{
    internal class Postgres
    {
        NpgsqlConnection _con;

        public Postgres(NpgsqlConnection con )
        {
            _con = con;
        }

        List<User> GetUsers()
        {
            _con.Open();
            List<User> users = new List<User>();
            using (var cmd = new NpgsqlCommand())
            {
                cmd.Connection = _con;
                users = _con.Query<User>($"SELECT * FROM \"Users\"").ToList();
            }
            return users;
        }

        public void PrintUsers()
        {
            List<User> users = this.GetUsers();
            foreach (var user in users)
            {
                string result = (user.LastName + ": " + user.Name + " : " + user.IsActive + " : " + user.Birthdate);
                Console.WriteLine(result);
            }
        }
    }
}

Create a class MSSQLServer in the file ‘MSSQLServer.cs’

using Dapper;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

namespace DapperExample
{
    internal class MSSQLServer
    {
        SqlConnection _con;

        public MSSQLServer(SqlConnection con )
        {
            _con = con;
        }

        List<User> GetUsers()
        {
            _con.Open();
            List<User> users = new List<User>();
            using (var cmd = new SqlConnection())
            {
                users = _con.Query<User>($"SELECT * FROM \"Users\"").ToList();
            }
            return users;
        }
        
        public void PrintUsers()
        {
            List<User> users = this.GetUsers();
            foreach (var user in users)
            {
                string result = (user.LastName + ": " + user.Name + " : " + user.IsActive + " : " + user.Birthdate);
                Console.WriteLine(result);
            }
        }

    }
}

In Program.cs in ‘void Main’ add this code:

            var con = new NpgsqlConnection(
                connectionString: "Server=localhost;Port=49153;User Id=postgres;Password=postgrespw;Database=postgres;");
            Postgres postgres = new Postgres(con);
            postgres.PrintUsers();


            var mssqlConnectionString = "Server=localhost; Database=TicketsDB; User Id=SA; Password=<YourStrong@Passw0rd>; Trusted_Connection=False; MultipleActiveResultSets=true";
            var connection = new SqlConnection(mssqlConnectionString);
            MSSQLServer mSSQLServer = new MSSQLServer(connection);
            mSSQLServer.PrintUsers();

You can see the source at github: https://github.com/alexbbell/DapperExample