Easiest way of loading JSON data in SQL using C#

Article
10/31/2013

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:

-----------

(1 row(s) affected)

-----------

(1 row(s) affected)

-----------

(1 row(s) affected)

-----------

(1 row(s) affected)

-----------

(1 row(s) affected)

After:

-----------

(1 row(s) affected)

-----------

(1 row(s) affected)

-----------

(1 row(s) affected)

-----------

(1 row(s) affected)

-----------

(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…

Easiest way of loading JSON data in SQL using C#

Additional resources