iterating through a dataset  
Author Message
Morrissey99





PostPosted: Visual C# General, iterating through a dataset Top

Hello all.  I want to iterate through a dataset and pretty much have the logic down.  I'm using a foreach loop like so:

foreach(DataRow row in ds.Tables[0].Rows)

{

//logic here

}

The table in the dataset has two columns.  The first column is a count (int) and the second column contains a timestamp (DateTime).  What I want to do is sum up all the counts in increments of 5 minutes.  The data provides me with increments of 1 minute timestamps, sometimes multiple records for the same timestamp.  In other words, my data would look something like this:

timestamp   count

1:53             1000

1:54             1000

1:54              500

1:54              1000

1:55               200

and so on...

So essentially, I just need some help in the logic involved to go through my dataset and sum up all the counts in increments of 5 minutes.  The timestamps will always start at the beginning of the day and end at the end of the day.  So I'll always start from midnight.  I would change the stored procedure in a way that this can be done on the backend, but I wanna learn more about C# and how to do it programatically.



Visual C#18  
 
 
Keith Rome





PostPosted: Visual C# General, iterating through a dataset Top

One approach would be to accumulate the values into a hashtable (probably a dozen other solutions out there as well):

Dictionary<int, int> statistics = new Dictionary<int, int>();

for (int minute = 0; minute < (24 * 60); minute += 5)

statistics.Add(minute, 0);

foreach (DataRow row in ds.Tables[0].Rows)

{

DateTime Timestamp = (DateTime)row["timestamp"];

int minutes = Convert.ToInt32(Timestamp.TimeOfDay.TotalMinutes);

int bucket = minutes - (minutes % 5);

int Count = (int)row["count"];

statistics[bucket] += Count;

}

StringBuilder sb = new StringBuilder();

foreach (int bucket in statistics.Keys)

sb.AppendFormat("{0} : {1}\n", (new TimeSpan(0, bucket, 0)).ToString(), statistics[bucket]);

MessageBox.Show(sb.ToString());

HTH



 
 
Morrissey99





PostPosted: Visual C# General, iterating through a dataset Top

Thanks for the reply Keith.  Using a hashtable as a solution never crossed my mind, but I like the idea.  However, there's one question that arises from using a hashtable.  I see that you're simply getting the total minutes and using it as a key and the corresponding count as the value.  Well, that works, but what if i want to keep the timestamp format

To clarify, I'm hoping to use this data and plot the points into a graph.  Lets say that between 1:53 am and 1:58 am, my total count is 5000.  Well, in my graph,  I want the  X-axis to display 1:58:00 and the corresponding Y-axis plot to be the total count (in this example, 5000).

Would this even be possible


 
 
James Curran





PostPosted: Visual C# General, iterating through a dataset Top

Well, I'm not even sure if changing the query is even an option, but I was bored, and wanted to play with some SQL for a while, so here goes:

SELECT DATEADD(mi, - (DATEPART(mi, CAST(timestamp AS smalldatetime)) % 5), CAST(timestamp AS smalldatetime)) AS TimeFrom, SUM(Count) AS Total
FROM Table2
GROUP BY DATEADD(mi, - (DATEPART(mi, CAST(timestamp AS smalldatetime)) % 5), CAST(timestamp AS smalldatetime))
ORDER BY TimeFrom

That will return a table with the values summed as you want.

To translate that:

"CAST(timestamp AS smalldatetime)) " basically means "timestamp without seconds"

"(DATEPART(mi, timestamp ) " is the minutes portion of the timestamp.

"(DATEPART(mi, timestamp ) % 5 " is the minutes since the start of the current 5 minute period.

"DATEADD(mi, - (DATEPART(mi, timestamp) % 5), timestamp)" subtracts the minutes since the start of the period from the current time, leaving the start of the period.