en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
אפר15

Written by: ronen ariely
15/04/2016 12:45 RssIcon

background

At the beginning of the month I lectured at the SQLSaturday # 481 event about the new support of JSON in SQL Server 2016. During the demonstration I showed queries that returns JSON text. SQL Server returns the JSON text formatted as compact text / dense format (one line, no extra space), which is good for most cases that we want to store the text or pass it between machines for example. But, by using a compact format the structure of the JSON document is not readable. 

In NoSQL servers, which based on JSON, we usually have a built-in method to format the JSON document into a format that is easier for humans to read (for example in mongoDB it named "pretty"). This feature must be added to the SSMS or to the SQL Server, in my opinion. In the mean time, for the sake of the lecture, I created simple app that do the job. it is basically a "one line app" using the nuget package Json.NET (Newtonsoft.Json).

After I uploaded the lecture materials (code and presentation), I got requests to upload the application as well. Since I do not like the idea that people execute code without understanding it, and since this is very simple and short code, and most DBA does not develop dot.net application, therefore I decided to post the code, as a step-by-step tutorial for beginners.


Step 1: create new project using the console application template

Step 2: Add references to Json.NET nuget package

>> Open "Solution Explorer" -> Right click on "References" -> click on "Add Reference.." 

>> In the search box type "Json.NET", and chose the newer package version -> Click OK

Step 3: Replace the built in code with this code:

using System;
using Newtonsoft.Json.Linq;
using System.IO;
 
namespace ArielyPrettyJSON
{
    class Program
    {
        static String EnterNewJSON = "Enter JSON in one line, or empty string to end";
        static void Main(string[] args)
        {
            Console.Title = "Ronen Ariely Pretty JSON formatter";
            Pretty();
        }
        static void Pretty()
        {
            String t = "";
            Console.WriteLine(EnterNewJSON);
            t = ReadLongLine();
            while (t.Length > 1)
            {
                Console.Clear();
                try
                {
                    Console.WriteLine(JToken.Parse(t).ToString(Newtonsoft.Json.Formatting.Indented));
                }
                catch
                {
                    Console.WriteLine("Input Error");
                }
                Console.ReadKey();
                Console.Clear();
                Console.WriteLine(EnterNewJSON);
                t = ReadLongLine();
            }
 
        }
        private static string ReadLongLine()
        {
            byte[] inputBuffer = new byte[8192];
            Stream inputStream = Console.OpenStandardInput(inputBuffer.Length);
            Console.SetIn(new StreamReader(inputStream, Console.InputEncoding, false, inputBuffer.Length));
            return Console.ReadLine();
        }
    }
}

You can now execute the application and start working with it, but I recommend to keep on reading.

Step 4: Configure the Console display

If you are using console application and you do not want to type the JSON each time, then you need to enable "QuickEdit Mode". Moreover, If we are going to use this app in a lecture we will need to configure the Console font size and colors.

4.1 Right click the title of the console application, once you execute it, and chose properties

4.2 go to the "Options" tab and mark the checkbook "QuickEdit Mode"

4.3 Go to the "Font" tab -> Chose size 20 (I think that 20 best fit for lecture, not for local use)

And we are ready to go :-)


Tags: JSON , Pretty , SQL , C Sharp
Categories: SQL , C# , ASP.Net