JSON to SQL Table

Question

I have following JSON data fetched from API and saved in object in C# and want it to insert in SQL database.

{
"6389": {
    "Offer": {
      "id": "6389",
      "name": "Swim",
      "description": "Incent",
      "require_approval": "1",
     }
  },
  "6141": {
    "Offer": {
      "id": "6141",
      "name": "Express",
      "description": "Incent",
      "require_approval": "1",
      }
  },
  "5677": {
    "Offer": {
      "id": "5677",
      "name": "Alive",
      "description": "",
      "require_approval": "1",
     }
  },
  "6669": {
    "Offer": {
      "id": "6669",
      "name": "All",
      "description": "Incent\r\",
      "require_approval": "1",
     }
  },
  "6767": {
    "Offer": {
      "id": "6767",
      "name": "App",
      "description": "",
      "require_approval": "1",
    }
  }

}

and i want to save the above JSON in below format of SQL database table.

ID      name       description  require_approval
6389    Swim       Incent             1
6141    Express    Incent             1
5677    Alive                         1
6669    All        Incent/r/          1
6767    App                           1

Thanks in advance.

assuming any difference in the data is just for expediency, what problem are you having inserting data into a database? — crashmstr, Mar 18 '16 at 10:54
data mismatch between JSON and table was by mistake. Do you know what should i do to insert the following JSON in SQL Table? — Sindhu, Mar 18 '16 at 11:21
Use [entity framework](http://stackoverflow.com/questions/8835434/insert-data-using-entity-framework-model), [SQL Table Adapter](https://msdn.microsoft.com/en-us/library/ms233812.aspx), or [SQL INSERT statements](https://technet.microsoft.com/en-us/library/dd776381%28v=sql.105%29.aspx). — crashmstr, Mar 18 '16 at 11:46
I am new to c# coding and i don't know how to use them. If you know, can you help with the code or any reference link? Thanks. — Sindhu, Mar 18 '16 at 11:50
Look at those links in my previous comment. You should also look into a book, class, or tutorial on working with databases. — crashmstr, Mar 18 '16 at 11:52
@Sindhu: Did you raised the same question twise? http://stackoverflow.com/questions/36061520/insert-json-text-into-sql-using-c-sharp — huMpty duMpty, Mar 18 '16 at 12:00
@huMptyduMpty - in that i was getting error and now i am stuck at another thing. As you can see, i am not getting any error here. — Sindhu, Mar 18 '16 at 12:38
@Sindhu That question has much more relevant details of what you are trying to do. This one is either too broad to answer as asked or a duplicate of your other one. — crashmstr, Mar 18 '16 at 12:38
Since in your *other* question you show you are using SQL Table Adapter, look at [How to: Insert New Records into a Database](https://msdn.microsoft.com/en-us/library/ms233812.aspx) — crashmstr, Mar 18 '16 at 12:40

score 0 · Answer 1 · edited Mar 18 '16 at 11:59

You need to implement something like this in your code.

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
  class Program
  {
      static void Main(string[] args)
      {
        HttpClient client = new HttpClient();

        client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

        HttpResponseMessage response = client.GetAsync("https://ebanking.bankofmaldives.com.mv/xe/").Result;


        if (response.IsSuccessStatusCode)
        {

            string JSON = response.Content.ReadAsStringAsync().Result;
            //Deserialize to strongly typed class i.e., RootObject
            RootObject obj = JsonConvert.DeserializeObject<RootObject>(JSON);
            //Suppose your connection string is with the name XYZEntities in web.config, and your database contains the table with proper structure required. Supposing table name as XYZ, then what you need to do is:
            //loop through the list and insert into database
            using(var db = new XYZEntities){
            foreach (CurrencyList currencyItem in obj.CurrencyList)
            {
                  db.XYZ.Add(new XYZ(){
                  key = currencyItem.Key,
                  code = currencyItem.Value.Code, 
                  description = currencyItem.Value.Description,
                  buy = currencyItem.Value.Buy,
                  sell = currencyItem.Value.Sell
                  });                  
            }
            try{
                 db.SaveChanges();
               }catch(Exception e){
                //do something to handle exception here.
               }
          }

        }
    }

    **Note: Below classes are generated by utilising http://json2csharp.com/**
    **ofcourse these can be hand coded by looking at the Json response from the service**

    public class Value
    {
        public string Code { get; set; }
        public string Description { get; set; }
        public double Buy { get; set; }
        public double Sell { get; set; }
    }

    public class CurrencyList
    {
        public string Key { get; set; }
        public Value Value { get; set; }
    }

    public class RootObject
    {
        public List<CurrencyList> CurrencyList { get; set; }
    }

  }
}

You need to create a table and insert data in these objects to that table. Please refer How to save json to Sql Database

Unfortunately this does not show at all how to get the data into a database table (which seems to be where the questioner has the problem). — crashmstr, Mar 18 '16 at 11:50
Unless Sindhu already has entity framework set up in the project and their tables added, this won't really make much sense (plus, I think this question is too broad to answer well based on their knowledge). Good attempt though. — crashmstr, Mar 18 '16 at 12:00
That is why I was not mentioning the details of inserting into db. But the logic is same, no matter which db is he using. — Alok Gupta, Mar 18 '16 at 12:16
Finally comes out: they are using SQL Table Adapter classes. So your example with entity framework will not help. — crashmstr, Mar 18 '16 at 12:41

JSON to SQL Table

1 Answers1