Datatable to JSON

Convert datatable to JSON in C#

This code snippet from Convert Datatable to JSON String in C#, VB.NET might help you.
It uses System.Web.Script.Serialization.JavaScriptSerializer to serialize the contents to JSON format:

public string ConvertDataTabletoString()
{
    DataTable dt = new DataTable();
    using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Initial Catalog=master;Integrated Security=true"))
    {
        using (SqlCommand cmd = new SqlCommand("select title=City,lat=latitude,lng=longitude,description from LocationDetails", con))
        {
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
            List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
            Dictionary<string, object> row;
            foreach (DataRow dr in dt.Rows)
            {
                row = new Dictionary<string, object>();
                foreach (DataColumn col in dt.Columns)
                {
                    row.Add(col.ColumnName, dr[col]);
                }
                rows.Add(row);
            }
            return serializer.Serialize(rows);
        }
    }
}

Convert JSON to DataTable

Deserialize your jsonstring to some class

List<User> UserList = JsonConvert.DeserializeObject<List<User>>(jsonString);

Write following extension method to your project

using System.ComponentModel;

public static DataTable ToDataTable<T>(this IList<T> data)
{
    PropertyDescriptorCollection props =
    TypeDescriptor.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;        
}

Call extension method like

UserList.ToDataTable<User>();

How to convert the following data from a DataTable to a JSON object

One way to achieve the result you want is by using Json.Net's LINQ-to-JSON API. Here is how you would do that:

// Create a new JObject by grouping the data table rows by block and then
// selecting the groups into JProperties where the block is the property name
// and the property value is a JArray containing JObjects with the resident IDs 
// and corresponding house numbers in the group making up the properties of those. 

var obj = new JObject(
    table.Rows.Cast<DataRow>()
         .GroupBy(r => r["Block"])
         .Select(g => new JProperty("Block " + g.Key,
             new JArray(g.Select(r =>
                 new JObject(
                     new JProperty(r["ResidentId"].ToString(), r["HouseNo"])
                 )
             ))
         ))
);

// Convert the JObject to a JSON string
var json = obj.ToString();

Working demo: https://dotnetfiddle.net/smb9oW

Convert DataTable to JSON with key per row

This is quite simple with JSON.NET. Just convert your data table into the equivalent dictionary of dictionaries:

public Dictionary<string, Dictionary<string, object>> DatatableToDictionary(DataTable dt, string id)
{
    var cols = dt.Columns.Cast<DataColumn>().Where(c => c.ColumnName != id);
    return dt.Rows.Cast<DataRow>()
             .ToDictionary(r => r[id].ToString(), 
                           r => cols.ToDictionary(c => c.ColumnName, c => r[c.ColumnName]));
}

Then call:

JsonConvert.SerializeObject(DatatableToDictionary(dt, "ID"), Newtonsoft.Json.Formatting.Indented);

Here's the full test:

var dt = new DataTable("MyTable");
dt.Columns.Add("ID");
dt.Columns.Add("Name");
dt.Columns.Add("Active");

dt.LoadDataRow(new[] {"ID1", "John", "True"}, true);
dt.LoadDataRow(new[] {"ID2", "Bill", "False"}, true);

JsonConvert.SerializeObject(DatatableToDictionary(dt, "ID"));

And the result:

{
  "ID1": {
    "Name": "John",
    "Active": "True"
  },
  "ID2": {
    "Name": "Bill",
    "Active": "False"
  }
}

Convert DataTable to JSON array

Try this:

JsonConvert.SerializeObject(dataTable.AsEnumerable().Select(r => r.ItemArray));

How to deserialize JSON object to DataTable

Construct an object model using one of the countless conversion utilities or even the capability to paste JSON as a class that is built into Visual Studio. (Have you even tried to do this?)

    public class Response
    {
        public string uri { get; set; }
        public string action { get; set; }
        public Result result { get; set; }
    }

    public class Result
    {
        public List<string> column_order { get; set; }
        public List<List<string>> rows { get; set; }
    }

    public class Root
    {
        public Response response { get; set; }
    }

Then parse the data. Assuming the JSON is in a string this is trivial.

var response = System.Text.Json.JsonSeralizer.Deserialize<Root>(jsonString).Response;

Then iterate the values of response.Result and populate your data table object. Since you haven't seen fit to provide details of that you can't have details for that part of the answer.

Incidentally, your JSON is invalid, it's missing a trailing brace.

How to create rows data in to DataTable using from json model json api respons flutter

I got the solution as below:
The API is:

Future<List<Employees>> fetchResults() async {
  Uri url = Uri.parse(" Link ");
  var response = await http.get(url);
  var resultsJson = json.decode(response.body).cast<Map<String, dynamic>>();
  List<Employees> emplist = await resultsJson
      .map<Employees>((json) => Employees.fromJson(json))
      .toList();
  return emplist;
}

The Page Example is:

class TestPage extends StatefulWidget {
  const TestPage({Key? key}) : super(key: key);

  @override
  _TestPageState createState() => _TestPageState();
}

class _TestPageState extends State<TestPage> {
  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text('Fetch Data Table Example'),
      ),
      body: SingleChildScrollView(
        child: Column(
          children: [
            FutureBuilder<List<Employees>>(
              initialData: const <Employees>[],
              future: fetchResults(),
              builder: (context, snapshot) {
                if (snapshot.hasError ||
                    snapshot.data == null ||
                    snapshot.connectionState == ConnectionState.waiting) {
                  return const CircularProgressIndicator();
                }

                return DataTable(
                  columns: const [
                    DataColumn(label: Text('ID')),
                    DataColumn(label: Text('Name')),
                    DataColumn(label: Text('Email')),
                  ],
                  rows: List.generate(
                    snapshot.data!.length,
                    (index) {
                      var emp = snapshot.data![index];
                      return DataRow(cells: [
                        DataCell(
                          Text(emp.id.toString()),
                        ),
                        DataCell(
                          Text(emp.name),
                        ),
                        DataCell(
                          Text(emp.email),
                        ),
                      ]);
                    },
                  ).toList(),
                );
              },
            ),
          ],
        ),
      ),
    );
  }}

---

Related Topics