c# - 调用存储过程时如何在表值参数中包含 RowVerson 列？

Question

如果我有一个包含 RowVersion 列的 SQLDataRecord，如下面的代码所示。我总是收到 SQL Server 错误 8052

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter %d ("%.*ls"), row %I64d, column %d: Data type 0x%02X (user-defined table type) timestamp column is required to be default.

我是将 RowVersion 值设置为 null 还是一个值。

我希望能够将现有记录的 RowVersion 值和新记录的 null 从 ADO.NET 发送到存储过程，这样我就可以在合并记录时使用光学并发检查。

如何做到这一点？

namespace MyApplication.DataAccess
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using Microsoft.SqlServer.Server;

    public interface IRowVersionRecord
    {
        Byte[] RowVersionValue { get; set; }
        Int32? Somevalue { get; set; }
    }

    public class RowVersionRecordBase : IRowVersionRecord
    {
        public Byte[] RowVersionValue { get; set; }
        public Int32? Somevalue { get; set; }

        public static IEnumerable<SqlDataRecord> CreateSqlDataRecordEnumerable(IEnumerable<IRowVersionRecord> dataTransferObjects)
        {
            var sqlMetaData = new SqlMetaData[]
            { new SqlMetaData("RowVersionValue", SqlDbType.Timestamp),
                new SqlMetaData("somevalue", SqlDbType.Int)
            };
            var record = new SqlDataRecord(sqlMetaData);
            foreach (var dto in dataTransferObjects)
            {
                record.SetValue(0, dto.RowVersionValue);
                record.SetValue(1, dto.Somevalue);
                yield return record;
            }
        }
    }

    public partial class RowVersionRecord : RowVersionRecordBase
    {
    }
}

namespace MyApplication.DataAccess
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;

    public abstract class RowVersionDAOBase : DAO
    {
        public virtual void Upsert(IEnumerable<RowVersionRecord> values)
        {
            using (var connection = Connection)
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "RowVersion_Upsert";
                    ((SqlCommand)command).Parameters.Add("@values", SqlDbType.Structured).Value =
                        (object)RowVersionRecordBase.CreateSqlDataRecordEnumerable(values) ?? (object)DBNull.Value;                    
                    using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SequentialAccess))
                    {
                        while (reader.NextResult())
                        {
                            ///.....
                        }
                    }
                }
            }
        }
    }

    public partial class RowVersionDAO : RowVersionDAOBase
    {
    }
}

//Test code snip
[TestMethod]
public void RowVersionTVPTest()
{
    RowVersionRecord record = new RowVersionRecord();
    record.Somevalue = 1;
    List<RowVersionRecord> records = new List<RowVersionRecord>()
    {
        record
    };
    using (RowVersionDAO dao = new RowVersionDAO())
    {
        dao.Upsert(records);
    }
}

Answer 1

您永远无法控制 rowversion 列的内容 - 但您已表示要为现有行提供特定值。

这表明您应该使用 binary(8) or varbinary(8) (分别取决于 not-NULL/NULL)作为 TVP 中该列的数据类型。