none
How can I include a RowVerson column in table valued parameter when calling a stored procedure. RRS feed

  • Question

  • If I have a SQLDataRecord that includes a RowVersion column as shown in the code below.   I always get a SQL Server Error 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.

    whether I set the RowVersion value to null or a value.

    I want to be able to send a row version value for existing records and null for new records from ADO.NET to a stored procedure such that I can then using optomistic concurrency checking when merging records.

    How can this be done.

    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);
                }
            }
    



    Friday, November 1, 2013 2:38 PM

All replies

  • Hello Rich,

    It seems that the codes is ok, however, I did the test with my simple store procedure so it may be not correct and it is hardly to do a test without the store procedure.

    So it is appreciate that if you share the SP and some regarding table structure.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 4, 2013 3:49 AM
    Moderator