Import JSON documents into SQL Server

[CSharp Enthusiast]

Hi, I am using newtosoft library and not sure how to import json document into SQL Server.

Table:

create table dbo.JsonDoc (
id int primary key identity,
jsondocument nvarchar(max)
);

json document:

[
{ "id" : 2,"firstName": "John", "lastName": "Smith", "age": 25, "dateOfBirth": "2007-03-25T12:00:00" },
{ "id" : 5,"firstName": "James", "lastName": "Brown", "age": 35, "dateOfBirth": "2005-11-04T12:00:00" },
{ "id" : 7,"firstName": "Jeanne", "lastName": "Roberts", "age": 15, "dateOfBirth": "1983-10-28T12:00:00" },
{ "id" : 8,"firstName": "Johan", "lastName": "Walter", "age": 12, "dateOfBirth": "1995-07-05T12:00:00" },
{ "id" : 9,"firstName": "Jonathon", "lastName": "Lopez", "age": 37, "dateOfBirth": "2015-03-25T12:00:00" }
]

C#:


string json;
json = getData(); // json document from api
//Json[] Jsondoc = JsonConvert.DeserializeObject<Json[]>(json);

string connectionString = @"Data Source=SQLDEV;Initial Catalog=DBDEV;Integrated Security=SSPI;";

// query
string query = "INSERT INTO dbo.JsonDoc (jsondocument) VALUES (@JsonDoc) ";

// create connection and command
using(SqlConnection cn = new SqlConnection(connectionString))
using(SqlCommand cmd = new SqlCommand(query, cn))
{
// define parameters and their values
cmd.Parameters.Add("@JsonDoc", SqlDbType.NVarChar, max).Value = json;

// open connection, execute INSERT, close connection
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}


I am stuck with how to import json document into the column jsondocument of the table dbo.JsonDoc.

Thank you.




SQLEnthusiast

Continue reading...