asp:SqlDataSource with ROWVERSION (TIMESTAMP)

Question

I ran into .NET (Framework, w/ WinForm and WebForms) / MS SQL project where significant tables in the database contain a TIMESTAMP (aka ROWVERSION) column (called tsModified) to prevent concurrency issues. Additionally, this project does not allow the application layer to interact directly with the tables (instead all CRUD and business logic must be done through Stored Procedures).

One of the things that has been driving me crazy is how to use an SqlDataSource that can account for the TIMESTAMP column during UPDATE.

The basic form of the CRUD procs are like this:

CREATE PROC Customer_List
  @pk_Customer INT = null
SELECT id, name, tsModified 
FROM Customer 
WHERE @pk_Customer IS NULL OR @pk_Customer = id;

CREATE PROC Customer_Update
  @id INT,
  @name varchar,
  @tsMod TIMESTAMP
IF NOT EXISTS (SELECT TOP 1 1 FROM Customer where id=@id and tsModified=@tsModified)
    Return; --actually RAISEERROR w/ a concurrency alert telling the user to refresh & try again

UPDATE Customer SET [param/value pairs] WHERE id = @id;

Sure, you could manually define partial classes and methods to account for tsModified, and then use asp:ObjectDataSource, but that is extra work. I just want the convenience of dropping an asp:SqlDataSource on the form, and get on with my day.

BUT... the SqlDataSource does not like the TIMESTAMP as a parameter. In fact, I've literally spent days researching how to make this work, and ran into plenty of others having the same issue.

I finally figured it out. See answer below.

Answer 1

Here's how you can use a MS SQL ROWVERSION (TIMESTAMP) column with an asp:SqlDataSource while using Stored Procedures, to handle concurrency.

Set up your SqlDataSource like this:

<asp:SqlDataSource ID="dsRegs" runat="server" OnUpdating="dsRegs_Updating" ConnectionString="[your connstr]" InsertCommand="RegulatoryAgency_Insert" InsertCommandType="StoredProcedure" SelectCommand="RegulatoryAgency_List" SelectCommandType="StoredProcedure" UpdateCommand="RegulatoryAgency_Update" UpdateCommandType="StoredProcedure">
            <InsertParameters>
                <asp:Parameter Name="RegulatoryCode" Type="String" />
                <asp:Parameter Name="RegulatoryName" Type="String" />
                <asp:Parameter Name="RegulatoryState" Type="String" />
            </InsertParameters>
            <SelectParameters>
                <asp:Parameter Name="pk_RegulatoryAgency" Type="DBNull" />
            </SelectParameters>
            <UpdateParameters>
                <asp:Parameter Name="pk_RegulatoryAgency" Type="Int32" />
                <asp:Parameter Name="RegulatoryCode" Type="String" />
                <asp:Parameter Name="RegulatoryName" Type="String" />
                <asp:Parameter Name="RegulatoryState" Type="String" />
                <asp:Parameter Direction="InputOutput" Name="tsModified" Type="Empty" />
            </UpdateParameters>
        </asp:SqlDataSource>

The important things to notice are:

1. In UpdateParameters, tsModified is the TIMESTAMP value and the Type="Empty".
2. OnUpdating is set to the dsRegs_Updating event.

Now the code behind:

    /// <summary>
    /// When editing for this record/row begins in the grid, we need to get the primary key from the row, 
    /// and then stuff the TIMESTAMP (tsModified) into a Session variable so it persists
    /// </summary>
    protected void gvRegs_StartRowEditing(object sender, DevExpress.Web.Data.ASPxStartRowEditingEventArgs e)
    {
        int pk = (int)e.EditingKeyValue;
        var db = new myDataContext();
        var ra = db.RegulatoryAgency_List(pk).First();
        Session["tsModified"] = ra.tsModified;
    }

    /// <summary>
    /// Before we call the database, convert the Session var back the original Linq-to-SQL type (System.Data.Linq.Binary), then
    /// convert it to a (byte) array, and update the SqlDataSource parameter with the correct value.
    /// </summary>
    protected void dsRegs_Updating(object sender, SqlDataSourceCommandEventArgs e)
    {
        DbParameter dp = e.Command.Parameters["@tsModified"];
        dp.Value = ((System.Data.Linq.Binary)Session["tsModified"]).ToArray();
    }

In this example, the front is using a DevExpress ASPxGridView, but the databinding and events should be similar on other databound controls. When the row editing begins, we pull the tsModified value for the record from the database and place it into a Session variable. Then the SqlDataSource fires its Updating event, we grab the Session variable, convert it back to it's original format (in my case a System.Data.Linq.Binary because this example is using Linq-to-SQL), and finally the last trick is that you can't pass the TIMESTAMP value as binary, varbinary, or byte -- is must be sent as btye[], which the .ToArray() is taking care.

With the code like this, I'm able to successfully SELECT, INSERT, UPDATE and DELETE through the SqlDataSource, and the tsModified (TIMESTAMP) value in the database increments as expected.