Introduction:

I was working on a project where I needed to display some reports based on the employee's daily work. The report should consist of an employee check in time, check out time, no of hours worked for each day and also the total hours worked. The task can be accomplished in number of ways which includes using the Crystal Reports or SQL SERVER Reporting Services but I thought I should use Nested GridView to achieve the desired result. In this article I will demonstrate how to use the Nested GridView approach to create subtotals and grand total.

Database Design:

Let’s first check out the database design. The database consists of a single table called “Reports”. The schema of the Reports table is given below:

ReportID (int) (PK): This is the primary key of the table.

HANumber (varchar(50): This uniquely identifies the employee.

CheckInDate (DateTime): The date and time employee checked in for work.

CheckOutDate (DateTime): The date and time employee checked out for work.

As, you can see the database schema is very simple. Now, let’s create some methods to get the data out of the “Reports” table.

Creating the Entity Classes:

There are two entity classes used in this application namely, Report and Date. The Report class holds all the information about the report which includes the “ReportID”, “CheckInDate”,”CheckOutDate”. The Date class holds the “ReportDate” and the collection of reports. The idea is to get all the unique dates from the database and store it in the Date collection. Then iterate over the collection and retrieve all the reports associated with that date.

Implementing the GetDates Method:

As, I stated before the GetDates method retrieves all the unique dates from the database. This retrieval should be dependent on the ID of the user which, in this case is the HANumber.

public static List<Date> GetDates(string haNumber)

    {

        string connectionString = "Server=localhost;Database=CWTimeSheetDB;Trusted_Connection=true";

        string selectCommand = "SELECT DISTINCT CAST( CONVERT(varchar(10),CheckInDate,110) AS DateTime) AS [ReportDate]  FROM Reports WHERE HANumber = @HANumber ";

        List<Date> dates = new List<Date>();

        using (SqlConnection myConnection = new SqlConnection(connectionString))

        {

          &nb