C# LINQ: Saving JSON Data to SQL Server Database using JSON.NET and SqlBulkCopy

By Brij Mohan

In this post, we will add JSON data to Sql Server tables using ASP.NET Web API, JSON.NET and SqlBulkCopy. The data is posted using jQuery ajax to web api which accepts JObject type argument, parse data, arrange data in the required table structure and save to database. Here is the structure of JSON and SQL Server tables.

Our object is to save 2009 data in the tables as in above image.
Here is the raw view of JSON object:

?

{     "states": {         "2005": {             "US-AL": 7.2,             ....         },         "2006": {             ...         },         "2007": {             ...         },         "2008": {             ...         },         "2009": {             "US-AL": 11.1,             "US-AK": 9.5,            ...         }     },     "metro": {         "codes": [             "3100000US12060",              ...         ],         "coords": [             [                 33.9783241,                 -84.4783064             ],             [                 30.51220349999999,                 -97.67312530000001             ],             ...         ],         "names": [             "Atlanta-Sandy Springs-Marietta, GA",             "Austin-Round Rock, TX",             ...         ],         "population": {             "2005": [                 4828838,                 ...                 5119490             ],             "2006": [                 5134871,                ...                 5288670             ],             "2007": [                 5271550,                 ...                 5356474             ],             "2009": [                 5476664,                ...                 5476241             ]         },         "unemployment": {             "2005": [                 7,                 6.7,                ...                 5             ],             "2006": [                 7.1,                 ...                 4.6             ],             "2007": [                 7.2,                ...                 4.7             ],             "2008": [                 7.2,                ...                 4.9             ],             "2009": [                 11.6,                 ...                 7.3             ]         }     } }

Assuming JSON is already retrieved or defined in javascript data variable. To post data:

?

var data = { JSON to be saved };  $.ajax({ url: 'api/data', type: 'POST', data: data, dataType: 'json' })             .done(function (data) {                 alert('saved successfully');             })

Web API:

In API controller, to handle post request:

?

public void Post(JObject value) {           }

To get Unemployment rate of states in 2009:

?

var states = ((JObject)value["states"]["2009"]).Properties().Select(x => new { StateCode = x.Name, Rate = Convert.ToDecimal(x.Value.ToString()) });

To get Metros table related data:

?

var codes = value["metro"]["codes"];            var lat = value["metro"]["coords"].Select(x => float.Parse(x[0].ToString()));            var lng = value["metro"]["coords"].Select(x => float.Parse(x[1].ToString()));            var name = value["metro"]["names"];            var population = value["metro"]["population"]["2009"];            var unemployment = value["metro"]["unemployment"]["2009"];

To combine data by index, we use join by index technique:

?

var output = (from n1 in codes.Select((item, index) => new { item, index })                         join n2 in lat.Select((item, index) => new { item, index }) on n1.index equals n2.index                         join n3 in lng.Select((item, index) => new { item, index }) on n2.index equals n3.index                         join n4 in name.Select((item, index) => new { item, index }) on n3.index equals n4.index                         join n5 in population.Select((item, index) => new { item, index }) on n4.index equals n5.index                         join n6 in unemployment.Select((item, index) => new { item, index }) on n5.index equals n6.index                         select new { Code = n1.item.ToString(), Lat = n2.item, Lng = n3.item, Name = n4.item.ToString(), Population = n5.item.Value<int>(), Unemployment = n6.item.Value<decimal>()}).ToList();

In order to use the SqlBulkCopy class for better performance, we need to be able to convert our collection to an array of DataRow instances, a DataTable or an IDataReader to use WriteToServer method. For this, download and add Linq Entity Data Reader and use AsDataReader() method to convert collection to array of IDataReader.

To save data, in database:

?

using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))           {               cn.Open();               using (SqlBulkCopy copy = new SqlBulkCopy(cn))               {                   copy.DestinationTableName = "UnemploymentRate";                   copy.ColumnMappings.Add("StateCode", "StateCode");                   copy.ColumnMappings.Add("Rate", "Rate");                   copy.WriteToServer(states.AsDataReader());               }                 using (SqlBulkCopy copy = new SqlBulkCopy(cn))               {                   copy.DestinationTableName = "Metros";                   copy.ColumnMappings.Add("Code", "Codes");                   copy.ColumnMappings.Add("Lat", "Latitude");                   copy.ColumnMappings.Add("Lng", "Longitude");                   copy.ColumnMappings.Add("Name", "Name");                   copy.ColumnMappings.Add("Population", "Population");                   copy.ColumnMappings.Add("Unemployment", "Unemployment");                   copy.WriteToServer(output.AsDataReader());               }           }

Now run the application and enjoy the JSON data in sql server database.