CODEWITHSUNDEEP
c#.netjsondatatable
Hadda
Hadda

Reputation: 47

Convert DataTable to JSON

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?

Upvotes: 0

Views: 10774

Answers (4)

Vishal Kottarathil
Vishal Kottarathil

Reputation: 346

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

Upvotes: 1

Bojan B
Bojan B

Reputation: 2111

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

Upvotes: 0

KMoussa
KMoussa

Reputation: 1578

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

Upvotes: 0

Ted
Ted

Reputation: 4067

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

Upvotes: 0

Related Questions