Convert Datatable to Json in C#

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 DataTable to JSON array

Try this:

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

Convert DataTable to JSON

You need to loop through the Rows of your Data Table, since there is no real need for a index, you can use foreach instead of a for-loop:

dt = mySQL.getAsDataTable("SELECT gamenumber, date, league FROM vSpiele 
                           WHERE gamenumber LIKE '" + sgamenumber + "'", null);

var getGames = new getGames(); 
getGames.games = new List<Game>();
foreach(DataRow row in dt.Rows)
{
   getGames.games.Add(new Game(){
      gamenumber = row["gamenumber"].ToString(),
      league = row["league"].ToString(),
      date= row["date"].ToString()
   });
}
string json = JsonConvert.SerializeObject(getSpiele);

Or you can use LINQ if you prefer:

var games = (from DataRow row in dt.Rows
select new Game() {
    gamenumber = row["gamenumber"].ToString(), 
    league = row["league"].ToString(), 
    date = row["date"].ToString()
}).ToList();

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

From DataTable in C# .NET to JSON

Instead of a datatable you should use a datareader. Your code is inefficient and somewhat hard to read - you may want to do something like this:

StringBuilder json = new StringBuilder();

using(SqlConnection cnn = new SqlConnection(your_connection_string)) 
{
    cnn.open();

    using(SqlCommand cmd = new SqlCommand("name_of_stored_procedure", cnn)) 
    {
        cmd.Paramters.AddWithValue("@Param", "value");

        using(SqlDataReader reader = cmd.ExecuteReader()) 
        {
            while(reader.Read()) 
            {
                json.AppendFormat("{{\"name\": \"{0}\"}}", reader["name"]);
            }
        }
    }

    cnn.close();
} 

you can then use json.ToString to get the outpt

Convert data table to json

Something like this should work:

JsonConvert.SerializeObject(new {
   columns = dt1.Columns.Cast<DataColumn>().Select(x => x.ColumnName),
   rows = dt1.AsEnumerable().Select(r => r.ItemArray),
});

I've made a simple fiddle: https://dotnetfiddle.net/T5Gs8l

The result seems to be exactly what you are expecting:

{
  "columns": [
    "firstName",
    "lastName",
    "email",
    "password"
  ],
  "rows": [
    [
      "Alpha",
      "Tango",
      "AlphaTango@domain.com",
      "123"
    ],
    [
      "Charlie",
      "Tango",
      "CharlieTango@domain.com",
      "456"
    ]
  ]
}

Note that you lose the type information, so if your columns might be of varied datatypes, deserializing the object back to the same datatable might be tricky (you may need to infer the column types from the values).

I'd probably store more information on the columns if I was to deserialize it.

---

Related Topics