OrmLite Database Independent sequence number generation?

ServiceStack.OrmLite
1

I’m new to OrmLite and I am hopeful that there is some DB Independent magic to solve the age old sequence number vs auto-increment problem.

In a nutshell, we don’t want to use auto-increment fields, for some important fields due to problems that can occur during replication and backup/restore.

But since we’re not sure where were going to land, (MYSQL or SQLServer) and each database sort of has DB specific ways to solve this, i was hopeful that a library or technique can be shared.

I wish i had the cash to buy oracle db’s where the SEQUENCE operator existed.

2

OrmLite has RDBMS-agnostic support for [AutoIncrement] where it will use the appropriate RDBMS construct for each provider, it doesn’t have support for an alternative version of Auto Increment based on custom sequences. You could potentially build your own support by using OrmLite’s Pre / Post Custom SQL Hooks to create a custom trigger that does this, we use a similar feature in to implement RowVersion support for MySql.

In MySql you can insert a row with an Id which overrides its auto_increment feature, you can temporarily tell OrmLite to treat the Primary Key as a normal Id by disabling it during your import/replication with:

var modelDef = typeof(T).GetModelMetadata();
modelDef.PrimaryKey.AutoIncrement = false;

//... Id's are included in Inserts

modelDef.PrimaryKey.AutoIncrement = true;

In SQL Server you’ll also need to disable the Auto Increment feature on RDBMS with Custom SQL before and after the import:

db.ExecuteSql($$"SET IDENTITY_INSERT {db.GetDialectProvider().GetQuotedTableName(modelDef)} OFF");

//... Import data

db.ExecuteSql($$"SET IDENTITY_INSERT {db.GetDialectProvider().GetQuotedTableName(modelDef)} ON");