Dapper DateOnly/TimeOnly

Dapper is a great addition to working with databases when working with a Microsoft data provider that requires a connection and command object and when required command parameters. Dapper at this point in time does not handle DateOnly and TimeOnly Struct.

To handle DateOnly and TimeOnly, the following handler/mappers will provide methods to work with DateOnly and TimeOnly.

public class SqlDateOnlyTypeHandler : SqlMapper.TypeHandler<DateOnly>
{
    public override void SetValue(IDbDataParameter parameter, DateOnly date) 
        => parameter.Value = date.ToDateTime(new TimeOnly(0, 0));
    public override DateOnly Parse(object value) => DateOnly.FromDateTime((DateTime)value);
}

public class SqlTimeOnlyTypeHandler : SqlMapper.TypeHandler<TimeOnly>
{
    public override void SetValue(IDbDataParameter parameter, TimeOnly time)
    {
        parameter.Value = time.ToString();
    }

    public override TimeOnly Parse(object value) => TimeOnly.FromTimeSpan((TimeSpan)value);
}

Usage

Add both or one of the class to a project then in the constructor of the class used to work data with Dapper.

using Dapper;
using Microsoft.Data.SqlClient;
using System.Data;
using kp.Dapper.Handlers;
using sqlserverApp.Models;
using static System.DateTime;

namespace sqlserverApp.Classes;
internal class Operations
{
    private IDbConnection _cn;
    public Operations()
    {
        _cn = new SqlConnection("Your connection string");
        SqlMapper.AddTypeHandler(new SqlDateOnlyTypeHandler());
        SqlMapper.AddTypeHandler(new SqlTimeOnlyTypeHandler());
    }
}

Now when needed to work with DateOnly and/or TimeOnly.

internal class Operations
{
    private IDbConnection _cn;
    /// <summary>
    /// Setup connection and DateOnly and TimeOnly handlers from Dapper.Handlers class project or NuGet package
    /// </summary>
    public Operations()
    {
        _cn = new SqlConnection(ConnectionString());
        SqlMapper.AddTypeHandler(new SqlDateOnlyTypeHandler());
        SqlMapper.AddTypeHandler(new SqlTimeOnlyTypeHandler());
    }

    public List<Test1> GetAll()
        => _cn.Query<Test1>("SELECT Id, DateOnly, TimeOnly FROM dbo.Test1;").ToList();

    public async Task<List<Test1>> GetAllAsync()
        => (await _cn.QueryAsync<Test1>("SELECT id, GenderType FROM Genders")).AsList();

    public bool Update()
    {
        var statement = "UPDATE dbo.Test1 SET DateOnly = @dataonly,TimeOnly = @timeonly  WHERE Id = @id";
        var affected = _cn.Execute(statement, new
        {
            dataonly = DateOnly.FromDateTime(Now),
            timeonly = TimeOnly.FromDateTime(Now),
            id = 10
        });

        return affected == 1;
    }
}

Package the code

When there are many projects that require handlers to work with DateOnly and TimeOnly the smart path is to create a class project with the handlers, compile and pack as an NuGet package so that when needed simply add the package to a project dependencies.

Actually there is no need to package the code as it has already been done.

Download page

If a developer wants to personalize the code, download the source code, modify and pack to a local NuGet package.

To create a local NuGet package source, create a folder, point to the folder as shown below. Once this is done adding the package is done through NuGet package manager.

Summary

By placing the code to handle DateOnly and TimeOnly into a class project or a NuGet package allows easy and consistent access to the handler code without the need to locate the code or copy and paste into a project each time either both or one handler is needed.

Resources

• Dapper at GitHub
• Using Dapper - C# Part 1
• Learn Dapper

Unsure if Dapper is right for you?

Check out this class which was replace with this class which cut the code in half.

License

No credits are required using a modified version of this code.

Source code

Clone this GitHub repository for use without using the NuGet package or placing the package in a local NuGet folder.