Json File Generation

Question

I Need some help in generating JSON Files from a database table. Below is my sample json generated through tsql code (using SQL Server 2017)

{  "TBLEMPLOYEES":[  
  {  
     "ID":1,
     "NAME":"SURESH",
     "SALARY":1000,
     "ADDRESS":{  
        "EMP_ADDRESS":"101 A ADDRESS1",
        "OTHERS":{  
           "EMP_PHONE":"987-654-4567",
           "EMP_CITY":"CITY1"
        }
     }
  },
  {  
     "ID":2,
     "NAME":"RAMESH",
     "SALARY":2000,
     "ADDRESS":{  
        "EMP_ADDRESS":"125 A ADDRESS2",
        "OTHERS":{  
           "EMP_PHONE":"588-654-4567",
           "EMP_CITY":"CITY2"
        }
     }
  }

] }

Below is the TSQL code that i'm using to generate the JSON output in SSMS

SELECT ID AS "ID", EMP_NAME AS "NAME", EMP_SALARY AS "SALARY",EMP_ADDRESS  AS "ADDRESS.EMP_ADDRESS", EMP_PHONE AS "ADDRESS.OTHERS.EMP_PHONE", EMP_CITY AS "ADDRESS.OTHERS.EMP_CITY" FROM TBLEMPLOYEES FOR JSON path, root('TBLEMPLOYEES')

Now issue is when I try to use the same query from winform application using C#.net im not getting the correct Json File format. Its having no data in it.

Here is the code that i'm using

var queryWithForJson = "SELECT ID AS \"ID\", EMP_NAME AS \"NAME\",  EMP_SALARY AS \"SALARY\",EMP_ADDRESS AS \"ADDRESS.EMP_ADDRESS\", EMP_PHONE AS \"ADDRESS.OTHERS.EMP_PHONE\", EMP_CITY AS \"ADDRESS.OTHERS.EMP_CITY\" FROM TBLEMPLOYEES FOR JSON PATH, ROOT('TBLEMPLOYEES')";

        string userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name;

        var conn = new SqlConnection("Server=localhost\\SQLEXPRESS01;Database=Sample_2018;Trusted_Connection=True;");
        var cmd = new SqlCommand(queryWithForJson, conn);
        conn.Open();
        var jsonResult = new StringBuilder();
        var reader = cmd.ExecuteReader();
        if (!reader.HasRows)
        {
            jsonResult.Append("[]");
        }
        else
        {
            while (reader.Read())
            {
                jsonResult.Append(reader.GetValue(0).ToString());
            }
        }

        System.IO.StreamWriter file = new System.IO.StreamWriter(@"D:\Output\TestJson.json");
        file.WriteLine(jsonResult.ToString());

Please let me know correct procedure to output JSON data (with same format) from database table to a JSON file.

what is the value of reader.HasRows ? if it's true what is the value of jsonResult after reader.Read() — Mihir Dave
– Mihir Dave, Commented Nov 30, 2018 at 11:18

er-sho · Accepted Answer · 2018-11-30 11:30:42Z

2

Its not problem of your query because it returns same json when you directly execute on SSMS or by using c# code.

The main cause that you are not writing your data to json file.

So for that you have to use StreamWriter.Flush();. like

System.IO.StreamWriter file = new System.IO.StreamWriter(@"D:\Output\TestJson.json");
file.WriteLine(xml.ToString());
file.Flush();       //<== This method write your buffered stream data to file.
file.Close();       //<== This method closes current stream.

StreamWriter.Flush():

Clears all buffers for the current writer and causes any buffered data to be written to the underlying stream.

In simple words your buffered data in stream is written by Flush() method.

edited Nov 30, 2018 at 11:30

answered Nov 30, 2018 at 11:18

er-sho

9,8212 gold badges17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1941025 Over a year ago

Thanks! its worked, I got the correct output file in proper format. Small mistake I missed it..

er-sho Over a year ago

if my answer was helpful to you then mark the tick on left side of answer to make it green and vote up by cliking up arrow. :)

Collectives™ on Stack Overflow

Json File Generation

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related