SQL to JSON

Question

Hi

I have a stored procedure that returns data like:

date1 number id Description date2 rowVersion date3 id2 Code Type date4 Owned RowVersion2
09/11/2018 17:57 123456 5566 Full description 09/11/2018 17:57 0x000000000T49G19B 27/12/2018 14:21 1001 101 1 24/01/2020 16:17 Yes 0x000000000E1TST89
09/11/2018 17:57 123456 5566 Full description 09/11/2018 17:57 0x000000000T49G19B 27/12/2018 14:21 1004 202 2 24/01/2020 16:17 Yes 0x000000000E1TST89
09/11/2018 17:57 123456 5566 Full description 09/11/2018 17:57 0x000000000T49G19B 27/12/2018 14:21 1010 303 3 24/01/2020 16:17 Yes 0x000000000E1TST89

I have this data currenlty stored in a dataset and i'm trying to transform this into json like:

{
    "section1": {
        "date1": "2018-11-09",
        "id": "5566",
        "Description": "Full description",
        "date2": "2018-11-09T17:57:42",
        "rowVersion": "0x000000000T49G19B"
    },
    "section2": {
        "date3": "2018-11-09",
        "codes": [
            {
                "id2": "1001",
                "Code": "101",
                "Type": "1"
            },
            {
                "id2": "1004",
                "Code": "202",
                "Type": "2"
            },
            {
                "id2": "1010",
                "Code": "303",
                "Type": "3"
            }
        ]
    },
    "section3": {
        "date4": "2020-01-24",
        "owned": "true",
        "rowVersio2": "0x000000000E1TST89"
    }
}

I have tried using JsonConvert with classes/models but so far I have been unable to output the required json.

Can anyone help produce the json based on the sql?

Many thanks
Paul

Answer 1

Please try if this code can work for you, first group the same data in different rows, then construct a class that conforms to the json structure according to the grouping result, and finally install a nuget package: System.Text.Json and serialize it.

    class Program  
    {  
        static void Main(string[] args)  
        {  
            DataTable dataTable = new DataTable();  
            dataTable.Columns.Add("date1", typeof(DateTime));  
            dataTable.Columns.Add("number", typeof(string));  
            dataTable.Columns.Add("id", typeof(int));  
            dataTable.Columns.Add("Description", typeof(string));  
            dataTable.Columns.Add("date2", typeof(DateTime));  
            dataTable.Columns.Add("rowVersion", typeof(string));  
            dataTable.Columns.Add("date3", typeof(DateTime));  
            dataTable.Columns.Add("id2", typeof(int));  
            dataTable.Columns.Add("Code", typeof(int));  
            dataTable.Columns.Add("type", typeof(int));  
            dataTable.Columns.Add("date4", typeof(DateTime));  
            dataTable.Columns.Add("Owned", typeof(string));  
            dataTable.Columns.Add("RowVersion2", typeof(string));  
            CultureInfo provider = CultureInfo.InvariantCulture;  
            var format = "dd/MM/yyyy H:mm";  
            var date1 = DateTime.ParseExact("09/11/2018 17:57", format, provider);  
            var date2 = date1;  
            var date3 = DateTime.ParseExact("27/12/2018 14:21", format, provider);  
            var date4 = DateTime.ParseExact("24/01/2020 16:17", format, provider);  
            dataTable.Rows.Add(date1, "123456", 5566, "Full description", date2, "0x000000000T49G19B", date3, 1001, 101, 1, date4, "Yes", "0x000000000E1TST89");  
            dataTable.Rows.Add(date1, "123456", 5566, "Full description", date2, "0x000000000T49G19B", date3, 1004, 102, 2, date4, "Yes", "0x000000000E1TST89");  
            dataTable.Rows.Add(date1, "123456", 5566, "Full description", date2, "0x000000000T49G19B", date3, 1010, 103, 3, date4, "Yes", "0x000000000E1TST89");  
  
            List<MyClass> myClasses = new List<MyClass>();  
             
  
            var re = (from row in dataTable.AsEnumerable()  
                      group row by new  
                      {  
                          date1 = row["date1"],  
                          number = row["number"],  
                          id = row["id"],  
                          Description = row["Description"],  
                          date2 = row["date2"],  
                          rowVersion = row["rowVersion"],  
                          date4 = row["date4"],  
                          date3 = row["date3"],  
                          Owned = row["Owned"],  
                          RowVersion2 = row["RowVersion2"],  
                      } into gcs  
                      select new  
                      {  
                          Key = gcs.Key,  
                          id2 = gcs.Select(r => r.Field<int>("id2")).ToArray(),  
                          Code = gcs.Select(r => r.Field<int>("Code")).ToArray(),  
                          type = gcs.Select(r => r.Field<int>("type")).ToArray()  
                      }).ToList();  
  
            foreach (var item in re)  
            {  
                MyClass myClass = new MyClass();  
                myClass.section1.date1 = (DateTime)item.Key.date1;  
                myClass.section1.number = item.Key.number.ToString();  
                myClass.section1.id = (int)item.Key.id;  
                myClass.section1.Description = item.Key.Description.ToString();  
                myClass.section1.date2 = (DateTime)item.Key.date2;  
                myClass.section1.rowVersion = item.Key.rowVersion.ToString();  
                myClass.section2.date3 = (DateTime)item.Key.date3;  
  
                myClass.section3.date4 = (DateTime)item.Key.date4;  
                myClass.section3.owned = item.Key.Owned.ToString()== "Yes" ? true:false;  
                myClass.section3.rowVersion2 = item.Key.RowVersion2.ToString();  
  
                for (int i = 0; i < item.id2.Count(); i++)  
                {  
                    CodeType codeType = new CodeType();  
                    codeType.id2 = re[0].id2[i];  
                    codeType.Code = re[0].Code[i];  
                    codeType.Type = re[0].type[i];  
                    myClass.section2.codes.Add(codeType);  
                }  
                myClasses.Add(myClass);  
            }  
  
            string jsonString = JsonSerializer.Serialize(myClasses);  
  
            Console.WriteLine("Press any key to continue...");  
            Console.ReadLine();  
        }  
    }  
  
    class MyClass  
    {  
        public section1 section1 { get; set; }  
        public section2 section2 { get; set; }  
        public section3 section3 { get; set; }  
        public MyClass()   
        {  
            section1 = new section1();  
            section2 = new section2();  
            section3 = new section3();  
        }  
    }  
    class section1  
    {  
        public DateTime date1 { get; set; }  
        public string number { get; set; }  
        public int id { get; set; }  
        public string Description { get; set; }  
        public DateTime date2 { get; set; }  
        public string rowVersion { get; set; }  
    }  
    class section2  
    {  
        public DateTime date3 { get; set; }  
        public List<CodeType> codes { get; set; }  
        public section2()   
        {  
            codes = new List<CodeType>();  
        }  
    }  
    class section3  
    {  
        public DateTime date4 { get; set; }  
        public bool owned { get; set; }  
        public string rowVersion2 { get; set; }  
    }  
    class CodeType  
    {  
        public int id2 { get; set; }  
        public int Code { get; set; }  
        public int Type { get; set; }  
    }  

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.