Limited DateOnly C# support when using SqlParameter.Structured #2209
Description
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
)
GOsql - 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