Home

Welcome to the Debaser wiki!

What is Debaser?

It's an "upsert helper" that works with Microsoft SQL Server, providing a pretty fast way to upsert many rows of data.

What's an "upsert"?

"Upsert" is just a combination of the words "Update" and "Insert". It denotes the scenario where an existing row is updated if it already exists in the database, and otherwise it is inserted.

This can be accomplished with the MERGE SQL statement, but it can be tedious to write that statement whenever you need it.

Show me the code

First, you create a class with properties of simple types – e.g. something like this:

class SomeDataRow
{
    public SomeDataRow(int id, decimal number, string text)
    {
        Id = id;
        Number = number;
        Text = text;
    }

    public int Id { get; }
    public decimal Number { get; }
    public string Text { get; }
}

which would correspond to a table of [Id] INT, [Number] DECIMAL, [Text] NVARCHAR in the database.

Then you go

var helper = new UpsertHelper<SomeDataRow>(connectionString);

possibly followed by

helper.CreateSchema();

which will create an appropriate table with the types shown above. In addition to the table, a custom table type will also be created, along with a stored procedure that will be invoked in order to carry out an upsert.

When it is time to use the helper, you call it like this:

await helper.Upsert(rows);

where rows is an IEnumerable<SomeDataRow>.

This is where the fun happens! 😁 because what happens now is this:

1. The rows sequence is asynchronously streamed to a temporary table in tempdb
2. The stored procedure that carries out the upsert operation is called for each row
3. The target table gets appropriate inserts/updates executed

and this happens very fast – I can upsert more than 200k rows/s on my machine.