Easiest way of loading JSON data in SQL using C#
I was searching this on Google\Bing and unfortunately did not find much interesting hits. (I can say that it was fortunate because that forced me to try something on my own)
So from there my ride began which was very short!!!
Initial goal was not to load in SQL but somehow get object structure out of JSON for which I did not find any simple way, and as we say as developer if it’s not simple try again. (If not till now from now and I invented it!!!)
So let’s come to THE POINT…
Let’s load some JSON data in SQL
JSON Data:
{"web-app": {
"servlet": [
{
"servlet-name": "cofaxCDS",
"servlet-class": "org.cofax.cds.CDSServlet",
"init-param": {
"configGlossary:installationAt": "Philadelphia, PA",
"configGlossary:adminEmail": "ksm@pobox.com",
"configGlossary:poweredBy": "Cofax",
"configGlossary:poweredByIcon": "/images/cofax.gif",
"configGlossary:staticPath": "/content/static",
"templateProcessorClass": "org.cofax.WysiwygTemplate",
"templateLoaderClass": "org.cofax.FilesTemplateLoader",
"templatePath": "templates",
"templateOverridePath": "",
"defaultListTemplate": "listTemplate.htm",
"defaultFileTemplate": "articleTemplate.htm",
"useJSP": false,
"jspListTemplate": "listTemplate.jsp",
"jspFileTemplate": "articleTemplate.jsp",
"cachePackageTagsTrack": 200,
"cachePackageTagsStore": 200,
"cachePackageTagsRefresh": 60,
"cacheTemplatesTrack": 100,
"cacheTemplatesStore": 50,
"cacheTemplatesRefresh": 15,
"cachePagesTrack": 200,
"cachePagesStore": 100,
"cachePagesRefresh": 10,
"cachePagesDirtyRead": 10,
"searchEngineListTemplate": "forSearchEnginesList.htm",
"searchEngineFileTemplate": "forSearchEngines.htm",
"searchEngineRobotsDb": "WEB-INF/robots.db",
"useDataStore": true,
"dataStoreClass": "org.cofax.SqlDataStore",
"redirectionClass": "org.cofax.SqlRedirection",
"dataStoreName": "cofax",
"dataStoreDriver": "com.microsoft.jdbc.sqlserver.SQLServerDriver",
"dataStoreUrl": "jdbc:microsoft:sqlserver://LOCALHOST:1433;DatabaseName=goon",
"dataStoreUser": "sa",
"dataStorePassword": "dataStoreTestQuery",
"dataStoreTestQuery": "SET NOCOUNT ON;select test='test';",
"dataStoreLogFile": "/usr/local/tomcat/logs/datastore.log",
"dataStoreInitConns": 10,
"dataStoreMaxConns": 100,
"dataStoreConnUsageLimit": 100,
"dataStoreLogLevel": "debug",
"maxUrlLength": 500}},
{
"servlet-name": "cofaxEmail",
"servlet-class": "org.cofax.cds.EmailServlet",
"init-param": {
"mailHost": "mail1",
"mailHostOverride": "mail2"}},
{
"servlet-name": "cofaxAdmin",
"servlet-class": "org.cofax.cds.AdminServlet"},
{
"servlet-name": "fileServlet",
"servlet-class": "org.cofax.cds.FileServlet"},
{
"servlet-name": "cofaxTools",
"servlet-class": "org.cofax.cms.CofaxToolsServlet",
"init-param": {
"templatePath": "toolstemplates/",
"log": 1,
"logLocation": "/usr/local/tomcat/logs/CofaxTools.log",
"logMaxSize": "",
"dataLog": 1,
"dataLogLocation": "/usr/local/tomcat/logs/dataLog.log",
"dataLogMaxSize": "",
"removePageCache": "/content/admin/remove?cache=pages&id=",
"removeTemplateCache": "/content/admin/remove?cache=templates&id=",
"fileTransferFolder": "/usr/local/tomcat/webapps/content/fileTransferFolder",
"lookInContext": 1,
"adminGroupID": 4,
"betaServer": true}}],
"servlet-mapping": {
"cofaxCDS": "/",
"cofaxEmail": "/cofaxutil/aemail/*",
"cofaxAdmin": "/admin/*",
"fileServlet": "/static/*",
"cofaxTools": "/tools/*"},
"taglib": {
"taglib-uri": "cofax.tld",
"taglib-location": "/WEB-INF/tlds/cofax.tld"}}}
Now goal is to push this data in SQL in minimum lines of code.
So what we will try to do is somehow get data in DataTable so that we can use BulkInsert to push data in SQL.
So what is needed to get DataTable out of JSON?
Consider data is text file which is on my local drive.
Code looks something like…
XmlDocument xml = JsonConvert.DeserializeXmlNode(System.IO.File.ReadAllText(@"\\Dil\d$\Dummy\Json.txt"), "RootObject");
DataSet ds = new DataSet("Json Data");
XmlReader xr= new XmlNodeReader(xml);
ds.ReadXml(xr);
That’s all.
Anything is thinking that it is not possible?
IT IS!!!
So now data is in DataSet which has following data tables,
1) Web-app
2) Servlet
3) Init-param
4) Servlet-mapping
5) Taglib
Isn’t that needed?
So next task is to push it in SQL (Assuming schema is already there)
SqlConnection conn = new SqlConnection("<Connection String>");
foreach (DataTable dt in ds.Tables)
{
Console.WriteLine("Bulk Insert Started table:" + dt.TableName);
SqlBulkCopy bulk = new SqlBulkCopy(conn);
bulk.DestinationTableName = "[" + dt.TableName.Replace('{',' ').Replace('}',' ') + "]";
bulk.WriteToServer(dt);
Console.WriteLine("Bulk Insert completed table:" + dt.TableName);
}
Now after running this query we should have data in SQL.
Count Query
select count(*) from [dbo].[init-param]
select count(*) from [dbo].[servlet]
select count(*) from [dbo].[servlet-mapping]
select count(*) from [dbo].[taglib]
select count(*) from [dbo].[web-app]
Before Insert:
-----------
0
(1 row(s) affected)
-----------
0
(1 row(s) affected)
-----------
0
(1 row(s) affected)
-----------
0
(1 row(s) affected)
-----------
0
(1 row(s) affected)
After:
-----------
3
(1 row(s) affected)
-----------
5
(1 row(s) affected)
-----------
1
(1 row(s) affected)
-----------
1
(1 row(s) affected)
-----------
1
(1 row(s) affected)
So boom… we have data in SQL now.
If you don’t have schema then you can create it dynamically also. I have covered it my other blog post.
Case Study: Import data from XML to SQL (Part-2)
What should be part of include?
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json;
using System.Data;
using System.Xml;
using System.IO;
using System.Data.SqlClient;
Newtonsoft.Json you could fine online and install it using nuget package manager.
I hope this was useful article and will help few people and achieving what they want.
Gracias…
Comments
Anonymous
July 02, 2014
Thanks, this post was helpful.Anonymous
September 16, 2014
The comment has been removedAnonymous
September 16, 2014
Im pulling data from file which has json data and pushing it in SQL server. Could you tell me what error message you are getting? -DilkushAnonymous
March 21, 2015
Thank you for this code - it has gotten me most of the way there. However, JsonConvert.DeserializeXmlNode creates its own index for related data (ie, 1-many relationships), Is there a way, with or without JSON schema, to make the resulting tables in the data set relate to each other using the keys in the data? For instance, there is a table called 'persons' and a related table 'phones' of phone numbers(where a person can have multiple phone numbers). The key in persons is 'personId', When deserialized, to dataset, tthe converter creates a column in the persons table called 'persons_Id' and relates the phones table to it (each record in phones has a column called 'persons_Id' and one called 'phones_Id'). In order to get the relationships to match the data, I have to create a new column called 'personId' in the phones table, join the phones table with the persons table on the key created by the converter, and update the new column to the personId value in the related parent record. I don't know how to do that with a system.data.datatable (I could do it in SQL but would have to make temp tables from all the tables in the dataset). There must be an easier way. Can you suggest something?Anonymous
March 22, 2015
Hi Steve, Working on Sunday Mornings!!!! not good i guess ;P Anyways one thing I'm not sure is whether your phones tables has personid column already or not. I'm assuming it has otherwise there would be no way finding out whose phone number it is. In that case you can create new data table and exclude this auto generate column and define new relationships in data table where we give primary and foreign column as per our wish. msdn.microsoft.com/.../ay82azad(v=vs.110).aspx www.functionx.com/.../Lesson06.htm I hope this helps. If my assumption was wrong them if possible give few sample rows of both tables if possible. Thanks, Dilkush