How to auto generate/create SQL server table from JSON in C#

Question

I am getting JSON of Form Data from users. Below is the example of JSON Data

{  
   "Name":"Mike",
   "Age":25,
   "Gender":"Male",
   "Skills":{  
      ".Net":true,
      "Mule":""
   }
}

I want to save this data in a table (SQL Server). There is no table in the database, I want to define table name before sending this data to sql. Is there any approach to achieve this. Please help.

You can deserialize this JSON string, then store it in a table. — SᴇM, Dec 03 '19 at 08:17
Hi @SᴇM, As I am new to this thing. Could you please elaborate with some example. — Abhi, Dec 03 '19 at 08:20
Take a look at this post https://stackoverflow.com/questions/11981282/convert-json-to-datatable/11982180#11982180 — SᴇM, Dec 03 '19 at 08:23
This question is not clear for me. You want to store this Json in Database but you don't have table right?. So do you want to create Table in DB runtime or so? — Selim Yildiz, Dec 03 '19 at 09:38
You can do it using ado.net like any other sql statement like `CREATE TABLE ..` — Selim Yildiz, Dec 03 '19 at 09:44

score 2 · Answer 1 · answered Dec 03 '19 at 08:50

I suggest using json2csharp to convert the JSON to C# models and alter the names which are not recognized

    public class Skills
    {
        [JsonProperty(PropertyName = ".Net")] //Add JsonProperty to include unclassified names
        public bool DotNet { get; set; }
        public string Mule { get; set; }
    }

    public class RootObject10
    {
        public string Name { get; set; }
        public int Age { get; set; }
        public string Gender { get; set; }
        public Skills Skills { get; set; }
    }

then, you can deserialize the json using JsonConvert

using (StreamReader r = new StreamReader(filepath))
{
      string json = r.ReadToEnd();
      var obj = JsonConvert.DeserializeObject<RootObject10>(json);
}

After this, based on your requirement create single data table or 2 data tables and inject the data

score 1 · Accepted Answer · answered Dec 03 '19 at 09:44

Assuming your question is not about how to translate json into c# object but rather about how to store it in SQL Server while still being able to query it, you can actually work with json data in sql server: https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15.

I would opt for something like that if I didn’t have schema upfront or I knew it will change often. Then I would create a table called FormData with an Id and Data fields and just stored your JSON in there.

Bear in mind this is likely less performant than defining tables and properly parsing json (which is covered by other answers here) - make sure you make the call after you’ve considered all pros and cons of schema-less storage.

Upd: if you absolutely must create tables at runtime you could potentially run plain SQL DDL statements like ´CREATE TABLE´ using plain ADO.NET

How to auto generate/create SQL server table from JSON in C#

2 Answers2