Add Support for DateOnly & TimeOnly types on SqlDataRecord

[dckorben]

Since DateOnly and TimeOnly were added to NET 6, there has been some limited changes to the API to add direct paths for these new types.
 
I'd like to see SetDate and SetTime added to SqlDataRecord to bypass the need to instantiate a DateTime each time for columns with SqlDbType.Date and SqlDbType.Time. Or alternatively, overloads to SetDateTime which take these types directly.

Current workaround is to convert to a full DateTime via .ToDateTime(TimeOnly.MinValue) each time.

[ErikEJ]

Could you provide a code sample?

[dckorben]

The general use is, populate a structured table variable that is sent to a stored procedure, but the SqlDataRecord is reused for each row by convention as to not duplicate the data many times for each row as it is being streamed as it would with say a DataTable. Let me know if this is an insufficient repo. What is handy is the data can be changing up until the moment it is streamed out. I have not used TimeOnly in this way yet but makes sense to include it.

using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.Server;
using System.Collections.Generic;
using System.Data;

#if NET6_0_OR_GREATER
using DateType = System.DateOnly;
#else
using DateType = System.DateTime;
#endif

public class Record
{
    public string Source { get; init; }
    public DateTime UpdateStamp { get; init; }
    public DateType AsOfDate { get; init; }
}

public class RecordStreamer<T>
    where T : Record
{
    const string SourceKey = @"Source";
    readonly int SourceIndex;

    const string UpdateStampKey = @"UpdateStamp";
    readonly int UpdateStampIndex;

    const string AsOfDateKey = @"AsOfDate";
    readonly int AsOfDateIndex;

    SqlDataRecord SqlRecord { get; init; }

    public RecordStreamer()
    {
        //Create Column Metadata
        var columns = new List<SqlMetaData>();

        var sourceColumn = new SqlMetaData(SourceKey, SqlDbType.NVarChar, 128);
        columns.Add(sourceColumn);
        SourceIndex = columns.Count - 1;

        var updateStampColumn = new SqlMetaData(UpdateStampKey, SqlDbType.DateTime);
        columns.Add(updateStampColumn);
        UpdateStampIndex = columns.Count - 1;

        var asOfDateColumn = new SqlMetaData(AsOfDateKey, SqlDbType.Date); //DbType doesn't change since it is a valid SQL Type
        columns.Add(asOfDateColumn);
        AsOfDateIndex = columns.Count - 1;

        //Create SqlDataRecord
        var sqlRecord = new SqlDataRecord(columns.ToArray()); //Must this be an array?
        SqlRecord = sqlRecord;
    }

    public void Execute(SqlConnection connection, IEnumerable<T> data)
    {
        using var command = GetCommand(connection, data);
        Execute(command);
    }

    void Execute(SqlCommand command)
    {
        var connection = command.Connection;
        connection.Open();
        var impactedRows = command.ExecuteNonQuery();
        connection.Close();
    }

    SqlCommand GetCommand(SqlConnection connection, IEnumerable<T> data)
    {
        var command = new SqlCommand("[dbo].[pc_Audit]", connection)
        {
            CommandType = CommandType.StoredProcedure
        };
        _ = AddDataParam(command, data);
        return command;
    }

    SqlParameter AddDataParam(SqlCommand command, IEnumerable<T> data)
    {
        var parameter = new SqlParameter("@Data", SqlDbType.Structured)
        {
            Value = GetSqlDataRecords(data)
        };
        command.Parameters.Add(parameter);
        return parameter;
    }

    IEnumerable<SqlDataRecord> GetSqlDataRecords(IEnumerable<T> data)
    {
        foreach (var row in data)
        {
            if (SetSqlDataRecord(row))
                yield return SqlRecord;
        }
    }

    bool SetSqlDataRecord(T row)
    {
        SqlRecord.SetString(SourceIndex, row.Source);

        SqlRecord.SetDateTime(UpdateStampIndex, row.UpdateStamp);

        SqlRecord.SetValue(AsOfDateIndex , row.AsOfDate); //Fail: Boxes & Cast, though why isn't everything an overload?
        SqlRecord.SetDate(AsOfDateIndex , row.AsOfDate); //Invalid: This would make sense with Net6 and greater
        SqlRecord.SetDateTime(AsOfDateIndex , row.AsOfDate.ToDateTime(TimeOnly.MinValue)); //Workaround
        return true;
    }
}