Dealing with TimeStamp columns with SubSonic 3 and MS Sql Server

Today someone asked a question at StackOverflow
regarding problem with SubSonic, not allowing to update record in table with "TimeStamp" typed column. I've been SS enthusiastic since long. I was quick to reply:

You don't need to worry about this. SubSonic is intelligent enough to handle this! Just create new object and assign values to properties and save it.

I then created a table to test it and it worked like charm.




User commented back that he is not able to make it work. It then occurred to me that he might be using SubSonic 3. I tested with SS3 and he was right there was something funny. I got following error:



What is this? SubSonic failed to take into account that a TimeStamp datatyped column should not be explicitly set in Insert or Update statements.

I fired .Net Reflector and opened SubSonic.Core.dll. It took me to the following method in SubSonic.Extensions.Database class:

public static ISqlQuery ToInsertQuery(this T item, IDataProvider provider) where T: class, new()
{
    Type type = typeof(T);

    ITable tbl = provider.FindOrCreateTable();

    Insert query = null;

    if (tbl != null)
    {
        Dictionary hashed = item.ToDictionary();

        query = new Insert(provider).Into(tbl);

        foreach (string key in hashed.Keys)
        {
            IColumn col = tbl.GetColumn(key);

            if ((col != null) && !(col.AutoIncrement || col.IsReadOnly))
            {
                query.Value(col.QualifiedName, hashed[key], col.DataType);
            }
        }
    }
    return query;

As you can see in this line:

It was checking if column was null or auto-increment, and was omitting it from column list for query. By default TimeStamp typed column should be readonly. But even if it was readonly the line above is not checking for ReadOnly property. I also tried to set it to null but it did not work.

I went to GitHub and forked subsonic source code. I was sure putting another check in if statement above will work:

if ((column != null) && !column.AutoIncrement && !column.IsReadOnly)

But when I opened the code the above method read something like this:

public static ISqlQuery ToInsertQuery(this T item, IDataProvider provider) where T : class, new()
{
    Type type = typeof(T);
    ITable tbl = provider.FindOrCreateTable();
    Insert query = null;
    if(tbl != null)
    {
        var hashed = item.ToDictionary();

        query = new Insert(provider).Into(tbl);

        foreach(string key in hashed.Keys)
        {
            IColumn col = tbl.GetColumn(key);
            if(col != null)
            {
                if(!col.AutoIncrement && !col.IsReadOnly)
                    query.Value(col.QualifiedName, hashed[key], col.DataType);
            }
        }
    }
    return query;
}

Hmm, the problem is already sorted out. The questioner is also using dll from some old build. I compiled, added reference and re-ran the test code. No luck. It confronted me with same error. I put break point at save statement to delve deeper. The column was never set as ReadOnly :(

I scourged the generated code and saw what it generated for TestTimeStamp table. It read:

public class TestTimeStampTable: DatabaseTable {
    public TestTimeStampTable(IDataProvider provider):base("TestTimeStamp",provider){
    ClassName = "TestTimeStamp";
    SchemaName = "dbo";
    ………………
    ………………

    Columns.Add(new DatabaseColumn("RowVersion", this)
    {
        IsPrimaryKey = false,
        DataType = DbType.Binary,
        IsNullable = false,
        AutoIncrement = false,
        IsForeignKey = false
    });
}

So problem was somewhere here! When creating column, SS was not putting if the column is readonly or not. I checked structs.tt T4 template.

It has following code:

<# foreach(var col in tbl.Columns){#>
    Columns.Add(new DatabaseColumn("<#=col.Name#>", this) {
         IsPrimaryKey = <#=col.IsPK.ToString().ToLower()#>,
         DataType = DbType.<#=col.DbType.ToString()#>,
         IsNullable = <#=col.IsNullable.ToString().ToLower()#>,
         AutoIncrement = <#=col.AutoIncrement.ToString().ToLower()#>,
         IsForeignKey = <#=col.IsForeignKey.ToString().ToLower()#>
    });

<# }#>

It is not even bothering to check if column is readonly or not :( I put following line after IsForeignKey line:

IsReadOnly = <#=col.DataType.ToLower().Equals("timestamp").ToString().ToLower() #>

On saving the file the code was regenerated. TestTimeStamp class now read:

Columns.Add(new DatabaseColumn("RowVersion", this) {
    IsPrimaryKey = false,
    DataType = DbType.Binary,
    IsNullable = false,
    AutoIncrement = false,
    IsForeignKey = false,
    IsReadOnly = true
});

I tried my luck again. It ran without any error. I ran it again, opened SQL Server Management Studio and executed SQL Query:

Select * From TestTimeStamp

Now it returned:

RowID	RowDescription	RowVersion
--------	-------------------------	------------------------
1	Hello world!	0x00000000000007D1
2	Hello world, Again!	0x00000000000007D2
(2 row(s) affected)

Happy Coding!