locked
How to save json to Sql Database. RRS feed

  • Question

  • User-640512382 posted

    I would like to save the json result of the following URL to MSSQL at predefined intervals. 

    https://ebanking.bankofmaldives.com.mv/xe/

    {"CurrencyList":[{"Key":"AUD","Value":{"Code":"AUD","Description":"Australian Dollar","Buy":13.5722,"Sell":14.3624}},{"Key":"CAD","Value":{"Code":"CAD","Description":"Canadian Dollar","Buy":14.0242,"Sell":15.6559}},{"Key":"DKK","Value":{"Code":"DKK","Description":"Danish Krone","Buy":2.5221,"Sell":2.8883}}]}

     Slq table format as follows:

    Code Description Buying Selling
    AUD Australian Dollar 13.5722 14.3624
    CAD Canadian Dollar 14.0242 15.6559
    DKK Danish Krone 2.5221 2.8883

    How can I achieve this ?

    Saturday, July 27, 2013 4:11 PM

Answers

  • User220959680 posted

    Below is the tested solution. It is required to implement the database insert logic, which is trivial with EF.

    Approach 1: Anonymous type


    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)
            {
                //create an instance of HttpClient
                HttpClient client = new HttpClient();
    
                //DefaultRequestHeader to Json                                      
                client.DefaultRequestHeaders.Accept.Add(new  MediaTypeWithQualityHeaderValue("application/json"));
    
                //Create an instance of HttpResponse & invoke the service asynchronously
                HttpResponseMessage response = client.GetAsync("https://ebanking.bankofmaldives.com.mv/xe/").Result;
                
                //Http Status code 200
                if (response.IsSuccessStatusCode)
                {
                    //Read response content result into string variable  
                    string JSON = response.Content.ReadAsStringAsync().Result;
                   //Deserialize the string(JSON) object                 
                   var jObj = (JObject)JsonConvert.DeserializeObject(JSON);
       
                   //access items from anonymous (Json object) type and add to the list
                    var result = jObj["CurrencyList"].Select(item => new
                    {
                        key = item["Key"],
                        code = item["Value"]["Code"],
                        description = item["Value"]["Description"],
                        buy = item["Value"]["Buy"],
                        sell = item["Value"]["Sell"],
                    }).ToList();
    
                    //output the data || NOTE: **NSERT into database table**
                    foreach (var item in result)
                    { 
                     Console.WriteLine(item.key + "--" + item.code + "--" + item.description + item.buy + item.sell);
                    }
                }
            }
    
        }
    }
    

    Approach 2: Strongly typed


    Generate C# classes from Json at http://json2csharp.com/

    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);
    
                    //loop through the list and insert into database
                    foreach (CurrencyList currencyItem in obj.CurrencyList)
                    {
                    Console.WriteLine(currencyItem.Key + "-" + currencyItem.Value.Code + "-" + currencyItem.Value.Description +
                           "-"+ currencyItem.Value.Buy + "-" + currencyItem.Value.Sell);
                        
                    }
    
                }
            }
    
            **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; }
            }
            
        }
    }
    

    Nuget package

    This package adds support for formatting and content negotiation to System.Net.Http. It includes support for JSON, XML, and form URL encoded data.

    To install Microsoft ASP.NET Web API Client Libraries, run the following command in thePackage Manager Console

    PM> Install-Package Microsoft.AspNet.WebApi.Client -Version 4.0.30506.0

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, July 27, 2013 9:10 PM

All replies

  • User220959680 posted
    • Simple solution is to implement a Console application that makes Http request to the URI https://ebanking.bankofmaldives.com.mv/xe/ using HttpClient class.
    • Loop through the currency list objects and store into a List object(of type currency with same properties).
    • Insert the data while looping through each object (Foreach)
    • Schedule the console app(.exe) at specific intervals using windows scheduler
    Saturday, July 27, 2013 5:36 PM
  • User220959680 posted

    Below is the tested solution. It is required to implement the database insert logic, which is trivial with EF.

    Approach 1: Anonymous type


    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)
            {
                //create an instance of HttpClient
                HttpClient client = new HttpClient();
    
                //DefaultRequestHeader to Json                                      
                client.DefaultRequestHeaders.Accept.Add(new  MediaTypeWithQualityHeaderValue("application/json"));
    
                //Create an instance of HttpResponse & invoke the service asynchronously
                HttpResponseMessage response = client.GetAsync("https://ebanking.bankofmaldives.com.mv/xe/").Result;
                
                //Http Status code 200
                if (response.IsSuccessStatusCode)
                {
                    //Read response content result into string variable  
                    string JSON = response.Content.ReadAsStringAsync().Result;
                   //Deserialize the string(JSON) object                 
                   var jObj = (JObject)JsonConvert.DeserializeObject(JSON);
       
                   //access items from anonymous (Json object) type and add to the list
                    var result = jObj["CurrencyList"].Select(item => new
                    {
                        key = item["Key"],
                        code = item["Value"]["Code"],
                        description = item["Value"]["Description"],
                        buy = item["Value"]["Buy"],
                        sell = item["Value"]["Sell"],
                    }).ToList();
    
                    //output the data || NOTE: **NSERT into database table**
                    foreach (var item in result)
                    { 
                     Console.WriteLine(item.key + "--" + item.code + "--" + item.description + item.buy + item.sell);
                    }
                }
            }
    
        }
    }
    

    Approach 2: Strongly typed


    Generate C# classes from Json at http://json2csharp.com/

    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);
    
                    //loop through the list and insert into database
                    foreach (CurrencyList currencyItem in obj.CurrencyList)
                    {
                    Console.WriteLine(currencyItem.Key + "-" + currencyItem.Value.Code + "-" + currencyItem.Value.Description +
                           "-"+ currencyItem.Value.Buy + "-" + currencyItem.Value.Sell);
                        
                    }
    
                }
            }
    
            **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; }
            }
            
        }
    }
    

    Nuget package

    This package adds support for formatting and content negotiation to System.Net.Http. It includes support for JSON, XML, and form URL encoded data.

    To install Microsoft ASP.NET Web API Client Libraries, run the following command in thePackage Manager Console

    PM> Install-Package Microsoft.AspNet.WebApi.Client -Version 4.0.30506.0

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, July 27, 2013 9:10 PM