Ormlite RowVersion Between SQL Server and SQLite

ServiceStack.OrmLite
1

Can we use data type long RowVersion in SQLite and SQL server.
The reason I ask we do some test localy on our PC/MAC, with SQLite but run live on SQLServer.

Now my Concurrency test fail localy but are OK on SQLServer
byte[] RowVersion is null from SQLite test

I try to avoid #ifdef in my POCO definition.
At run time I change my connection string #ifDef will need more compilation.

2

You can use ulong RowVersion to enable optimistic concurrency in different RDBMS’s. But as it relies on Triggers in Sqlite it requires that the tables containing the ulong RowVersion property was created with OrmLite, which also creates the necessary triggers when creating/dropping the table.

1 Like
3

OK I change my type to ulong and I see my timestamp DbType in my DB… wow you manage all that for us… Can you point me where I can see that in the code.

So you do a cast somewhere from timestamp to ulong ???

Again SS is the best. :smile:

1 Like
4

The ulong is just that an unsigned long, it’s not a timestamp it’s a unique version number. In SQL Server it uses its rowversion data type, in SQLite it’s just using a BIGINT.

You can see what SQL OrmLite generates by enabling a debug logger, e.g:

LogManager.LogFactory = new ConsoleLogFactory(debugEnabled:true);

public class User
{
    public long Id { get; set; }
    public string Name { get; set; }
    public ulong RowVersion { get; set; }
}

var dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
var db = dbFactory.Open();

db.DropAndCreateTable<User>();

Which will print to the Console:

DEBUG: SQL: CREATE TABLE "User" 
(
  "Id" INTEGER PRIMARY KEY, 
  "Name" VARCHAR(8000) NULL, 
  "RowVersion" BIGINT NOT NULL DEFAULT 1 
); 

DEBUG: SQL: CREATE TRIGGER UserRowVersionUpdateTrigger BEFORE UPDATE ON User FOR EACH ROW BEGIN UPDATE User SET "RowVersion" = OLD."RowVersion" + 1 WHERE "Id" = NEW."Id"; END;