Debaser

Have you ever had the need to insert/update many rows in SQL Server? (i.e. "upsert" them)

Did you try to use the MERGE INTO ... syntax then? (and did you enjoy it?)

Did you know that ADO.NET has an API that allows for STREAMING rows to a temporary table in tempdb making SQL Server call a stored procedure for each row very quickly?

Did you know that Debaser can do these things for you?

How to do it?

Create a class that looks the way you want it to look:

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; } }

(Debaser supports using a default constructor and properties with setters, or using a constructor with parameters matching the properties like this example shows)

and then you create the UpsertHelper for it:

var upsertHelper = new UpsertHelper<SomeDataRow>("db");

(where db in this case is the name of a connection string in the current app.config)

and then you do this once:

upsertHelper.CreateSchema();

(which will create a table, a table data type, and a stored procedure)

and then you insert 100k rows like this:

var rows = Enumerable.Range(1, 100000) .Select(i => new SomeDataRow(i, i*2.3m, $"This is row {i}")) .ToList(); var stopwatch = Stopwatch.StartNew(); await upsertHelper.Upsert(rows); var elapsedSeconds = stopwatch.Elapsed.TotalSeconds; Console.WriteLine($"Upserting {rows.Count} rows took {elapsedSeconds} - that's {rows.Count / elapsedSeconds:0.0} rows/s");