Sequences

The Sequence class in Weasel.SqlServer manages SQL Server sequences with support for delta detection and ownership.

Defining a Sequence

// Simple sequence starting at 1
var seq = new Sequence("dbo.order_seq");

// Sequence with a custom start value
var seq2 = new Sequence(
    DbObjectName.Parse(SqlServerProvider.Instance, "dbo.invoice_seq"),
    startWith: 1000
);

^{snippet source | anchor}

Sequence Ownership

A sequence can be associated with a table column:

seq.Owner = DbObjectName.Parse(SqlServerProvider.Instance, "dbo.orders");
seq.OwnerColumn = "id";

^{snippet source | anchor}

Using Sequences with Table Columns

Set a column's default value from a sequence:

var table = new Table("dbo.orders");
var seq = new Sequence("dbo.order_seq");

table.AddColumn<long>("id").AsPrimaryKey()
    .DefaultValueFromSequence(seq);

^{snippet source | anchor}

This generates: DEFAULT next value for dbo.order_seq.

Generating DDL

var migrator = new SqlServerMigrator();
var writer = new StringWriter();

seq.WriteCreateStatement(migrator, writer);
// Output: CREATE SEQUENCE dbo.order_seq START WITH 1;

seq.WriteDropStatement(migrator, writer);
// Output: DROP SEQUENCE IF EXISTS dbo.order_seq;

^{snippet source | anchor}

Delta Detection

Sequences are checked for existence in sys.sequences:

await using var conn = new SqlConnection(connectionString);
await conn.OpenAsync();

var delta = await seq.FindDeltaAsync(conn);
// delta.Difference: None or Create

^{snippet source | anchor}