How to pass a timestamp value parameter from C# to SQL

Question

I have a stored procedure where the rowversion is of type Timestamp

CREATE PROCEDURE GetCoverages
    @PageStart INT = 0,
    @PageSize INT = 50000,
    @RowVersion TIMESTAMP = NULL
AS
    SELECT
        * 
    FROM
        Coverages cov
    WHERE
        @RowVersion IS NULL OR cov.RowVersion > @RowVersion
    ORDER BY 
        RowVersion 
        OFFSET @PageStart ROWS
        FETCH NEXT @PageSize ROWS ONLY   
GO

On the C# side, I have this following code:

            var rowVersionParam = new SqlParameter()
            {
                ParameterName = "@RowVersion",
                SqlDbType = SqlDbType.Timestamp,
                Direction = ParameterDirection.Input,
                Value = rowVersion != null ? BitConverter.GetBytes(Convert.ToInt64(rowVersion)) : (object)DBNull.Value
            };

            var prms = new SqlParameter[]
            {
                new SqlParameter("@PageStart", index),
                new SqlParameter("@PageSize", pageSize),
                rowVersionParam
            };

            var coverages = await outerRingDb.Set<SpCoverages>()
                                                .FromSqlRaw("EXEC GetCoverages @PageStart, @PageSize, @RowVersion", prms)
                                                .ToListAsync(cancellationToken);

In my C# code, the rowVersion is of type ulong? (nullable) and when I am including it in my parameter collection, I am converting it to byte array (byte[]) using the BitConverter. In the debug mode, I see that the rowVersion parameter is set to 50353967.

The problem that I am having is that, when I get the latest rowversion (ie. 0x000000000300572F) from a table (this table is used as a record keeper to store the latest rowversion when it was last synced) and when i run the debug run the code, C# executes the stored procedure and tells me that there are no records.

When I execute the stored procedure directly like this:

  EXEC GetCoverages 0, 50000, 0x000000000300572F`

SQL Server returns results.

I am not sure what I am doing wrong here or how the C# is passing my timestamp value to SQL Server. Any insight to this issue would be helpful. Thanks in advance.

Answer 1

I used the Timestamp extension method. What I ended up doing is:

1. Added the Timestamp class

2. Declared the following byte array: byte[] rowVersionInBytes = (Timestamp)rowVersion;

3. In the SqlParameter definition, I replaced the rowVersion conversion:

   var rowVersionParam = new SqlParameter()
           {
               ParameterName = "@RowVersion",
               SqlDbType = SqlDbType.Timestamp,
               Direction = ParameterDirection.Input,
               Value = rowVersion != null ? rowVersionInBytes : (object)DBNull.Value
           };
   

This gave me the correct conversion. Thanks to the developer who wrote the Timestamp.cs extension and thanks to @Grax32 for pointing me in the right direction.

Update:

Alternatively I could do this: BitConverter.GetBytes(Convert.ToUInt64(rowVersion)).Reverse().ToArray()

which gives me the same result.