locked
Sql 2016 returning large JSON of array of objects to C# service RRS feed

  • Question

  • I have to read a large JSON of array of objects from DB. The result from SQL is split into multiple rows. How can I get the individual objects from array in my service code(C#).

    I want to get the individual objects as string, as I have to pass each object to another service, so I don't won't to duplicate serialize-deserialze.

    I tried with JSON.NET to read stream returned from SqlDatareader.GetTextReader(0, but it is able to read only the first row returned from sql, the json object that is split across first and second row. It's not able to read.

    var sqlReader = sqlCommand.ExecuteReader(CommandBehaviour.SequentialAccess);
    while (sqlReader.Read())
    {
      JsonTextReader jsonReader = new JsonTextReader(sqlReader.GetTextReader(0));
      jsonReader.SupportMultipleContent = true;
      while(jsonReader.Read())
       {
          if(jsonReader.TokenType == JsonToken.StartObject)
           {
               var obj = JObject.Load(jsonReader);
           }
       }
    }


    singhhome

    Saturday, February 11, 2017 3:09 AM

Answers

  • Hi singhhome,

    I have made a test with following code to query the “AdventureWorks2012” with following T-SQL code and it works fine on my machine. If I don't cast the result to Varchar(max), it would happen error in C# code.

    T-SQL Code

    select CAST
    (
    	(
    		select 
    			SalesOrderNumber as 'Order.Number',
    			OrderDate  as 'Order.Date'
    		from Sales.SalesOrderHeader
    		for json path
    	) 
    as varchar(max)) as result
    

    C# code

                int cnt = 0;
                using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=AdventureWorks2012;Integrated Security=True; "))
                {
                    conn.Open();
                    using (SqlCommand sqlCommand = new SqlCommand() {
                        Connection = conn,
                        CommandText = @"select CAST
                                        (
    	                                    (
    		                                    select 
    			                                    SalesOrderNumber as 'Order.Number',
    			                                    OrderDate  as 'Order.Date'
    		                                    from Sales.SalesOrderHeader
    		                                    for json path
    	                                    ) 
                                        as varchar(max)) as result"
                    })
                    {
                        var sqlReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
                        while (sqlReader.Read())
                        {
                            JsonTextReader jsonReader = new JsonTextReader(sqlReader.GetTextReader(0));
                            jsonReader.SupportMultipleContent = true;
                            while (jsonReader.Read())
                            {
                                if (jsonReader.TokenType == JsonToken.StartObject)
                                {
                                    try
                                    {
                                        var obj = JObject.Load(jsonReader);
                                        Console.WriteLine($"Number: {(string)obj["Order"]["Number"]}");
                                        Console.WriteLine($"Date: {(string)obj["Order"]["Date"]}");
                                        cnt++;
                                    }
                                    catch (Exception)
                                    {
                                    }
                                }
                            }
                        }
                    }
                }
                Console.WriteLine($"Total Conut: {cnt}");
    

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Albert_ Zhang Monday, February 20, 2017 10:31 AM
    • Marked as answer by singhhome Friday, March 17, 2017 12:01 AM
    Monday, February 13, 2017 11:46 AM

All replies

  • This is not a SQL Questions. It should be posted/moved to one of the .net forums.
    Saturday, February 11, 2017 12:54 PM
  • Hi,

    From 2016, SQL Server supports to convert JSON data to rows & columns format and vice versa.

    You can convert in the backend and consume each records in the application layer.


    sathya - My blog ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Saturday, February 11, 2017 5:46 PM
  • HI Sathya,

    when my JSON array has lot of objects, SQL splits the result into multiple rows. So it looks like

    [{"d1":d1, "d2":d2}, {"d1":

    d2}]

    so my second JSON object is split into 2 rows. Is there an option where SQl can return everything in single row/column, so I can stream it to my client?

    Thanks


    singhhome

    Saturday, February 11, 2017 8:09 PM
  • Hi singhhome,

    I have made a test with following code to query the “AdventureWorks2012” with following T-SQL code and it works fine on my machine. If I don't cast the result to Varchar(max), it would happen error in C# code.

    T-SQL Code

    select CAST
    (
    	(
    		select 
    			SalesOrderNumber as 'Order.Number',
    			OrderDate  as 'Order.Date'
    		from Sales.SalesOrderHeader
    		for json path
    	) 
    as varchar(max)) as result
    

    C# code

                int cnt = 0;
                using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=AdventureWorks2012;Integrated Security=True; "))
                {
                    conn.Open();
                    using (SqlCommand sqlCommand = new SqlCommand() {
                        Connection = conn,
                        CommandText = @"select CAST
                                        (
    	                                    (
    		                                    select 
    			                                    SalesOrderNumber as 'Order.Number',
    			                                    OrderDate  as 'Order.Date'
    		                                    from Sales.SalesOrderHeader
    		                                    for json path
    	                                    ) 
                                        as varchar(max)) as result"
                    })
                    {
                        var sqlReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
                        while (sqlReader.Read())
                        {
                            JsonTextReader jsonReader = new JsonTextReader(sqlReader.GetTextReader(0));
                            jsonReader.SupportMultipleContent = true;
                            while (jsonReader.Read())
                            {
                                if (jsonReader.TokenType == JsonToken.StartObject)
                                {
                                    try
                                    {
                                        var obj = JObject.Load(jsonReader);
                                        Console.WriteLine($"Number: {(string)obj["Order"]["Number"]}");
                                        Console.WriteLine($"Date: {(string)obj["Order"]["Date"]}");
                                        cnt++;
                                    }
                                    catch (Exception)
                                    {
                                    }
                                }
                            }
                        }
                    }
                }
                Console.WriteLine($"Total Conut: {cnt}");
    

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Albert_ Zhang Monday, February 20, 2017 10:31 AM
    • Marked as answer by singhhome Friday, March 17, 2017 12:01 AM
    Monday, February 13, 2017 11:46 AM