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.

If you query the record out with ADO.NET (not any other way - must be with ADO.NET) and check the timestamp byte array in C# - does it exactly match the array you are passing in? — mjwills
– mjwills, Commented Jun 5, 2020 at 2:29
You might try Convert.ToUInt64 instead of Convert.ToInt64. The value is an unsigned int. You might also try using Sql Server Profiler to capture the query so that you can see what query C# sent. — Grax32
– Grax32, Commented Jun 5, 2020 at 2:42
I have tried using the Convert.ToUInt64 but I still get the same results. I am yet to try using the SQL Profile...thanks for the idea :) — CrazyCoder
– CrazyCoder, Commented Jun 5, 2020 at 2:44
@Grax32 I looked up the SQL Profiler and the correct value is not being passed. It was passing @RowVersion=0x2F57000300000000 which is different from 0x000000000300572F. Now my next problem is how to I convert this 0x000000000300572F properly — CrazyCoder
– CrazyCoder, Commented Jun 5, 2020 at 2:47
Look into the endian-ness of this. I seem to remember having to reverse the bits or something. — Grax32
– Grax32, Commented Jun 5, 2020 at 2:52

CrazyCoder · Accepted Answer · 2020-06-05 03:51:56Z

4

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

Added the Timestamp class
Declared the following byte array: byte[] rowVersionInBytes = (Timestamp)rowVersion;

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.

edited Jun 5, 2020 at 3:51

answered Jun 5, 2020 at 3:41

CrazyCoder

5171 gold badge6 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Grax32 Over a year ago

Thanks for posting this answer so we can see how it turned out.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related