Solved

trying to import JSON into Microsoft SQL database using C#

Posted on 2010-09-23
4
1,400 Views
Last Modified: 2013-11-08
Hi, I am pulling lead data in  JSON format  from a web site.
This page has an exmple of the JSON Response - http://docs.hubapi.com/wiki/Searching_Leads 
It is an array of one or more leads.  Each lead has several basic fields.
Each lead can also contain child info and some of that child info can also have child info.

I am fairly new to C# and .NET.  I have successfully been able to use JSON.NET to loop through the JSON and then loop through each lead and I can reference the different data fields and print the values to the immediate window.

My question - what would be the most efficient way to get the data into a MS SQL database?
I know I could create my sql tables in advance and then loop through the JSON leads and grab each value and populate the main data rows and the child table rows.
BUT is there a more efficient more automatic way of doing this?
AND what if the JSON format changes (they add a new field in the middle somewhere)?

Thanks in advance!
0
Comment
Question by:kevinvw1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
4 Comments
 
LVL 82

Accepted Solution

by:
leakim971 earned 500 total points
ID: 33750806
>BUT is there a more efficient more automatic way of doing this?
I don't know anyone, perhaps the next version of SQL Server...

>AND what if the JSON format changes (they add a new field in the middle somewhere)?
This is a common problem with all type of data format (xml, csv and so on) not only with JSON
If you need the new data, you create a new row for it and modify your DataContract (for example)
0
 
LVL 82

Expert Comment

by:leakim971
ID: 33750830
0
 
LVL 82

Expert Comment

by:leakim971
ID: 33762984
Another way : You may create a dedicated sql stored procedure accepting a json string and doing the job. So if the format change you "just" need to update the stored procedure
0
 
LVL 82

Expert Comment

by:leakim971
ID: 33872387
Thanks for the points!
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Suggested Courses

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question