Retrieve JSON Data from SQL Server using a Stored Procedure and C#

The previous post demonstrated how to load JSON into SQL Server using a stored procedure and C#. This post will examine the opposite – how to retrieve JSON data from SQL Server using a stored procedure and C#. Just like with the JSON loading technique, the new JSON functions in SQL Server 2016 allow for this retrieval technique.

Here’s how it works in short:

Data stored in a database table will be converted to JSON text in the stored procedure and returned to the C# client via an output parameter.

SQL Server Table

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

--Create table
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
)

--Insert sample row
INSERT INTO [dbo].[PerfCounter]
           ([RecordedDateTime]
           ,[RecordedDateTimeLocal]
           ,[CpuPctProcessorTime]
           ,[MemAvailGbytes])
     VALUES
           ('2018-03-19 15:15:40'
           ,'2018-03-19 11:15:40'
           ,12
           ,28)
GO

--Create table

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

)

--Insert sample row

INSERT INTO [dbo].[PerfCounter]

([RecordedDateTime]

,[RecordedDateTimeLocal]

,[CpuPctProcessorTime]

,[MemAvailGbytes])

VALUES

('2018-03-19 15:15:40'

,'2018-03-19 11:15:40'

,12

,28)

Before looking at the stored procedure, let’s test the query that will be used in the procedure to make sure it’s returning the results in the desired format. For this example, we’ll pretend that there are multiple rows in the table, and we’ll return only the most recent row.

  -- Retreive most recent row
  SELECT TOP(1)
       [RecordedDateTime] AS 'dateTime'
      ,[RecordedDateTimeLocal] AS 'dateTimeLocal'
      ,[CpuPctProcessorTime]
      ,[MemAvailGbytes]
  FROM [dbo].[PerfCounter]
  ORDER BY RecordedDateTimeLocal DESC
  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

-- Retreive most recent row

SELECT TOP(1)

[RecordedDateTime] AS 'dateTime'

,[RecordedDateTimeLocal] AS 'dateTimeLocal'

,[CpuPctProcessorTime]

,[MemAvailGbytes]

FROM [dbo].[PerfCounter]

ORDER BY RecordedDateTimeLocal DESC

FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

The FOR JSON clause will convert the query results to JSON text, which should look like what is shown below when formatted nicely. Notice that the aliases in the SQL query are used in the outputted JSON. There are other formatting options available with FOR JSON. This is a simple example.

{
  "dateTime": "2018-03-19T15:15:40",
  "dateTimeLocal": "2018-03-19T11:15:40",
  "CpuPctProcessorTime": 12,
  "MemAvailGbytes": 28
}

{

"dateTime": "2018-03-19T15:15:40",

"dateTimeLocal": "2018-03-19T11:15:40",

"CpuPctProcessorTime": 12,

"MemAvailGbytes": 28

}

Now that the query is working correctly, let’s create the stored procedure. The same query tested above is used in the procedure, and the results are assigned to an output parameter.

DROP PROCEDURE IF EXISTS dbo.RetrievePerfCounterData
GO

CREATE PROCEDURE dbo.RetrievePerfCounterData @jsonOutput NVARCHAR(MAX) OUTPUT

AS

BEGIN

SET @jsonOutput = (SELECT TOP (1)
        [RecordedDateTime] AS 'dateTime'
       ,[RecordedDateTimeLocal] AS 'dateTimeLocal'
       ,[CpuPctProcessorTime]
       ,[MemAvailGbytes]
    FROM [dbo].[PerfCounter]
    ORDER BY RecordedDateTimeLocal DESC
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)

END

DROP PROCEDURE IF EXISTS dbo.RetrievePerfCounterData

CREATE PROCEDURE dbo.RetrievePerfCounterData @jsonOutput NVARCHAR(MAX) OUTPUT

BEGIN

SET @jsonOutput = (SELECT TOP (1)

[RecordedDateTime] AS 'dateTime'

,[RecordedDateTimeLocal] AS 'dateTimeLocal'

,[CpuPctProcessorTime]

,[MemAvailGbytes]

FROM [dbo].[PerfCounter]

ORDER BY RecordedDateTimeLocal DESC

FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)

END

Let’s test the procedure in SQL Server Management Studio to make sure that it’s working:

DECLARE @json AS NVARCHAR(MAX)
EXEC dbo.RetrievePerfCounterData @jsonOutput = @json OUTPUT

SELECT @json

DECLARE @json AS NVARCHAR(MAX)

EXEC dbo.RetrievePerfCounterData @jsonOutput = @json OUTPUT

SELECT @json

The JSON text should be returned to the results grid:

C# Code

With the table and stored procedure in place, let’s look at the code. As per usual, I use LINQPad for these examples. The following namespace is required:

System.Data.SqlClient

1	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 = "RetrievePerfCounterData";
    string jsonOutputParam = "@jsonOutput";

    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();

        using (SqlCommand cmd = new SqlCommand(sprocname, conn))
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            // Create output parameter. "-1" is used for nvarchar(max)
            cmd.Parameters.Add(jsonOutputParam, SqlDbType.NVarChar, -1).Direction = ParameterDirection.Output;

            // Execute the command
            cmd.ExecuteNonQuery();

            // Get the values
            string json = cmd.Parameters[jsonOutputParam].Value.ToString();

            Console.WriteLine(json);
        }
    }
}

static void Main()

{

string connString = @"Data Source=localhost\sql2016;Initial Catalog=dwDev;Integrated Security=SSPI";

string sprocname = "RetrievePerfCounterData";

string jsonOutputParam = "@jsonOutput";

using (SqlConnection conn = new SqlConnection(connString))

{

conn.Open();

using (SqlCommand cmd = new SqlCommand(sprocname, conn))

{

cmd.CommandType = System.Data.CommandType.StoredProcedure;

// Create output parameter. "-1" is used for nvarchar(max)

cmd.Parameters.Add(jsonOutputParam, SqlDbType.NVarChar, -1).Direction = ParameterDirection.Output;

// Execute the command

cmd.ExecuteNonQuery();

// Get the values

string json = cmd.Parameters[jsonOutputParam].Value.ToString();

Console.WriteLine(json);

}

When you run the C# script, the JSON string should returned from the stored procedure and written to the console. When nicely formatted it should look like this:

{
  "dateTime": "2018-03-19T15:15:40",
  "dateTimeLocal": "2018-03-19T11:15:40",
  "CpuPctProcessorTime": 12,
  "MemAvailGbytes": 28
}

{

"dateTime": "2018-03-19T15:15:40",

"dateTimeLocal": "2018-03-19T11:15:40",

"CpuPctProcessorTime": 12,

"MemAvailGbytes": 28

}

With the JSON data now in the application, it can be transmitted to another location or deserialized for use within the application.

Other Considerations

NVARCHAR(MAX) is required by the SQL Server functions that parse JSON text. Since the JSON text is simply being output in this example, you can use other string types besides NVARCHAR(MAX) with this technique.

References

Format Query Results as JSON with FOR JSON (SQL Server)
Get Return and Out parameter values using Ado.Net

Thanks for this tuto.

I have a problem :

When I run my stored procedure in TSQL, it is fine :

[{“pseudo”:”John”,”age”:85,”city”:”LYON”,”country”:”FR”},{“pseudo”:”Stève”,”age”:30,”city”:”NANTES”,”country”:”FR”}]

But in c#, the result have escaped chars :

“[{\”pseudo\”:\”John\”,\”age\”:85,\”city\”:\”LYON\”,\”country\”:\”FR\”},{\”pseudo\”:\”Steve\”,\”age\”:30,\”city\”:\”NANTES\”,\”country\”:\”FR\”}]”

My code is basically the same as you..

    [Route("api/p_GetAllUser")]
    [HttpGet]
    public string p_GetAllUser()
    {

        string result = "";

        using (
        var conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString))
        using (var command = new SqlCommand("p_GetAllUser", conn)
        {
            CommandType = CommandType.StoredProcedure
        })

        {
            var returnParameter = command.Parameters.Add("@result", SqlDbType.NVarChar, -1);
            returnParameter.Direction = ParameterDirection.Output;

            conn.Open();
            command.ExecuteNonQuery();

            result = command.Parameters["@result"].Value.ToString();

        }

        return result;
    }

[Route("api/p_GetAllUser")]

[HttpGet]

public string p_GetAllUser()

{

string result = "";

using (

var conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString))

using (var command = new SqlCommand("p_GetAllUser", conn)

{

CommandType = CommandType.StoredProcedure

})

{

var returnParameter = command.Parameters.Add("@result", SqlDbType.NVarChar, -1);

returnParameter.Direction = ParameterDirection.Output;

conn.Open();

command.ExecuteNonQuery();

result = command.Parameters["@result"].Value.ToString();

}

return result;

}

Modern data platform and engineering in Azure

Retrieve JSON Data from SQL Server using a Stored Procedure and C#

SQL Server Table

C# Code

Other Considerations

References

4 Comments

Leave a Reply (Markdown formatting available) Cancel reply