Using TableValuedParameter with Dapper Conversion Fails for string to smalldatetime

Question

I get this error when converting a datatable to a TableValuedParameter using dapper. Am I missing something in my conversion to datatable, does DBNull.Value work with nullable datetimes? Is there a mismapped property I'm not seeing in my code?

System.Private.CoreLib: Exception while executing function: MyProject. Core .Net SqlClient Data Provider: Conversion failed when converting character string to smalldatetime data type.

I create the List of data to the datatable

List<PersonData> personDatas = maps.Select( mappedData =>
    new PersonData
    {
        PersonId = mappedData.PersonId,
        StartDate = mappedData.StartDate,
        EndDate = mappedData.EndDate,
        CurrentDate = DateTime.Now,
        SomeString = mapped.SomeStringData
    })
    .ToList()
    .ToDataTable();

Method for ToDataTable can be found here

PersonData is defined as

public class PersonData
{
    public int PersonId { get; set; }
    public DateTime? StartDate { get; set; }
    public DateTime? EndDate { get; set; }
    public string SomeString { get; set; }    
    public DateTime? CurrentDate { get; set; }
}

Then I execute the query with Dapper like this

using (var cn = new SqlConnection(connectionString))
{
    cn.Open();
    //This is where the exception happens
    cn.Execute(RepositorySql.MergeSql(), new
    {
        UserDefinedTable = dataTable.AsTableValuedParameter("[dbo].[PersonData]")       
    });
    cn.Close();
}

the Sql is

MERGE dbo.tblPersonData AS target
USING @UserDefinedTable AS source
ON target.PersonID = source.PersonID
    WHEN MATCHED
    THEN UPDATE SET 
                    StartDate = source.StartDate, 
                    EndDate = source.EndDate,
                    --UpdatedDate instead of InsertedDate
                    UpdatedDate = source.CurrentDate,
                    SomeString = source.SomeString
    WHEN NOT MATCHED BY TARGET
    THEN
        --Inserted date instead of UpdatedDate
        INSERT(PersonID, StartDate, EndDate, InsertedDate, SomeString)
        VALUES
            (source.PersonId, source.StartDate, source.EndDate, source.CurrentDate, source.SomeString)

The user Defined Table is

CREATE TYPE [dbo].[PersonData] AS TABLE(    
   PersonId int NOT NULL
   ,SomeString varchar(50) null
   ,StartDate smalldatetime null
   ,EndDate smalldatetime null
   ,CurrentDate smalldatetime null)

score 1 · Accepted Answer · answered Jan 02 '20 at 21:48

The problem was because the serialization of the object was putting the columns in different order than the User Defined Table so they where trying to save dates into teh string column. I changed the object to this and the error was resolved, notice the order of properties has changed:

public class PersonData
{
    public int PersonId { get; set; }
    public string SomeString { get; set; } 
    public DateTime? StartDate { get; set; }
    public DateTime? EndDate { get; set; }   
    public DateTime? CurrentDate { get; set; }
}

Using TableValuedParameter with Dapper Conversion Fails for string to smalldatetime

1 Answers1