Share via

Building JSON with C# Classes

Kmcnet 706 Reputation points
2023-04-14T20:42:47.0066667+00:00

Hello everyone and thanks for the help in advance. I need to develop an application that queries a SQL database to create a JSON file for submission to a vendor. I have very little experience doing this and only have a sample JSON file to start with;


{
  "Message": {
   "@DatatypesVersion": "20170715",
   "@TransportVersion": "20170715",
   "@TransactionDomain": "SCRIPT",
   "@TransactionVersion": "20170715",
   "@StructuresVersion": "20170715",
   "@ECLVersion": "20170715",
   "Header": {
      "To": {
         "@Qualifier": "P",
         "#text": "7654321"
      },
      "From": {
         "@Qualifier": "C",
         "#text": "Someone"
      },
      "MessageID": "MustBeUnique",
      "SentTime": "1991-02-11T04:56:15.45",
      "Security": {
         "UsernameToken": {
            "Username": "3username",
		"Password": {
			"@Type": "PasswordDigest",
			"#text": "password"
			},
            "Nonce": "string",
            "Created": "1973-05-27T05:03:09.65"
         },
         "Sender": {
            "SecondaryIdentification": "developer@somewhere.com",
            "TertiaryIdentification": "1234567"
         },
         "Receiver": {
            "SecondaryIdentification": "WENO_EPCS_ONLINE",
            "TertiaryIdentification": "WENO_EPCS_ONLINE"
         }
      },
      "SenderSoftware": {
         "SenderSoftwareDeveloper": "Developer",
         "SenderSoftwareProduct": "Product",
         "SenderSoftwareVersionRelease": "V1"
      },
      "DigitalSignature": {
         "@Version": "T",
         "DigitalSignatureIndicator": "0"
      }
   },
   "Body": {
      "NewRx": {
         "Patient": {
            "HumanPatient": {
               "Name": {
                  "LastName": "Doe",
                  "FirstName": "Jane"
               },
               "Gender": "F",
               "DateOfBirth": {
                  "Date": "1955-06-17"
               },
               "Address": {
                  "AddressLine1": "1234 Main",
                  "City": "MyTown",
                  "StateProvince": "FL",
                  "PostalCode": "12345",
                  "CountryCode": "A"
               }
            }
         },
         "Pharmacy": {
            "Identification": {
               "NCPDPID": "123456",
               "NPI": "1234567890"
            },
            "Specialty": "Retail",
            "BusinessName": "Test Stand Alone Pharmacy",
            "Address": {
               "AddressLine1": "1 Main",
               "AddressLine2": "",
               "City": "",
               "StateProvince": "",
               "PostalCode": "",
               "CountryCode": "A"
            },
            "CommunicationNumbers": {
               "PrimaryTelephone": {
                  "Number": "9999999999"
               },
               "Fax": {
                  "Number": "9999999999"
               }
            }
         },
         "Prescriber": {
            "NonVeterinarian": {
               "Identification": {
                  "NPI": "1234567890"
               },
               "Name": {
                  "LastName": "",
                  "FirstName": ""
               },
               "Address": {
                  "AddressLine1": "",
                  "City": "",
                  "StateProvince": "",
                  "PostalCode": "",
                  "CountryCode": "A"
               },
               "CommunicationNumbers": {
                  "PrimaryTelephone": {
                     "Number": "2228889999"
                  }
               }
            }
         },
         "MedicationPrescribed": {
            "DrugDescription": "hydrogen peroxide 3 % Topical Solution",
            "DrugCoded": {
               "DrugDBCode": {
                  "Code": "91349",
                  "Qualifier": "SCD"
               },
               "DEASchedule": {
                  "Code": "C38046"
               }
            },
            "Quantity": {
               "Value": "70",
               "CodeListQualifier": "38",
               "QuantityUnitOfMeasure": {
                  "Code": "C28254"
               }
            },
            "WrittenDate": {
               "Date": "2019-05-30"
            },
            "Substitutions": "0",
            "NumberOfRefills": "0",
            "Sig": {
               "SigText": "Take two tablets before meals and one tablet at bedtime"
            }
         }
      }
   }
}
}

I assume I need to create a class to map the objects, but I really don't understand how to do this.  I did try some JSON tools to class, but didn't work.
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,648 questions
Accepted answer
  1. Bruce (SqlWork.com) 61,731 Reputation points
    2023-04-16T21:31:42.45+00:00

    you first step is to create class objects to represent the JSON. then you need to write queries and create the objects. the sample JSON is very hierarchal and may require server queries.

    there are a number of tools to convert the JSON to c# classes. here is my result (you might want to rename the class Root to something more intuitive: 

        // Root myDeserializedClass = JsonConvert.DeserializeObject<Root>(myJsonResponse);
    public class Address
    {
        public string AddressLine1 { get; set; }
        public string City { get; set; }
        public string StateProvince { get; set; }
        public string PostalCode { get; set; }
        public string CountryCode { get; set; }
        public string AddressLine2 { get; set; }
    }

    public class Body
    {
        public NewRx NewRx { get; set; }
    }

    public class CommunicationNumbers
    {
        public PrimaryTelephone PrimaryTelephone { get; set; }
        public Fax Fax { get; set; }
    }

    public class DateOfBirth
    {
        public string Date { get; set; }
    }

    public class DEASchedule
    {
        public string Code { get; set; }
    }

    public class DigitalSignature
    {
        [JsonProperty("@Version")]
        public string Version { get; set; }
        public string DigitalSignatureIndicator { get; set; }
    }

    public class DrugCoded
    {
        public DrugDBCode DrugDBCode { get; set; }
        public DEASchedule DEASchedule { get; set; }
    }

    public class DrugDBCode
    {
        public string Code { get; set; }
        public string Qualifier { get; set; }
    }

    public class Fax
    {
        public string Number { get; set; }
    }

    public class From
    {
        [JsonProperty("@Qualifier")]
        public string Qualifier { get; set; }

        [JsonProperty("#text")]
        public string text { get; set; }
    }

    public class Header
    {
        public To To { get; set; }
        public From From { get; set; }
        public string MessageID { get; set; }
        public DateTime SentTime { get; set; }
        public Security Security { get; set; }
        public SenderSoftware SenderSoftware { get; set; }
        public DigitalSignature DigitalSignature { get; set; }
    }

    public class HumanPatient
    {
        public Name Name { get; set; }
        public string Gender { get; set; }
        public DateOfBirth DateOfBirth { get; set; }
        public Address Address { get; set; }
    }

    public class Identification
    {
        public string NCPDPID { get; set; }
        public string NPI { get; set; }
    }

    public class MedicationPrescribed
    {
        public string DrugDescription { get; set; }
        public DrugCoded DrugCoded { get; set; }
        public Quantity Quantity { get; set; }
        public WrittenDate WrittenDate { get; set; }
        public string Substitutions { get; set; }
        public string NumberOfRefills { get; set; }
        public Sig Sig { get; set; }
    }

    public class Message
    {
        [JsonProperty("@DatatypesVersion")]
        public string DatatypesVersion { get; set; }

        [JsonProperty("@TransportVersion")]
        public string TransportVersion { get; set; }

        [JsonProperty("@TransactionDomain")]
        public string TransactionDomain { get; set; }

        [JsonProperty("@TransactionVersion")]
        public string TransactionVersion { get; set; }

        [JsonProperty("@StructuresVersion")]
        public string StructuresVersion { get; set; }

        [JsonProperty("@ECLVersion")]
        public string ECLVersion { get; set; }
        public Header Header { get; set; }
        public Body Body { get; set; }
    }

    public class Name
    {
        public string LastName { get; set; }
        public string FirstName { get; set; }
    }

    public class NewRx
    {
        public Patient Patient { get; set; }
        public Pharmacy Pharmacy { get; set; }
        public Prescriber Prescriber { get; set; }
        public MedicationPrescribed MedicationPrescribed { get; set; }
    }

    public class NonVeterinarian
    {
        public Identification Identification { get; set; }
        public Name Name { get; set; }
        public Address Address { get; set; }
        public CommunicationNumbers CommunicationNumbers { get; set; }
    }

    public class Password
    {
        [JsonProperty("@Type")]
        public string Type { get; set; }

        [JsonProperty("#text")]
        public string text { get; set; }
    }

    public class Patient
    {
        public HumanPatient HumanPatient { get; set; }
    }

    public class Pharmacy
    {
        public Identification Identification { get; set; }
        public string Specialty { get; set; }
        public string BusinessName { get; set; }
        public Address Address { get; set; }
        public CommunicationNumbers CommunicationNumbers { get; set; }
    }

    public class Prescriber
    {
        public NonVeterinarian NonVeterinarian { get; set; }
    }

    public class PrimaryTelephone
    {
        public string Number { get; set; }
    }

    public class Quantity
    {
        public string Value { get; set; }
        public string CodeListQualifier { get; set; }
        public QuantityUnitOfMeasure QuantityUnitOfMeasure { get; set; }
    }

    public class QuantityUnitOfMeasure
    {
        public string Code { get; set; }
    }

    public class Receiver
    {
        public string SecondaryIdentification { get; set; }
        public string TertiaryIdentification { get; set; }
    }

    public class Root
    {
        public Message Message { get; set; }
    }

    public class Security
    {
        public UsernameToken UsernameToken { get; set; }
        public Sender Sender { get; set; }
        public Receiver Receiver { get; set; }
    }

    public class Sender
    {
        public string SecondaryIdentification { get; set; }
        public string TertiaryIdentification { get; set; }
    }

    public class SenderSoftware
    {
        public string SenderSoftwareDeveloper { get; set; }
        public string SenderSoftwareProduct { get; set; }
        public string SenderSoftwareVersionRelease { get; set; }
    }

    public class Sig
    {
        public string SigText { get; set; }
    }

    public class To
    {
        [JsonProperty("@Qualifier")]
        public string Qualifier { get; set; }

        [JsonProperty("#text")]
        public string text { get; set; }
    }

    public class UsernameToken
    {
        public string Username { get; set; }
        public Password Password { get; set; }
        public string Nonce { get; set; }
        public DateTime Created { get; set; }
    }

    public class WrittenDate
    {
        public string Date { get; set; }
    }

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Boris Von Dahle 3,121 Reputation points
    2023-04-15T10:30:05.85+00:00 
    using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Newtonsoft.Json;

namespace SQLToJson
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Server=YourServer;Database=YourDatabase;User Id=YourUsername;Password=YourPassword;";
            string query = "SELECT * FROM YourTable";
            string outputFilePath = "output.json";

            DataTable dataTable = new DataTable();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    connection.Open();

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        dataTable.Load(reader);
                    }
                }
            }

            string json = JsonConvert.SerializeObject(dataTable, Formatting.Indented);
            File.WriteAllText(outputFilePath, json);

            Console.WriteLine($"JSON file created: {outputFilePath}");
        }
    }
}