8 Replies - 19485 Views - Last Post: 20 July 2010 - 08:06 AM Rate Topic: -----

#1 Premier2k  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 180
  • Joined: 26-August 08

Looping through a DataSet

Posted 20 July 2010 - 04:08 AM

Hi all,

I have a DataSet that has been returned by another method.
This DataSet returns a list of numbers, I need to loop through this data and find the largest number in the list and on what row it's on.

How do I do this? I have no idea where to start! I tried using a foreach loop, but it tells me I can't use that when working with DataSets.

In case you need it, this is my SQL string that populates the DataSet
SqlCommand dataCommand = new SqlCommand("SELECT COUNT(*) FROM game WHERE leagueyear = @cYear GROUP BY venueid", conndb);


I usually try to provide some code to show I've done something, but I really don't know where to start with this.

Premierk2

Is This A Good Question/Topic? 0
  • +

Replies To: Looping through a DataSet

#2 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,804
  • Joined: 16-October 07

Re: Looping through a DataSet

Posted 20 July 2010 - 04:15 AM

View PostPremier2k, on 20 July 2010 - 05:08 AM, said:

I tried using a foreach loop, but it tells me I can't use that when working with DataSets.


That's the way to go. Make sure you access the rows, though:
DataSet ds = GetData();
foreach(DataRow row in ds.Rows) {
//...


Was This Post Helpful? 0
  • +
  • -

#3 Premier2k  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 180
  • Joined: 26-August 08

Re: Looping through a DataSet

Posted 20 July 2010 - 04:20 AM

View Postbaavgai, on 20 July 2010 - 03:15 AM, said:

View PostPremier2k, on 20 July 2010 - 05:08 AM, said:

I tried using a foreach loop, but it tells me I can't use that when working with DataSets.


That's the way to go. Make sure you access the rows, though:
DataSet ds = GetData();
foreach(DataRow row in ds.Rows) {
//...


I actually get an error with this, it doesn't recognise .Rows??
Was This Post Helpful? 0
  • +
  • -

#4 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: Looping through a DataSet

Posted 20 July 2010 - 04:22 AM

it is actually
foreach(DataRow row in ds.Table[0].Rows) {
....



:P
Was This Post Helpful? 1
  • +
  • -

#5 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,804
  • Joined: 16-October 07

Re: Looping through a DataSet

Posted 20 July 2010 - 07:10 AM

View PostNoBrain, on 20 July 2010 - 05:22 AM, said:

it is actually
foreach(DataRow row in ds.Table[0].Rows) {
....



:P


Doh!

Um:
DataSet ds = GetData();
foreach(DataTable dt in ds.Tables) {
   foreach(DataRow row in dt.Rows) {



Honestly, I work at the DataTable level most of the time. Most of Microsoft's canned code seems to have pushed in that direction as well.
Was This Post Helpful? 0
  • +
  • -

#6 Frinavale  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 203
  • View blog
  • Posts: 776
  • Joined: 03-June 10

Re: Looping through a DataSet

Posted 20 July 2010 - 07:19 AM

Once populated, a DataSet consists of a collection of DataTable objects.

You can't loop through "rows" in a "DataSet" because a DataSet doesn't have any rows: it has Tables.

A DataTable has rows.

So you could loop through all of the Tables in a DataSet, and then add an inner loop to loop through each table's rows:
(Example taken from the MSDN article)
private void PrintRows(DataSet dataSet)
{
    // For each table in the DataSet, print the row values.
    foreach(DataTable table in dataSet.Tables)
    {
        foreach(DataRow row in table.Rows)
        {
            foreach (DataColumn column in table.Columns)
            {
                Console.WriteLine(row[column]);
            }
        }
    }
}

What you probably want to do is retrieve the DataTable that you want to work with from the DataSet and loop through that particular DataTable's rows.

myDataSet.Tables[0] will give you the first DataTable in the DataSet. You should always make sure that the DataSet has DataTables before you try to use a DataTable in a DataSet.

-Frinny

This post has been edited by Frinavale: 20 July 2010 - 07:20 AM

Was This Post Helpful? 0
  • +
  • -

#7 ItIntern3  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 97
  • Joined: 14-July 10

Re: Looping through a DataSet

Posted 20 July 2010 - 07:23 AM

You may also find this MSDN article worth looking at. Depending on the way you wish to solve this probelem the example at the bottom could be relevant.

http://msdn.microsof...datareader.aspx
Was This Post Helpful? 0
  • +
  • -

#8 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: Looping through a DataSet

Posted 20 July 2010 - 07:49 AM

Nicest solution for getting the largest count number from returned dataset would be:

int cYear = 1999;
OleDbCommand cmd = new OleDbCommand("SELECT COUNT(*) FROM game WHERE leagueyear = @cYear GROUP BY venueid", m_conn);
cmd.Parameters.AddWithValue("cYear", cYear);

OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "MyCountsTable");

DataTable dt = ds.Tables["MyCountsTable"];  //Get the table from Data Set
int max = dt.Select().Max(x => x.Field<int>(0));  //Use LINQ to query the data table and get the largest count


This post has been edited by FlashM: 20 July 2010 - 07:53 AM

Was This Post Helpful? 1
  • +
  • -

#9 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: Looping through a DataSet

Posted 20 July 2010 - 08:06 AM

Easier solution would be to update your SQL statement as follows:

SELECT COUNT(*) AS cnt FROM game WHERE leagueyear = @cYear GROUP BY venueid ORDER BY cnt DESC


This way, the largest count would always be in the first row of returned data.

BTW: why do you even need to know what row it's on??? Sounds like your SQL statement should retrieve some other info from DB besides the count...

This post has been edited by FlashM: 20 July 2010 - 08:07 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1