Help needed with C# logic

Question

Hi, I have table with below fields,

table Shows,
  Id   Name   Time
   1     A    7/28/2010 11:15:00 AM
   2     B    7/29/2010 8:50:00 AM
   3     C    7/29/2010 8:55:00 AM

I have an object Show that has data from all the fields. Now I want to have a UI, that displays count of shows on an hourly basis for all days..

Date 7/28/2010
  Hours Count
  11-12   1

Date 7/29/2010
  Hours Count
  8-9   2

I have no idea how I shall do it in C#(the logic for it.)Also, is there something like a explode funtion that we have in php, in C#. Because my database field has value 7/29/2010 8:55:00 AM and I want to break date and time. Can anybody help with logic to build the above UI?

As I want to display all hours and their counts for each date, will I have to use a listbox for dates and in that another listbox, with all hours and counts in that date?Can you show me how to do it?

Answer 1

Hey,

Most database has conversion capabilities. Like SQL Server for instance, you can convert a date and format it so that it appears as 7/29/2010 in the convert keyword. Additionally, you can use the datepart function in SQL to extract the first hour (you'd have to calculate the second hour by adding one unless the hour is 12, then return 1, which can be done with a function).

So group by the date, then by hour range, and then count the number of occurrences... is the logic.

HTH.

Answer 2

As for your DateTime issue, since 7/29/2010 8:55:00 AM is a valid DateTime, you can just use parse

System.DateTime dateTime;
System.DateTime.TryParse("7/29/2010 8:55:00 AM", out dateTime);
var date = dateTime.Date;
var time = dateTime.TimeOfDay;

Various other properties and methods are available. If you just want strings you can use

var timeString = dateTime.ToString("hh:mm");

In C#, you really rarely need to do string manipulation if you are using 'normal' types of data like dates, numbers, times, etc

Answer 3

Your SQL statement could be used to break up your data into the groupings. For example, here's the SQL statement I would use (The weird parsing you see in the first column is just my way of dropping the time from a datetime object):

SELECT 
  CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, DateTimeColumnName))) as [Date],
  DATEPART(Hour, DateTimeColumnName) as [Hour], 
  Count(*) as [Count]
FROM Shows
GROUP BY 
  FLOOR(CONVERT(FLOAT, DateTimeColumnName)), 
  DATEPART(Hour, DateTimeColumnName)

This would return you a dataset that contains the Date, an Hour, and how many records meet that criteria. Something like:

[Date]               [Hour]       [Count]
7/28/2010 00:00:00   11           1
7/29/2010 00:00:00   8            2

Just take this data and format it however you want in your UI.

In regards to your other question, the DateTime object contains properties for the different time parts, so just cast your datetime data into a DateTime object like Chad suggested.

Answer 4

Assuming you're able to use LINQ, then you can do all the grouping and ordering in the C# fairly easily. (I'm assuming just a list of DateTime objects rather than full Show objects -- it would appear you can get the DateTime for each Show with a simple LINQ Select statement.)

var shows = new[]
            {
                new DateTime(2010, 7, 28, 11, 15, 0),
                new DateTime(2010, 7, 29, 8, 50, 0),
                new DateTime(2010, 7, 29, 8, 55, 0)
            };

var dates = shows.GroupBy(d => d.Date).OrderBy(d => d.Key);
foreach (var date in dates)
{
    Console.WriteLine("Date {0}", date.Key.ToShortDateString());
    var hours = date.GroupBy(d => d.Hour).OrderBy(d => d.Key);
    Console.WriteLine("\tHours\tCount");
    foreach (var hour in hours)
        Console.WriteLine("\t{0}-{1}\t{2}", hour.Key, (hour.Key+1)%24, hour.Count());
}

This provides the output:

Date 7/28/2010
        Hours   Count
        11-12   1
Date 7/29/2010
        Hours   Count
        8-9     2

Note that this is just a simple Console Application example. You have not specified what GUI technology you are using (WinForms, WPF), so I'll leave it as an exercise to take the grouping results and apply them in a GUI.

Edit: Here is an example format in XAML using nested ItemsControls with DataBinding to a LINQ statement.

<ItemsControl Name="ShowDates" ItemsSource="{Binding}">
<ItemsControl.ItemTemplate>
    <DataTemplate>
        <HeaderedContentControl HeaderStringFormat="Date: {0}">
            <HeaderedContentControl.Header>
                <StackPanel>
                    <TextBlock Text="{Binding Path=DateString, StringFormat='Date: {0}'}" />
                    <TextBlock Margin="20,1,1,1" Text="Hour : Shows"/>
                </StackPanel>
            </HeaderedContentControl.Header>
            <ItemsControl Name="HoursList" Margin="20,1,1,1" ItemsSource="{Binding Path=Hours}">
                <ItemsControl.ItemTemplate>
                    <DataTemplate>
                        <TextBlock>
                            <TextBlock.Text>
                                <MultiBinding StringFormat="{}{0}-{1} : {2}">
                                    <MultiBinding.Bindings>
                                        <Binding Path="HourStart" /> 
                                        <Binding Path="HourEnd" /> 
                                        <Binding Path="Count" /> 
                                    </MultiBinding.Bindings>
                                </MultiBinding>
                            </TextBlock.Text>
                        </TextBlock>
                    </DataTemplate>
                </ItemsControl.ItemTemplate>
            </ItemsControl>
        </HeaderedContentControl>
    </DataTemplate>
</ItemsControl.ItemTemplate>
</ItemsControl>

This uses a modified version of my original LINQ statement to make it easier for the DataBinding to be wired up. This code is in the constructor of my test Window.

    var result =
    shows
    .GroupBy(d => d.Date)
    .Select(gp => new{
        Date = gp.Key,
        DateString = gp.Key.ToShortDateString(),
        Hours = gp.GroupBy(d => d.Hour)
                    .Select(hgp => new {
                        HourStart = hgp.Key,
                        HourEnd = (hgp.Key + 1) % 24,
                        Count = hgp.Count()
                    })
                    .OrderBy(h => h.HourStart)
    });
ShowDates.DataContext = result;