Table Valued Parameters

Table-Valued Parameters are a powerful way to bulk insert, update, delete, or query large amounts of data. Nevermore makes it possible to use table-valued parameters as part of your queries.

Normally, when you do a query with Nevermore, arguments you pass to the query are sent as parameter. For example, this query:

transaction.Query<Customer>()
  .Where("OrganizationId in @ids")
  .Parameter("ids", new[] { "Org-1", "Org-2" }).ToList();

Will translate into:

select * from Customer where OrganizationId in (@ids_1, @ids_2)

There's a limit, however, of 2100 parameters per query. And as you add more parameters, the query becomes less efficient.

Table-Valued Parameters solve this problem.

Inserts and Updates

Bulk inserts

To do a bulk insert, assuming this it the table schema:

create table dbo.SomeTable 
(
  [Id] nvarchar(50), 
  [Name] nvarchar(50), 
  [References] nvarchar(50)
)

You first need to declare a type (reference):

create type dbo.SomeTableInsertData 
    as table 
    (
      [Id] nvarchar(50), 
      [Name] nvarchar(50), 
      [References] nvarchar(50)
    )

Building the records to be inserted is a little tedious, but looks like this:

using var transaction = await store.BeginWriteTransactionAsync();

// We need to tell SQL what the schema looks like
var idMetaData = new SqlMetaData("Id", SqlDbType.NVarChar, 50);
var nameMetadata = new SqlMetaData("Name", SqlDbType.NVarChar, 50);
var referencesMetadata = new SqlMetaData("References", SqlDbType.NVarChar, 50);

// Now we build our records
var records = new List<SqlDataRecord>();
for (var i = 0; i < 100000; i++)
{
    var record = new SqlDataRecord(idMetaData, nameMetadata, referencesMetadata);
    record.SetString(0, "MyId-" + i);
    record.SetString(1, "Name for " + i);
    record.SetString(2, "Some-Other-Doc-" + i);
    records.Add(record);
}

var parameters = new CommandParameterValues();

// TableValuedParameter is a Nevermore type. The first argument is the name of the `type` the variable
// will be declared as.
parameters.AddTable("bulkInsertData", 
    new TableValuedParameter("dbo.SomeTableInsertData", records));

await transaction.ExecuteNonQueryAsync(
    @"insert into dbo.SomeTable ([Id], [Name], [References]) 
      select [Id], [Name], [References] from @bulkInsertData", parameters);
await transaction.CommitAsync();

On my computer, inserting 100,000 items this way takes a few hundred milliseconds.

Bulk updates

A similar technique as above could be used for bulk updates. Simply create a type that defines the shape of the data you need to pass, then pass the data as a table-valued parameter.

Under the hood

ADO.NET figures out how to encode the values safely (to avoid SQL injection) and puts them as a big old insert, without passing them as parameters. This is what gets sent:

declare @p3 dbo.SomeTableInsertData
insert into @p3 values(N'MyId-0',N'Name for 0',N'Some-Other-Doc-0')
insert into @p3 values(N'MyId-1',N'Name for 1',N'Some-Other-Doc-1')
insert into @p3 values(N'MyId-2',N'Name for 2',N'Some-Other-Doc-2')
insert into @p3 values(N'MyId-3',N'Name for 3',N'Some-Other-Doc-3')
insert into @p3 values(N'MyId-4',N'Name for 4',N'Some-Other-Doc-4')
insert into @p3 values(N'MyId-5',N'Name for 5',N'Some-Other-Doc-5')
insert into @p3 values(N'MyId-6',N'Name for 6',N'Some-Other-Doc-6')
insert into @p3 values(N'MyId-7',N'Name for 7',N'Some-Other-Doc-7')
insert into @p3 values(N'MyId-8',N'Name for 8',N'Some-Other-Doc-8')
insert into @p3 values(N'MyId-9',N'Name for 9',N'Some-Other-Doc-9')

exec sp_executesql N'insert into dbo.SomeTable ([Id], [Name], [References]) select [Id], [Name], [References] from @bulkInsertData',N'@bulkInsertData [dbo].[SomeTableInsertData] READONLY',@bulkInsertData=@p3

If one of your strings was Name for ' -- drop table, for example, it would do:

insert into @p3 values(N'MyId-9',N'Name for '' -- drop table foo 9',N'Some-Other-Doc-9')

This encoding happens within ADO.NET, not by Nevermore, so we assume it's safe.

So, there's no magic here. You still have to transfer a lot of data potentially. If the records you are inserting have large JSON blobs, you might even consider compressing them with GZIP, and then calling DECOMPRESS in SQL server to reduce bandwidth.

However, while there's no magic, check out the benchmarks - this compares the TVP approach to just inserting in a loop for different numbers of records. At 100 records it's 10x faster, and the difference keeps growing.

Queries and Deletes

Queries

Single Identifiers

If you just have a list of ID's or other strings that you want to match, AddTable lets you pass it directly. There will then be a temporary table variable, with a single column called ParameterValue that you can join against.

List<string> emails = GetHugeListOfEmailAddresses();

var args = new CommandParameterValues();
args.AddTable("emailList ", emails);

transaction.Stream<Person>(@"
    select p.* from Person p 
       inner join @emailList t 
       on t.[ParameterValue] = p.Email",
    args);

Multiple Identifiers

If you have multiple identifiers you want to match, you need to define a type (reference), similar to inserts:

create type dbo.ThingQueryParameter
    as table 
    (
      [Id] nvarchar(50), 
      [Name] nvarchar(50), 
      [References] nvarchar(50)
    )

You can then build up a set of CommandParameterValues that match this type:

var args = new CommandParameterValues();
var parameterValues = things.Select(package =>
{
    var record = new SqlDataRecord(new SqlMetaData("Id", SqlDbType.NVarChar, 50), new SqlMetaData("Name", SqlDbType.NVarChar, 50));
    record.SetString(0, thing.Id);
    record.SetString(1, thing.Name);
    return record;
}).ToList();

And provide this set of CommandParameterValues to a TableValuedParameter via the AddTable method.

args.AddTable("thingReference", new TableValuedParameter("dbo.ThingQueryParameter", parameterValues));

Finally, Nevermore's Stream querying capability will accept the CommandParameterValues, which can then be referenced in the SQL for the query:

var result = store.GetQueryExecutor().Stream<Things>(@"
        select t.* from Things t
           inner join @thingReference r
           on r.Id = t.Id AND r.Name = t.Name",
    args).ToArray();

Deletes

You could do something similar for deletes:

transaction.Stream<Person>(@"
    delete from Person where Id in (select ParameterValue from @emailList)",
    args);