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.

DROP TABLE IF EXISTS dbo.PerfCounter

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
GO

CREATE PROCEDURE dbo.InsertPerfCounterData
    @json NVARCHAR(max)

AS
BEGIN

    INSERT INTO dbo.PerfCounter (
        RecordedDateTime
       ,RecordedDateTimeLocal
       ,CpuPctProcessorTime
       ,MemAvailGbytes
       )

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

END

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:

System.Data.SqlClient

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))
    {
        conn.Open();

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

            cmd.ExecuteReader();
        }
    }
}

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

SELECT 
    RecordedDateTime
   ,RecordedDateTimeLocal
   ,CpuPctProcessorTime
   ,MemAvailGbytes
FROM dbo.PerfCounter

Results:

Other Considerations

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

References