DapperLib / DapperLib/Dapper

TypeHandler not used for null values

Open
#1,234 2 comments 3 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
C#
Stars
18.4k
Forks
3.7k
Avg merge
5h 8m
Merged PRs (30d)
1

Description

Custom type handler is not called by Query* or Execute* methods for null values. In the following sample for Microsoft SQL I expect to insert DbNull value into the table if Foo.Data is null. Instead, an exception is thrown due to an invalid query parameter type (nvarchar instead of varbinary).

using Dapper;
using System;
using System.Data;
using System.Data.SqlClient;

namespace DapperBug
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlMapper.AddTypeHandler(new ValuesHandler());

            var connectionString = "Data Source =.; Initial Catalog = tempdb; Integrated Security = True;";
            var sql = @"CREATE TABLE #Test (Id int IDENTITY(1,1) PRIMARY KEY, Data binary(2));
                        INSERT INTO #Test (Data) VALUES (@Data); SELECT SCOPE_IDENTITY() AS Id";
            using (var connection = new SqlConnection(connectionString))
            {
                var foo = new Foo();
                int id = connection.ExecuteScalar<int>(sql, foo);
                Console.WriteLine(id);
            }
        }
    }

    class Values
    {
        public Values() { }
        public Values(byte first, byte second)
        {
            First = first;
            Second = second;
        }

        public byte First { get; set; }
        public byte Second { get; set; }
    }

    class Foo
    {
        public int Id { get; set; }
        public Values Data { get; set; }
    }

    class ValuesHandler : SqlMapper.TypeHandler<Values>
    {
        public override Values Parse(object value)
        {
            if (value is byte[] bytes)
            {
                return new Values(bytes[0], bytes[1]);
            }

            return null;
        }

        public override void SetValue(IDbDataParameter parameter, Values value)
        {
            parameter.DbType = DbType.Binary;
            if (value == null)
            {
                parameter.Value = DBNull.Value;
            }
            else
            {
                parameter.Value = new byte[2] { value.First, value.Second };
            }
        }
    }
}

The resulting request on the server:

exec sp_executesql N'CREATE TABLE #Test (Id int IDENTITY(1,1) PRIMARY KEY, Data binary(2));
INSERT INTO #Test (Data) VALUES (@Data); SELECT SCOPE_IDENTITY() AS Id',N'@Data nvarchar(4000)',@Data=NULL

I'm trying to use a custom type handler for Systems.Drawing.Image

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the sample against Microsoft SQL and trace parameter handling from the Query* or Execute* methods when Foo.Data is null. Done means the custom ValuesHandler is called, the parameter is configured as binary, and the request inserts DBNull rather than using nvarchar.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.