How to create a DataTable based on the Json deserialized JArray Data?

Question

I have fallowing JArray data. I need to create a datatable dynamically based on the column names in the Jarray, after that I need to insert the data.

Can you please help me to do this operation.

<pre>

{[
{
"PID": 3,
"FirstName": "parveen",
"LastName": "a",
"Gender": "male"
},
{
"PID": 8,
"FirstName": "ramarao",
"LastName": "M",
"Gender": "male"
}
]}
</pre>

Thanks in advance

purna

Solution 1

Your JSON input is not valid. You should remove the first and the last brackets, since it is an array, not an object. If you know the row type you should use one of the existing JSON libraries, and deserialize the array to a strongly typed list. If you don't know the type, use the toDataTable method.

I used the following library in the example:

http://james.newtonking.com/json

    private static void Main(string[] args)
    {
        var data =
            "[{\"PID\": 3,\"FirstName\": \"parveen\",\"LastName\": \"a\",\"Gender\": \"male\"},{\"PID\": 8,\"FirstName\": \"ramarao\",\"LastName\": \"M\",\"Gender\": \"male\"}]";

        var dattable = toDataTable(data);

        var list = toList(data);
    }

    class User
    {
        public int PID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Gender { get; set; }
    }

    private static List<User> toList(string json)
    {
       return Newtonsoft.Json.JsonConvert.DeserializeObject<List<User>>(json);
    } 

    private static DataTable toDataTable(string json)
    {
        var result = new DataTable();
        var jArray = JArray.Parse(json);
        //Initialize the columns, If you know the row type, replace this   
        foreach (var row in jArray)
        {
            foreach (var jToken in row)
            {
                var jproperty = jToken as JProperty;
                if (jproperty == null) continue;
                if (result.Columns[jproperty.Name] == null)
                    result.Columns.Add(jproperty.Name,typeof(string));
            }
        }
        foreach (var row in jArray)
        {
            var datarow = result.NewRow();
            foreach (var jToken in row)
            {
                var jProperty = jToken as JProperty;
                if (jProperty == null) continue;
                datarow[jProperty.Name] = jProperty.Value.ToString();
            }
            result.Rows.Add(datarow);
        }

        return result;
    }

Solution 2

This worked for me:

 DataTable dataTable = JsonConvert.DeserializeObject<DataTable>(JsonConvert.SerializeObject(validJArrayData));

Solution 3

With a valid JArray the following code should do what you want.

DataTable MyTable = JsonConvert.DeserializeObject<DataTable>(YOURJARRAY.ToString());

You will need to name the table.

MyTable.TableName = "Test Table";

Comments

[DharaPPatel]

I am also in need of using the above function but when i am debugging, the jproperty always comes null even though i have data in jtoken. what do you think is problem there?

[Mahi]

Thanks, you saved my day :)