import JSON file to MS SQL server

Question

I make an API call to a URL using C# and it returns JSon response and i download the json file.

i would like to write the json data to database for use by a web application. how can i import the json file into sql or write it direct from my c# program to db . am using sql 2016

My JSon file looks as follows :

{

    "Items": [
    {
        "LocalTimestamp": "2017-07-05T18:59:29+02:00",
        "Id": 653914348,
        "Description": "Trip Shutdown",
        "Processed": false,
        "Position": [
            27.90225,
            -26.07706
        ]
    },
    {
        "LocalTimestamp": "2017-07-05T08:41:08+02:00",
        "Id": 653709051,
        "Description": "Trip Startup",
        "Processed": false,
        "Position": [
            27.90229,
            -26.07753
        ]
    },
    {
        "LocalTimestamp": "2017-07-05T07:42:19+02:00",
        "Id": 653692142,
        "Description": "Trip Shutdown",
        "Processed": false,
        "Position": [
            27.90228,
            -26.07708
        ]
    },
    {
        "LocalTimestamp": "2017-07-05T07:21:47+02:00",
        "Id": 653683088,
        "Description": "Trip Startup",
        "Processed": false,
        "Position": [
            27.9222,
            -26.04318
        ]
    },
    {
        "LocalTimestamp": "2017-07-05T07:21:00+02:00",
        "Id": 653682830,
        "Description": "Trip Shutdown",
        "Processed": false,
        "Position": [
            27.92348,
            -26.04612
        ]
    },
    {
        "LocalTimestamp": "2017-07-04T10:15:35+02:00",
        "Id": 653330923,
        "Description": "Trip Shutdown",
        "Processed": false,
        "Position": [
            27.997,
            -26.05747
        ]
    },
    {
        "LocalTimestamp": "2017-07-04T09:41:19+02:00",
        "Id": 653320268,
        "Description": "Trip Startup",
        "Processed": false,
        "Position": [
            28.02899,
            -26.20546
        ]
    },
    {
        "LocalTimestamp": "2017-07-04T09:32:54+02:00",
        "Id": 653317769,
        "Description": "Trip Shutdown",
        "Processed": false,
        "Position": [
            28.0293,
            -26.20549
        ]
    },
    {
        "LocalTimestamp": "2017-07-03T18:40:43+02:00",
        "Id": 653089737,
        "Description": "Trip Shutdown",
        "Processed": false,
        "Position": [
            27.90228,
            -26.07707
        ]
    },
    {
        "LocalTimestamp": "2017-07-03T18:29:01+02:00",
        "Id": 653086470,
        "Description": "Trip Startup",
        "Processed": false,
        "Position": [
            27.94599,
            -26.07828
        ]
    },
    {
        "LocalTimestamp": "2017-07-03T18:26:30+02:00",
        "Id": 653085810,
        "Description": "Trip Shutdown",
        "Processed": false,
        "Position": [
            27.94662,
            -26.07807
        ]
    },
    {
        "LocalTimestamp": "2017-07-03T18:15:50+02:00",
        "Id": 653082613,
        "Description": "Trip Startup",
        "Processed": false,
        "Position": [
            27.98847,
            -26.05269
        ]
    },
    {
        "LocalTimestamp": "2017-07-03T18:13:15+02:00",
        "Id": 653082018,
        "Description": "Trip Shutdown",
        "Processed": false,
        "Position": [
            27.99036,
            -26.05341
        ]
    }
 ],
 "HasMoreResults": false
}

Answer 1

Ok. As i have mentioned in my comment, you have to de-serialize the json string which you received to object/class and save that class/object properties to database using c#.

In database, you need to create tables based on your "json string structure" or "class/object".

According to your json string the class should be like below

public class Item
{
    public string LocalTimestamp { get; set; }
    public int Id { get; set; }
    public string Description { get; set; }
    public bool Processed { get; set; }
    public List<double> Position { get; set; }
}

public class AllItems
{
    public List<Item> Items { get; set; }
    public bool HasMoreResults { get; set; }
}

To de-serialize the json string, below is the code and for that, you need the namespace "System.Web.Script.Serialization".

string jsonstring = @"define your json string here";
//Deserialize the json string to the object/class format
var serializer = new JavaScriptSerializer();
AllItems allItemsObj = serializer.Deserialize<AllItems>(jsonstring);

Create your database table as per the json string structure

CREATE TABLE AllItemsTable (
    ItemIdPrimary int NOT NULL IDENTITY(1,1),
    LocalTimestamp nvarchar(255),
    Id int,
    Description nvarchar(255),
    Processed nvarchar(255),
    Position1 nvarchar(50),
    Position2 nvarchar(50)

    PRIMARY KEY (ItemIdPrimary)
);

Here the complete code to de-serialize the json string and save data to database.

For jsonstring, i have added a json file named as "test1.json" inside the application and copied your json string to the json file.Late i am consuming the json from that file("test1.json")

using System;
using System.Collections.Generic;
using System.IO;
using System.Web.Script.Serialization;
using System.Data.SqlClient;
using System.Configuration;

namespace DeserializeJson2ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            //Get the Json string
            string jsonstring = File.ReadAllText(@"D:\My Apps\Console Applications\DeserializeJson2ConsoleApp\DeserializeJson2ConsoleApp\test1.json");
            //Deserialize the json string to the object/class format
            var serializer = new JavaScriptSerializer();
            AllItems allItemsObj = serializer.Deserialize<AllItems>(jsonstring);
            //Save the deserilized object/class to database
            //Get your connection string defined inside the Web.config(for web application) / App.config(for console/windows/wpf/classlibrary application) file
            string myConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
            //Create your Sql Connection here
            using (SqlConnection con = new SqlConnection(myConnectionString))
            {
                //Open the sql connection
                con.Open();
                //Loop each items and save to database
                foreach (var item in allItemsObj.Items)
                {
                    //Save/Insert to database
                    if(SaveToDatabase(con, item))
                    {
                        Console.WriteLine("Success : " + item.Description + " Saved into database");
                    }
                    else
                    {
                        Console.WriteLine("Error : " + item.Description + " unable to Saved into database");
                    }
                }
            }
            Console.Read();
        }

        /// <summary>
        /// Insert to database
        /// </summary>
        /// <param name="con"></param>
        /// <param name="aItemObj"></param>
        /// <returns></returns>
        static bool SaveToDatabase(SqlConnection con,Item aItemObj)
        {
            try
            {
                string insertQuery = @"Insert into AllItemsTable(LocalTimestamp,Id,Description,Processed,Position1,Position2) Values(@LocalTimestamp,@Id,@Description,@Processed,@Position1,@Position2)";
                using (SqlCommand cmd = new SqlCommand(insertQuery, con))
                {
                    cmd.Parameters.Add(new SqlParameter("@LocalTimestamp",aItemObj.LocalTimestamp));
                    cmd.Parameters.Add(new SqlParameter("@Id", aItemObj.Id));
                    cmd.Parameters.Add(new SqlParameter("@Description", aItemObj.Description));
                    cmd.Parameters.Add(new SqlParameter("@Processed", aItemObj.Processed));
                    for(int index=0;index<aItemObj.Position.Count;index++)
                    {
                        if(index==0)
                            cmd.Parameters.Add(new SqlParameter("@Position1", aItemObj.Position[index].ToString()));
                        else
                            cmd.Parameters.Add(new SqlParameter("@Position2", aItemObj.Position[index].ToString()));
                    }
                    cmd.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception objEx)
            {
                return false;
            }
        }
    }

    public class Item
    {
        public string LocalTimestamp { get; set; }
        public int Id { get; set; }
        public string Description { get; set; }
        public bool Processed { get; set; }
        public List<double> Position { get; set; }
    }

    public class AllItems
    {
        public List<Item> Items { get; set; }
        public bool HasMoreResults { get; set; }
    }
}

And here is the message in console after data saved to the database.

And here is the data which has been saved to the database.

Hope, it solves your problem. Revert me back if you have any issues.