Limited DateOnly C# support when using SqlParameter.Structured

[PK-ByTE]

C# DateOnly not supported when using SqlParameter.Structured (Datatable)

Current .net 8 rc2 works with DateOnly type when passing DateOnly values to sql procedure like so:

But if we try to pass the DateOnly as udtt via SqlParameter.Structured we get an error like so:

To reproduce

Program.cs

 internal class Program
 {
     static async Task Main(string[] args)
     {
         var x = new TestAppSimple();
         await x.TestDataGet(); 
     }
 }

TestAppSimple.cs

 internal class TestAppSimple
{
    private SqlConnection Connection { get; set; }

    public TestAppSimple()
    {
        var connectionString = "Server=localhost;..."; 
        Connection = new SqlConnection(connectionString);

    }

    public async Task TestDataGet()
    {
        try
        {
            Connection.Open();

            await TryToPassDateOnlyAsParamSimple(); //Works!
            await TryToPassDateOnlyAsStructuredParamSimple(); //Fails!
        }
        catch(Exception e)
        {
            Console.WriteLine(e.Message);
        }
        finally
        {
            Connection?.Close();
            Connection?.Dispose();
        }
    }

    #region Sql tests

    private async Task TryToPassDateOnlyAsParamSimple()
    {
        try
        {
            var command = Connection.CreateCommand();
            command.CommandText = "[dbo].[spTestDateOnly]";
            command.CommandTimeout = 30;
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@date",
                SqlDbType = SqlDbType.Date,
                Value = new DateOnly(2023, 11, 15)
            });

            using (var reader = await command.ExecuteReaderAsync())
            {
                //if passes here, then it worked.
            }

        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
    }

    private async Task TryToPassDateOnlyAsStructuredParamSimple()
    {
        try
        {
            var command = Connection.CreateCommand();
            command.CommandText = "[dbo].[spTestDateOnlyUdtt]";
            command.CommandTimeout = 30;
            command.CommandType = CommandType.StoredProcedure;

            //init param data
            var dates = new List<DateOnly>(new[] { new DateOnly(2023, 11, 15), new DateOnly(2022, 10, 20) });

            //populate dt parameter
            var dtDates = new DataTable();
            dtDates.Columns.Add(new DataColumn("Date", typeof(DateOnly)));

            foreach (var date in dates)
            {
                var dataRow = dtDates.NewRow();
                dataRow["Date"] = date;
                dtDates.Rows.Add(dataRow);
            }

            //pass dt parameter to procedure
            command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@dates",
                SqlDbType = SqlDbType.Structured,
                TypeName = "[dbo].[udttDateOnly]",
                Value = dtDates
            });


            using (var reader = await command.ExecuteReaderAsync())
            {
                //if passes here, then it worked.
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    }

    #endregion
   
}

sql - udttDateOnly

  CREATE TYPE [dbo].[udttDateOnly] AS TABLE(
     [Date] DATE NULL
 )
 GO

sql - spTestDateOnlyUdtt

 CREATE OR ALTER PROC [dbo].[spTestDateOnlyUdtt]
 (
   @dates [dbo].[udttDateOnly] READONLY
 )
 AS
     SET NOCOUNT ON;

     DECLARE @context NVARCHAR(255) = '[spTestDateOnlyUdtt]'

     SELECT COUNT(*) FROM @dates;
	
 GO

sql - spTestDateOnly

CREATE OR ALTER PROC [dbo].[spTestDateOnly]
(
    @date DATE
)
AS
    SET NOCOUNT ON;

    DECLARE @context NVARCHAR(255) = '[spTestDateOnly]'

    PRINT @date
	
GO

Expected behavior

SqlParameter.Structured should pass the provided/populated udtt to the sql procedure.

Further technical details

Microsoft.Data.SqlClient version: 5.2.0-preview3.23201.1
.NET target: 8.0.100-rc.2.23502.2
SQL Server version: SQL Server 16.0.1105.1
Operating system: Windows 11 Business 23H2

[JRahnama]

@PK-ByTE thank you for bringing this up. We will look into this and will update you.

[Wraith2]

/cc @ErikEJ

[ErikEJ]

@Wraith2 @JRahnama Yeah, diving into the 4000 line src\Microsoft.Data.SqlClient\src\Microsoft\Data\SqlClient\Server\ValueUtilsSmi.cs class 😄