create json array in c#

Question

i have developing json feed

i am expected following output

 {
        "SectionId": "3",
        "SectionName": "News",
        "ParentSectionId": "",
        "MetaTitle": "News",
        "MetaDescription": "News",
        "MetaKeyword": "News",
        "Status": "A",
        "URL": "",
        "SiteID": "1",
        "RegionalLanguage1": "News",
        "RegionalLanguage2": "",
        "CreatedBy": "20",
        "CreatedDate": "11-11-2013 12:48:28",
        "IsSearchable": "Y",
        "LastUpdatedBy": "20",
        "LastUpdatedDate": "11-11-2013 12:54:51",
        "IsRSS": "True",
        "RssOrder": "",
        "ISCMS": "1",
        "Priority": "",
        "IsVideo": "0",
        "IsDisplayMenu": "0",
        "phone": [
          {
        "SectionId": "7",
        "SectionName": "Entertainment",
        "ParentSectionId": "",
        "MetaTitle": "Entertainment",
        "MetaDescription": "Entertainment",
        "MetaKeyword": "Entertainment",
        "Status": "A",
        "URL": "",
        "SiteID": "1",
        "RegionalLanguage1": "Entertainment",
        "RegionalLanguage2": "",
        "CreatedBy": "20",
        "CreatedDate": "11-11-2013 12:50:50",
        "IsSearchable": "Y",
        "LastUpdatedBy": "20",
        "LastUpdatedDate": "17-12-2013 13:49:15",
        "IsRSS": "True",
        "RssOrder": "",
        "ISCMS": "0",
        "Priority": "",
        "IsVideo": "0",
        "IsDisplayMenu": "1",       
      },
        ]
      },

using below code

 var  cont = new Cont();
         var pho1=new Phone();      
        JavaScriptSerializer serializer = new JavaScriptSerializer();
        var objectToSerialize = new RootObject();
        DataTable dt = new DataTable();
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ToString());
        SqlCommand command = new SqlCommand("select TOP 1000 [SectionId] ,[SectionName] ,[ParentSectionId]  ,[MetaTitle] ,[MetaDescription] ,[MetaKeyword] ,[Status],[URL] ,[SiteID] ,[RegionalLanguage1],[RegionalLanguage2] ,[CreatedBy] ,[CreatedDate],[IsSearchable],[LastUpdatedBy],[LastUpdatedDate],[IsRSS],[RssOrder],[ISCMS],[Priority],[IsVideo],[IsDisplayMenu] FROM [DATABASE1].[dbo].[EDMstSection] where ParentSectionId is null", connection);
        SqlDataAdapter da = new SqlDataAdapter(command);
        da.Fill(dt);
        List<Cont> ContCollection = new List<Cont>();
        foreach (DataRow dr in dt.Rows)
        {
            cont = new Cont()

              {
                  SectionId = dr["SectionId"].ToString(),
                  SectionName = dr["SectionName"].ToString(),
                  ParentSectionId = dr["ParentSectionId"].ToString(),
                  MetaTitle = dr["MetaTitle"].ToString(),
                  MetaDescription = dr["MetaDescription"].ToString(),
                  MetaKeyword = dr["MetaKeyword"].ToString(),
                  Status = dr["Status"].ToString(),
                  URL = dr["URL"].ToString(),
                  SiteID = dr["SiteID"].ToString(),
                  RegionalLanguage1 = dr["RegionalLanguage1"].ToString(),
                  RegionalLanguage2 = dr["RegionalLanguage2"].ToString(),
                  CreatedBy = dr["CreatedBy"].ToString(),
                  CreatedDate = dr["CreatedDate"].ToString(),
                  IsSearchable = dr["IsSearchable"].ToString(),
                  LastUpdatedBy = dr["LastUpdatedBy"].ToString(),
                  LastUpdatedDate = dr["LastUpdatedDate"].ToString(),
                  IsRSS = dr["IsRSS"].ToString(),
                  RssOrder = dr["RssOrder"].ToString(),
                  ISCMS = dr["ISCMS"].ToString(),
                  Priority = dr["Priority"].ToString(),
                  IsVideo = dr["IsVideo"].ToString(),
                  IsDisplayMenu = dr["IsDisplayMenu"].ToString(),
                  // phone = new Phone { Section = "kgljh" }
                  //  phone = new List<Phone>()
              };
            ContCollection.Add(cont);


            cont.phone = new List<Phone>();
            DataTable dt1 = new DataTable();
            SqlCommand command1 = new SqlCommand("SELECT TOP 1000 [SectionId] ,[SectionName] ,[ParentSectionId]  ,[MetaTitle] ,[MetaDescription] ,[MetaKeyword] ,[Status],[URL] ,[SiteID] ,[RegionalLanguage1],[RegionalLanguage2] ,[CreatedBy] ,[CreatedDate],[IsSearchable],[LastUpdatedBy],[LastUpdatedDate],[IsRSS],[RssOrder],[ISCMS],[Priority],[IsVideo],[IsDisplayMenu] FROM [DATABASE1].[dbo].[EDMstSection] where ParentSectionId='" + cont.ParentSectionId + "'", connection);
            SqlDataAdapter sqladapter = new SqlDataAdapter(command1);
            sqladapter.Fill(dt1);
            foreach (DataRow dr1 in dt1.Rows)
            {
                pho1 = new Phone
                {
                    SectionId = dr1["SectionId"].ToString(),
                    SectionName = dr1["SectionName"].ToString(),
                    ParentSectionId = dr1["ParentSectionId"].ToString(),
                    MetaTitle = dr1["MetaTitle"].ToString(),
                    MetaDescription = dr1["MetaDescription"].ToString(),
                    MetaKeyword = dr1["MetaKeyword"].ToString(),
                    Status = dr1["Status"].ToString(),
                    URL = dr1["URL"].ToString(),
                    SiteID = dr1["SiteID"].ToString(),
                    RegionalLanguage1 = dr1["RegionalLanguage1"].ToString(),
                    RegionalLanguage2 = dr1["RegionalLanguage2"].ToString(),
                    CreatedBy = dr1["CreatedBy"].ToString(),
                    CreatedDate = dr1["CreatedDate"].ToString(),
                    IsSearchable = dr1["IsSearchable"].ToString(),
                    LastUpdatedBy = dr1["LastUpdatedBy"].ToString(),
                    LastUpdatedDate = dr1["LastUpdatedDate"].ToString(),
                    IsRSS = dr1["IsRSS"].ToString(),
                    RssOrder = dr1["RssOrder"].ToString(),
                    ISCMS = dr1["ISCMS"].ToString(),
                    Priority = dr1["Priority"].ToString(),
                    IsVideo = dr1["IsVideo"].ToString(),
                    IsDisplayMenu = dr1["IsDisplayMenu"].ToString(),
                };
                cont.phone.Add(pho1);
            }
        }


        ContCollection.Add(cont);
        objectToSerialize.contacts = ContCollection;        
        Response.Write(serializer.Serialize(new { item = objectToSerialize }));

my database news is main section and news contain four sub sections.

but i got output is news and entertainment is main section.

i need entertainment is sub section of news

i got below output

{
                "SectionId": "3",
                "SectionName": "News",
                "ParentSectionId": "",
                "MetaTitle": "News",
                "MetaDescription": "News",
                "MetaKeyword": "News",
                "Status": "A",
                "URL": "",
                "SiteID": "1",
                "RegionalLanguage1": "News",
                "RegionalLanguage2": "",
                "CreatedBy": "20",
                "CreatedDate": "11-11-2013 12:48:28",
                "IsSearchable": "Y",
                "LastUpdatedBy": "20",
                "LastUpdatedDate": "11-11-2013 12:54:51",
                "IsRSS": "True",
                "RssOrder": "",
                "ISCMS": "1",
                "Priority": "",
                "IsVideo": "0",
                "IsDisplayMenu": "0",
                "phone": []
            },
            {
                "SectionId": "7",
                "SectionName": "Entertainment",
                "ParentSectionId": "",
                "MetaTitle": "Entertainment",
                "MetaDescription": "Entertainment",
                "MetaKeyword": "Entertainment",
                "Status": "A",
                "URL": "",
                "SiteID": "1",
                "RegionalLanguage1": "Entertainment",
                "RegionalLanguage2": "",
                "CreatedBy": "20",
                "CreatedDate": "11-11-2013 12:50:50",
                "IsSearchable": "Y",
                "LastUpdatedBy": "20",
                "LastUpdatedDate": "17-12-2013 13:49:15",
                "IsRSS": "True",
                "RssOrder": "",
                "ISCMS": "0",
                "Priority": "",
                "IsVideo": "0",
                "IsDisplayMenu": "1",
                "phone": []
            },

any one help me what is wrong in my code .

Answer 1

You can use this code for generating dynamic objects that can contains non pre-defined properties:

var dynamicList = new List<dynamic>();
dynamicList.Add(new { SectionId = "3", SectionName = "News", /*and other properties*/ });

and for converting to json string use this:

using System.Web.Script.Serialization;
...
JavaScriptSerializer jss = new JavaScriptSerializer();

string output = jss.Serialize(dynamicList);

Answer 2

Given the json object you need you can have these two model classes:

public class JsonWrapper
        {
            public string SectionId { get; set; }
            public string SectionName { get; set; }
            public string ParentSectionId { get; set; }
            public string MetaTitle { get; set; }
            public string MetaDescription { get; set; }
            public string MetaKeyword { get; set; }
            public string Status { get; set; }
            public string URL { get; set; }
            public string SiteID { get; set; }
            public string RegionalLanguage1 { get; set; }
            public string RegionalLanguage2 { get; set; }
            public string CreatedBy { get; set; }
            public string CreatedDate { get; set; }
            public string IsSearchable { get; set; }
            public string LastUpdatedBy { get; set; }
            public string LastUpdatedDate { get; set; }
            public string IsRSS { get; set; }
            public string RssOrder { get; set; }
            public string ISCMS { get; set; }
            public string Priority { get; set; }
            public string IsVideo { get; set; }
            public string IsDisplayMenu { get; set; }
            public List<Phone> phone { get; set; }
        }




public class Phone
    {
        public string SectionId { get; set; }
        public string SectionName { get; set; }
        public string ParentSectionId { get; set; }
        public string MetaTitle { get; set; }
        public string MetaDescription { get; set; }
        public string MetaKeyword { get; set; }
        public string Status { get; set; }
        public string URL { get; set; }
        public string SiteID { get; set; }
        public string RegionalLanguage1 { get; set; }
        public string RegionalLanguage2 { get; set; }
        public string CreatedBy { get; set; }
        public string CreatedDate { get; set; }
        public string IsSearchable { get; set; }
        public string LastUpdatedBy { get; set; }
        public string LastUpdatedDate { get; set; }
        public string IsRSS { get; set; }
        public string RssOrder { get; set; }
        public string ISCMS { get; set; }
        public string Priority { get; set; }
        public string IsVideo { get; set; }
        public string IsDisplayMenu { get; set; }
    }

You can read your data into these two classes and then use Newtonsoft library to serialize it to a json string.

Answer 3

It's pretty simple using Json.NET:

dynamic stuff = JsonConvert.DeserializeObject("{ 'Name': 'Jon Smith', 'Address': { 'City': 'New York', 'State': 'NY' }, 'Age': 42 }");

string name = stuff.Name;
string address = stuff.Address.City;