This post will demonstrate how to load JSON into SQL Server using a stored procedure and C#. This technique is possible with the new JSON functions starting in SQL Server 2016, and is nice for the following reasons:

  • Simplifies application code. Just serialize the C# objects to JSON and send them to the database.
  • Executing a stored procedure with C# is easily done with a small amount of code.
  • JSON is widely used and familiar to developers.
  • Structural changes to the data could potentially be done entirely on the database side, meaning that you could make changes without having to touch the application code.

SQL Server Table

Let’s start with the SQL Server table, which will contain performance counter data.


CREATE TABLE dbo.PerfCounter(
    RecordedDateTime        datetime2(0) NOT NULL,
    RecordedDateTimeLocal   datetime2(0) NOT NULL,
    CpuPctProcessorTime     smallint     NOT NULL,
    MemAvailGbytes          smallint     NOT NULL

SQL Server Stored Procedure

Next we need to create a stored procedure that will accept JSON text as a parameter and insert it into the table. Two important points here:

  • JSON text must use the NVARCHAR(MAX) data type in SQL Server in order to support the JSON functions.
  • The OPENJSON function is used to convert the JSON text into a rowset, which is then inserted into the previously created table.
DROP PROCEDURE IF EXISTS dbo.InsertPerfCounterData

CREATE PROCEDURE dbo.InsertPerfCounterData
    @json NVARCHAR(max)


    INSERT INTO dbo.PerfCounter (

    FROM OPENJSON(@json)
    WITH (
        RecordedDateTime      DATETIME2(0) '$.dateTime'
       ,RecordedDateTimeLocal DATETIME2(0) '$.dateTimeLocal'
       ,CpuPctProcessorTime   SMALLINT     '$.cpuPctProcessorTime'
       ,MemAvailGbytes        SMALLINT     '$.memAvailGbytes'
    ) AS jsonValues


C# Code

Now that the table and stored procedure are available, let’s look at the code. I’ve been running it in LINQPad, which stores the namespace declarations separately from the script. You’ll need to add the following namespace:


The script is below. Be sure to update the connection string on line 3 for your SQL Server instance and database.

static void Main()
    string connString = @"Data Source=localhost\sql2016;Initial Catalog=dwDev;Integrated Security=SSPI";
    string sprocname = "InsertPerfCounterData";
    string paramName = "@json";
    // Sample JSON string 
    string paramValue = "{\"dateTime\":\"2018-03-19T15:15:40.222Z\",\"dateTimeLocal\":\"2018-03-19T11:15:40.222Z\",\"cpuPctProcessorTime\":\"0\",\"memAvailGbytes\":\"28\"}";

    using (SqlConnection conn = new SqlConnection(connString))

        using (SqlCommand cmd = new SqlCommand(sprocname, conn))
            // Set command object as a stored procedure
            cmd.CommandType = CommandType.StoredProcedure;

            // Add parameter that will be passed to stored procedure
            cmd.Parameters.Add(new SqlParameter(paramName, paramValue));


If everything is setup correctly, you should see a row of data in the SQL Server table after running the C# script.

FROM dbo.PerfCounter


Other Considerations

Performance can be improved by using Native Compilation of Tables and Stored Procedures.