Dapper Micro-ORM

2023-01-04 C# Dapper SQL Database

A high performance Micro-ORM supporting SQL Server, MySQL, Sqlite, SqlCE, Firebird etc..

In my opinion, the Dapper is nice middle ground between Full ORM like EntityFramework and plain ADO.net. It will let you write your SQL, but handle inflating of the objects from each row, properly converting the SQL data types into C# types.

An example might be something like this:

public class AccessGroup
{
    public int ID { get; set; }
    public string Group { get; set; }
}

public enum UserStatus
{
    Active = 1,
    NoActive = 2,
}

public class User
{
    public int UserID { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string LastLogin { get; set; }
    public UserStatus Status { get; set; }
    public AccessGroup AccessGroup { get; set; }
}

public class UserRepository
{
    public IEnumerable<User> GetAll()
    {
        return connection.Query<User, AccessGroup, User>(@"
            SELECT
                user.`id`               AS `UserID`
              , user.`name`             AS `Name`
              , user.`email`            AS `Email`
              , user.`last_login`       AS `LastLogin`
              , user.`status`           AS `Status`
              , ag.`id`                 AS `ID`
              , ag.`group`              AS `Group`
            FROM 
                user JOIN user_access_group ag ON user.access_group = ag.id",
            (user, ag) => { user.AccessGroup = ag; return user; },
        );
    }
}

The example shows creating enumerable of User objects with populated properties, including creating AccessGroup objects and linking them through AccessGroup property of the User. This allows me to keep more control about actual queries and avoid writing mapping code.