Using JSON to Move Beyond TVPs for Multi-Row Updates

Many .NET applications will use Table-Valued-Parameters (TVPs) to send in a set of rows to be updated to a single stored procedure. Using this approach requires a decent amount of legwork to achieve:

• Define a TYPE in SQL Server to represent the table valued parameter
• Assign permissions to the TYPE so that it can be used within the proc
• Write the proc to include the TVP and use it appropriately
• Format an ADO.NET DataTable class in C# code (and make sure to get the type conversions right)

This is a LOT of overhead (IMHO) to achieve a set of rows being affected in a single sproc call.

Fortunately, there are alternatives that can make this easier to achieve with fewer artifacts (namely no TYPE and no special permissions are required): XML and JSON. This post will focus on the JSON technique, which is available with SQL Server 2016 and later.

Consider a table with the following columns:

• AccountFieldId: unique key for the table
• AccountId: foreign key to the account table
• FieldId: foreign key to a field table
• Value: value for a given field and a given account

We would like to update a batch of rows for a single account, and multiple new values for fields.

Desired parameters for the proc:

• AccountId
• JSON for the FieldIds and Values

The JSON should probably look something like this:

1[
2  { "FieldId": 123,
3    "FieldValue": "set-to-this-value"
4  },
5  { "FieldId": 456,
6    "FieldValue": "here is another value"
7  }
8]

Sproc Syntax

The basic strategy with sprocs doing a batch of updates is getting the JSON into a temp table (or table variable) so that your update statement just joins to the temp table you’ve created from the JSON. Here is the syntax that can accept the parameters mentioned above and load the JSON into a table variable, and then merge the results into the actual AccountField table:

 1CREATE PROCEDURE [dbo].[AddUpdateAccountFieldFromJson] (
 2     @AccountId UNIQUEIDENTIFIER   
 3    ,@JsonInput NVARCHAR(MAX)
 4)   
 5 AS 
 6 BEGIN
 7    SET NOCOUNT ON; 
 8
 9    DECLARE @Source TABLE (	
10	[AccountID] [uniqueidentifier] NOT NULL,
11	[FieldID] [bigint] NOT NULL,
12	[Value] [nvarchar](max) NULL
13    )
14
15    INSERT INTO @Source ([AccountID],[FieldID],[Value])
16    SELECT @AccountId,[FieldId],[Value]
17    FROM OPENJSON(@JsonInput)
18    WITH (		
19	    [FieldId] BIGINT
20	   ,[FieldValue] NVARCHAR(MAX)                 
21    )
22
23MERGE AccountField AS target  
24    USING (SELECT [AccountID],[FieldID],[Value]
25           FROM @Source) AS source
26    ON ( target.[AccountId] = source.[AccountId] 
27         AND target.[FieldId] = source.[FieldId] )  
28    WHEN MATCHED AND (source.[Value] <> target.[Value])THEN   
29        UPDATE SET Value = source.Value
30    WHEN NOT MATCHED THEN  
31        INSERT ( [AccountId], [FieldId], [Value] )  
32        VALUES ( @AccountId, source.FieldId, source.Value ); 
33END

C# Code Syntax

The final step in getting batch updates to work is having some code that can CALL the sproc above.

You may be able to do this with an anonymous class, but here is a class definition that can surface the JSON we’re after:

1public class FieldData
2{
3    public long FieldId { get; set; }
4    public string FieldValue { get; set; }
5}

Given that class, here is some code that uses Newtonsoft.JSON and Dapper to call the proc above with the inputs that we want:

1var accountId = "1234-4567-8901";
2var listOfFieldValues = new List<FieldData> { 
3    new FieldData { FieldId = 123, FieldValue = "set-to-this-value" },
4    new FieldData { FieldId = 456, FieldValue = "here is another value" }
5};
6
7var jsonInput = JsonConvert.SerializeObject(listOfFieldValues);
8db.Execute("AddUpdateAccountFieldFromJson", new { accountId, jsonInput }, commandType: CommandType.StoredProcedure); 

Voila! :)