Convert DataTable to JSON

Question

I have the following code.

public class Game
{
    public string gamenumber { get; set; }
    public string league { get; set; }
    public string date { get; set; }
}

public class getGames
{
    public List<Game> games { get; set; }
}

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

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

My output looks:

{
    "Games": [{
        "gamenumber": "123456",
        "league": "Test League",
        "date": "03.09.2016 15:00:00",
    }]
}

My problem is that I need not only the line 0, I need all the lines of the table. I wanted to solve the problem actually with a for-loop but I always get an error. Does anyone have an idea how I come to the remaining data of the table?

Possible duplicate of [Convert datatable to JSON in C#](https://stackoverflow.com/questions/17398019/convert-datatable-to-json-in-c-sharp) — Evan Carroll, Dec 24 '17 at 05:38

score 1 · Answer 1 · edited Nov 18 '16 at 12:53

This can be done easily by using Javascript serializer:

public string ConvertDataTabletoJSON(DataTable dt) 
{
    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);
    }
    System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
    serializer.Serialize(rows);
 }

If you need more details check here http://www.aspdotnet-suresh.com/2013/05/c-convert-datatable-to-json-string-in-c.html

Bojan B · Accepted Answer · 2016-11-18T11:51:38.167

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();

score 0 · Answer 3 · answered Nov 18 '16 at 11:31

Assuming that dt is a DataTable, you can do a foreach loop like so:

var games = new List<Game>();

foreach(DataRow row in dt.Rows)
{
   games.add(new Game() 
   {
     gamenumber = row["gamenumber"].ToString(),
     league = row["league"].ToString(),
     date= row["date"].ToString()
   });
}

score 0 · Answer 4 · answered Nov 18 '16 at 11:31

You should be able to loop through all the rows in your data table as follows:

dt = mySQL.getAsDataTable("SELECT gamenumber, date, league FROM vSpiele WHERE gamenumber LIKE '" + sgamenumber + "'", null);
var allGames = new getGames { games = new List<Game>() };

foreach(DataRow dr in dt.Rows)
{                
    allGames.games.Add(new Game
    {
        gamenumber = dr["gamenumber"].ToString(),
        league = dr["league"].ToString(),
        date= dr["date"].ToString(),
    });
}


string json = JsonConvert.SerializeObject(allGames);

Convert DataTable to JSON

4 Answers4