ADO,Net (conversion of file from existed json to table form)

Follw up as below steps:

Deserialize your jsonstring to some class

List UserList = JsonConvert.DeserializeObject>(jsonString);

Write following extension method to your project

public static DataTable ToDataTable(this IList data)
{
PropertyDescriptorCollection props =
TpeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
for(int i = 0 ; i < props.Count ; i++)
{
PropertyDescriptor prop = props[i];
table.Columns.Add(prop.Name, prop.PropertyType);
}
object[] values = new object[props.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
return table;
}

or try below one

private DataTable ConvertJSONToDataTable(string jsonString)
{
DataTable dt = new DataTable();
//strip out bad characters
string[] jsonParts = Regex.Split(jsonString.Replace("[", "").Replace("]", ""),"},{");
//hold column names
List<string> dtColumns = new List<string>();
//get columns
foreach (string jp in jsonParts)
{
//only loop thru once to get column names
string[] propData = Regex.Split(jp.Replace("{", "").Replace("}", ""),",");
foreach (string rowData in propData)
{
try
{
int idx = rowData.IndexOf(":");
string n = rowData.Substring(0, idx - 1);
string v = rowData.Substring(idx + 1);
if (!dtColumns.Contains(n))
{
dtColumns.Add(n.Replace("\"", ""));
}
}
catch (Exception ex)
{
throw new Exception(string.Format("Error Parsing Column Name : {0}", rowData));
}
}
break; // TODO: might not be correct. Was : Exit For
}
//build dt
foreach (string c in dtColumns)
{
dt.Columns.Add(c);
}
//get table data
foreach (string jp in jsonParts)
{
string[] propData = Regex.Split(jp.Replace("{", "").Replace("}", ""), ",");
DataRow nr = dt.NewRow();
foreach (string rowData in propData)
{
try
{
int idx = rowData.IndexOf(":");
string n = rowData.Substring(0, idx - 1).Replace("\"", "");
string v = rowData.Substring(idx + 1).Replace("\"", "");
nr[n] = v;
}
catch (Exception ex)
{
continue;
}
}
dt.Rows.Add(nr);
}
return dt;
}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar5y ago
Accepted answer
Follw up as below steps:

Deserialize your jsonstring to some class
List UserList = JsonConvert.DeserializeObject>(jsonString);
Write following extension method to your project
public static DataTable ToDataTable(this IList data)
{
    PropertyDescriptorCollection props =
    TpeDescriptor.GetProperties(typeof(T));
    DataTable table = new DataTable();
    for(int i = 0 ; i < props.Count ; i++)
    {
        PropertyDescriptor prop = props[i];
        table.Columns.Add(prop.Name, prop.PropertyType);
    }
    object[] values = new object[props.Count];
    foreach (T item in data)
    {
        for (int i = 0; i < values.Length; i++)
        {
            values[i] = props[i].GetValue(item);
        }
        table.Rows.Add(values);
    }
    return table;
}
or try below one
private DataTable ConvertJSONToDataTable(string jsonString)
    {
        DataTable dt = new DataTable();
        //strip out bad characters
        string[] jsonParts = Regex.Split(jsonString.Replace("[", "").Replace("]", ""),"},{");

        //hold column names
        List<string> dtColumns = new List<string>();

        //get columns
        foreach (string jp in jsonParts)
        {
            //only loop thru once to get column names
            string[] propData = Regex.Split(jp.Replace("{", "").Replace("}", ""),",");
            foreach (string rowData in propData)
            {
                try
                {
                    int idx = rowData.IndexOf(":");
                    string n = rowData.Substring(0, idx - 1);
                    string v = rowData.Substring(idx + 1);
                    if (!dtColumns.Contains(n))
                    {
                        dtColumns.Add(n.Replace("\"", ""));
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(string.Format("Error Parsing Column Name : {0}", rowData));
                }

            }
            break; // TODO: might not be correct. Was : Exit For
        }

        //build dt
        foreach (string c in dtColumns)
        {
            dt.Columns.Add(c);
        }
        //get table data
        foreach (string jp in jsonParts)
        {
            string[] propData = Regex.Split(jp.Replace("{", "").Replace("}", ""), ",");
            DataRow nr = dt.NewRow();
            foreach (string rowData in propData)
            {
                try
                {
                    int idx = rowData.IndexOf(":");
                    string n = rowData.Substring(0, idx - 1).Replace("\"", "");
                    string v = rowData.Substring(idx + 1).Replace("\"", "");
                    nr[n] = v;
                }
                catch (Exception ex)
                {
                    continue;
                }

            }
            dt.Rows.Add(nr);
        }
        return dt;
    }
Vinay Rayaprol5y ago
Thankyou Rajanikant