Advance JSON deserialize > SQL Insert

thexiscoming

New member
Joined
Apr 24, 2016
Messages
3
Programming Experience
1-3
Hope someone can please help

I want to Insert JSON Values to a SQL Database. It is working, but There is an object within the main object, which I cannot seem to retrieve the value.

Here is my JSON code
C#:
{  
   "items":[  
      {  
         "uid":"b003a6ad8ef543dfbebf3ea2d48a1c33",
         "commStatus":"OK",
         "gateway":{  
            "serialNumber":"CA13313053110 05",
            "type":null,
            "uid":"6b8ca67f3eec46c1a94f535c57d d48a3",
            "imei":"353567040903313",
            "macAddress":null
         },
         "subscription":{  
            "identifier":"8927000003268372315 ",
            "state":"INVENTORY",
            "mobileNumber":null,
            "network Identifier":"655103015669848",
            "uid":"1ba23cc6f6bc4 a26b42029e4d5b83d1f",
            "ipAddress":"10.13.14.40",
            "operator":"MTN"
         },
         "name":"220"
      },
      {  
         "uid":"500ed42280594 d5b9a3049db15b6e5c9",
         "commStatus":"OK",
         "gateway":{  
            "serialNumber":"CA1301201391004",
            "type":"GX400",
            "uid":"515334dbf5154911aa019520508b8077",
            "imei":"353 567041609349",
            "macAddress":"00:14:3E:11:48:0D"
         },
         "subscription":{  
            "identifier":"8927000002300070259",
            "state":"INVENTORY",
            "mobileNumber":null,
            "networkIde ntifier":"655103308678726",
            "uid":"073f0fc7ece240e7 807db5bc1538d9ef",
            "ipAddress":"10.13.4.51",
            "operator":"MTN"
         },
         "name":"4116"
      }
   ],
   "count":380,
   "size":100,
   "offset":0
}

So my first problem I am having, is I have to remove the {"items":, otherwise I don't get any values.

  1. So how can I modify the code to include the {"items":

My second problem I am having: gateway is another object, so anything within gateway, I am unable to receive those values. I am trying to get the value of serialNumber, but its returning null.

  1. So my 2nd Question is how can I get values within another object
Here is my code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;
using System.IO;
using System.Data.SqlClient;

namespace EMS_AVMS_Retrieval
{
    class Program
    {
    [DataContract]
    class items
    {
        [DataMember]
        public string uid { get; set; }
        [DataMember]
        public string commStatus { get; set; }
        [DataMember]
        public string serialNumber { get; set; }
    }

    //[DataContract]
    //public class Gateway
    //{
    //    [DataMember]
    //    public string serialNumber { get; set; }
    //}
    static void Main(string[] args)
    {

        string json = @"[{"items":[{"uid":"b003a6ad8ef543dfbebf3ea2d48a1c33","commStatus":"OK","gateway":{"serialNumber":"CA1331305311005","type":null,"uid":"6b8ca67f3eec46c1a94f535c57dd48a3","imei":"353567040903313","macAddress":null},"subscription":{"identifier":"8927000003268372315","state":"INVENTORY","mobileNumber":null,"networkIdentifier":"655103015669848","uid":"1ba23cc6f6bc4a26b42029e4d5b83d1f","ipAddress":"10.13.14.40","operator":"MTN"},"name":"220"},{"uid":"500ed42280594d5b9a3049db15b6e5c9","commStatus":"OK","gateway":{"serialNumber":"CA1301201391004","type":"GX400","uid":"515334dbf5154911aa019520508b8077","imei":"353567041609349","macAddress":"00:14:3E:11:48:0D"},"subscription":{"identifier":"8927000002300070259","state":"INVENTORY","mobileNumber":null,"networkIdentifier":"655103308678726","uid":"073f0fc7ece240e7807db5bc1538d9ef","ipAddress":"10.13.4.51","operator":"MTN"},"name":"4116"}],"count":380,"size":100,"offset":0}]";

        //Deserialize the data
        DataContractJsonSerializer ser = new DataContractJsonSerializer(typeof(List<items>));
        MemoryStream stream = new MemoryStream(Encoding.UTF8.GetBytes(json));
        var obj = (List<items>)ser.ReadObject(stream);

        //Loop thrrouch values and save the details into database
        foreach (items p in obj)
        {
            //Create SQL conection to your database here
            using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["my    ConnectionString"].ConnectionString))
            {
                // Open your connection
                try
                {
                    cn.Open();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                    Console.ReadLine();
                    Environment.Exit(0);
                }
                //Change the table name here
                string sql = "INSERT INTO tempTableAVMSRetrievals (uid, commStatus, serialNumber, retrievalDate) VALUES (@uid, @commStatus, @serialNumber, @retrievalDate)";
                // Create the Command and Parameter objects.
                using (SqlCommand cmd = new SqlCommand(sql, cn))
                {
                    //Loop through the and get of parameter values
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Parameters.AddWithValue("@uid", p.uid);
                    cmd.Parameters.AddWithValue("@commStatus", p.commStatus);
                    cmd.Parameters.AddWithValue("@serialNumber", "SN");
                    cmd.Parameters.AddWithValue("@retrievalDate", currentDate);

                    //Execute the query
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch(Exception e2)
                    {
                        Console.WriteLine(e2.ToString());
                        Console.ReadLine();
                        Environment.Exit(0);
                    }


                }
            }
        }
        Environment.Exit(0);
    }
}
}
 
Last edited by a moderator:
Back
Top Bottom