Dapper DateOnly/TimeOnly
Tags: csharp, dotnetcore, api
Published: 2024-01-15 14:41:40

[Dapper](https://github.com/DapperLib/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](https://learn.microsoft.com/en-us/dotnet/api/system.dateonly?view=net-8.0) and TimeOnly Struct.

To handle DateOnly and TimeOnly, the following [handler/mappers](https://github.com/DapperLib/Dapper/blob/main/Dapper/SqlMapper.cs) will provide methods to work with DateOnly and [TimeOnly](https://learn.microsoft.com/en-us/dotnet/api/system.timeonly?view=net-8.0).

```csharp
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.

```csharp
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.

```csharp
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.


![NuGet page for DateOnly and TimeOnly for Dapper](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ftmvmypxenrszl5zb9sx.png)

[Download page](https://www.nuget.org/packages/kp.Dapper.Handlers/1.0.0?_src=template#supportedframeworks-body-tab)

If a developer wants to personalize the code, download [the source code](https://github.com/karenpayneoregon/dapper-libraries/tree/master/Dapper.Handlers), 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. 


![NuGet package manager in Microsoft Visual Studio 2022](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2lj5qkcwhvtbgkztcorm.png)

## 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](https://github.com/DapperLib/Dapper) at GitHub
- [Using Dapper - C# Part 1](https://dev.to/karenpayneoregon/working-with-dapper-in-c-5kd)
- [Learn Dapper](https://www.learndapper.com/)

## Unsure if Dapper is right for you?

Check out this [class](https://github.com/karenpayneoregon/WindowsFormsMoveItemsUpDown/blob/master/DataGridViewCore/Classes/Operations.txt) which was replace with this [class](https://github.com/karenpayneoregon/WindowsFormsMoveItemsUpDown/blob/master/DataGridViewCore/Classes/Operations.cs) which cut the code in half.

## License 

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

## Source code

Clone this [GitHub repository](https://github.com/karenpayneoregon/dapper-libraries/tree/master) for use without using the NuGet package or placing the package in a local NuGet folder.