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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
--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 |
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.
1 2 3 4 5 6 7 8 9 |
-- 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.
1 2 3 4 5 6 |
{ "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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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 |
Let’s test the procedure in SQL Server Management Studio to make sure that it’s working:
1 2 3 4 |
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:
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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:
1 2 3 4 5 6 |
{ "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..
Hi Steve, sorry about my slow reply. Did you find an answer to your problem? Are you checking the variable contents in a breakpoint? If that is the case the escape characters are expected. If you output the JSON string to the console or use it elsewhere it should not contain the escape characters.
I realize this is old, but in case anyone else wants to receive a JSON object rather than a string, this can be used if you’re returning a
HttpResponseMessage
object from your API:var response = req.CreateResponse(HttpStatusCode.OK);
response.Content = new StringContent(jsonData, Encoding.UTF8, "application/json");
return response;
That will take whatever escaped json string data is in the jsonData variable and return it in the response as a json object.
Great article! Thanks!