Displaying yearly totals in ListView control

Question

I am testing a sample application, which adds a number of individuals i.e., "people" and records a measure of time for each month of the year for each of these people i.e., "minTime" a decimal value, and "auxTime" a decimal value.

In my code, I iterate through a SQL database table in an effort to total the decimal values of each individual's hours. "person" is an nvarchar, and "yearTotal" is a decimal.

I want to display the totals in a ListView screen.

For testing purposes I have added 2-people. Each individual has a measure of time entered for each of the 12-months.

There are 2-problems I'm running into.

1) Attempting to add "yearTotal" to the ListView control, ends up displaying each month of the 12-months worth of data for each person.

For example: John Smith 50-hours, John Smith 75-hours, etc, rather than a total for John Smith, and this occurs for all the persons.

2) And as soon as "yearTotal" reaches the correct total for the first individual, it holds onto this value and increments it for the next person, giving a false value for all subsequent persons.

I need to know how (and where) to add each person's yearly total in ONE Entry to the ListView control, rather than multiple entries of the same person.

For example: John Smith 840-hours, Sally Jones 750-hours.

Here is a link to download a "testDB" with a single table named "timeRecord". Please bear in mind that I need a way to completely Total the values for each individual person, totaling their values from January through December and displaying that person's grand total in a ListView control.

When I've attempted to use SUM on the minTime and auxTime columns (with an alias) SQL still displays the individual month totals but not a single Grand Total for each person. sample database

Answer 1

I will not explain It but just write finished code.

class String_String
{
    public _string1 { get; set; }
    public _string2 { get; set; }
}


try
{
    using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TimeKeeping.mdf;Integrated Security=True;User Instance=False"))
    {
        conn.Open();

        using (SqlCommand cmd = new SqlCommand(
        "SELECT people, SUM(minTime) FROM timeTable ASC GROUP BY PEOPLE ORDER BY people", conn))
        {
            SqlDataReader dr = cmd.ExecuteReader();

            List<String_String> list = new List<String_String>();

            while(dr.Read())
            {
                list.Add(new List<String_String> { _string1 = dr[0].ToString(), _string2 = dr[1].ToString() });
            }


            lstVwPeopleTime.DataSource = list;
            lstVwPeopleTime.DisplayMember = "_string2";
            lstVwPeopleTime.ValueMember = "_string1";
        }
    }
}
catch (SqlException ex) { MessageBox.Show(ex.Message); }
catch (System.Exception ex) { MessageBox.Show(ex.Message); }

Code is not tested so it may give error.

Answer 2

I finally figured out how to get around the issues I was experiencing. I created a separate method, i.e., private void GetTotals(string name) wherein I pass the name of each person. I queried the person, minTime, auxTime from tablename where person = @person. Then inside a SQL datareader loop, I increment the minTime and auxTime values and pass those values to the ListViewItem for display in my ListView control.

while (reader.Read())
{
    yearTotal += Convert.ToDecimal(reader["minTime"].ToString(), CultureInfo.InvariantCulture) + 0.00M;
    auxTotal += Convert.ToDecimal(reader["auxTime"].ToString(), CultureInfo.InvariantCulture) + 0.00M;
}
ListViewItem listItem = new ListViewItem(name);
listItem.SubItems.Add(yearTotal.ToString());
listItem.SubItems.Add(auxTotal.ToString());
lstVwPeopleTime.Items.Add(listItem);
name = "";
return;

In my former method, I merely create and instantiate an ArrayList, query all the person's names in table and add them to the ArrayList. Then inside a foreach loop, I pass each person's name to the GetTotals method. This method successfully totals and displays each person's 12-month value in the ListView control.