locked
Import JSON documents into SQL Server RRS feed

  • Question

  • 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 = jsondocument;

            // 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




    Monday, July 27, 2020 8:04 PM

Answers

  • What are you using for inserting / updating data ? Are you using ADO.NET? If so, just insert as a normal string through UPDATE / INSERT command with parameters.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, July 27, 2020 8:16 PM

All replies