C# - COUNT(*) Rows in SQL per month

[BSpendlove]

Hi,

 

I have been working on a ticket booking application and I am currently at a standstill. I am trying to display an overall stats of all the tickets that are completed per month. When a ticket is completed the following information is logged in the database that I will need to allow to filter so my manager can see how many tickets per engineer has completed on a monthly basis. (Picture below)

 

Spoiler

 

The two main columns for the filtering will be 'CompletedBy' and 'CompletedOn'. I find that 

"SELECT MONTH(CompletedOn) m, YEAR(CompletedOn) y, COUNT(*) FROM tickets WHERE CompletedBy=@username GROUP BY y, m"

Spoiler

 This is what is returned after running the SQL command. The only test tickets in the booking system set as completed are in June 2016 (Month 6 as shown)

 

Works perfectly in SQL to display what I need but having a bit of trouble trying to get the data from the 'GROUP BY' into something like a variable so I can use on a bar chart that displays the tickets completed per month.

 

            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                string getAmountOfTicketsJan = "SELECT MONTH(CompletedOn) m, YEAR(CompletedOn) y, COUNT(*) FROM tickets WHERE CompletedBy=@username GROUP BY y, m";
                MySqlCommand cmdTicket = new MySqlCommand(getAmountOfTicketsJan, conn);
                cmdTicket.Parameters.AddWithValue("@username", chartUser);
                try
                {
                    conn.Open();
                    int test = Convert.ToInt32(cmdTicket.ExecuteScalar());
                    CommonGlobalVariables.graph_jan = test;
                    conn.Close();
                }
                catch (Exception error)
                {
                    MessageBox.Show("Exception: " + error.Message);
                    return;
                }

                MessageBox.Show("Month 1 = " + CommonGlobalVariables.graph_jan);
            }

The code above works but it grabs the data from the first column (being month). How can I get data from the third column (which is the total) in my case. I can elaborate if I explain very poorly lol.

 

Thanks, Brandon

 

 

[BSpendlove]

A very simple way to achieve what I was trying:

 

            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                string getAmountOfTicketsJan = "SELECT MONTH(CompletedOn) m, YEAR(CompletedOn) y, COUNT(*) FROM tickets WHERE CompletedBy=@username GROUP BY y, m";
                MySqlCommand cmdTicket = new MySqlCommand(getAmountOfTicketsJan, conn);
                cmdTicket.Parameters.AddWithValue("@username", chartUser);
                try
                {
                    conn.Open();
                    MySqlDataReader reader = cmdTicket.ExecuteReader();
                    if(reader.HasRows)
                    {
                        while(reader.Read())
                        {
                            MessageBox.Show("Column 1: " + reader.GetString(0) + "\nColumn 2: " + reader.GetString(1) + "\nColumn 3: " + reader.GetString(2));
                        }
                    }
                    conn.Close();
                }
                catch (Exception error)
                {
                    MessageBox.Show("Exception: " + error.Message);
                    return;
                }

                MessageBox.Show("Month 1 = " + CommonGlobalVariables.graph_jan);
            }

I am just using a DataReader which I have made obvious what reader.GetString(0) does. 0 = column 1, 1 = column 2 etc...

 

If anyone does have a better suggestion, still feel free to suggest!

[Nuluvius]

I really don't know why you are trying to hand code all of this when there's abstraction frameworks available such as the Entity Framework and NHibernate. Why don't you use one of those for your model and then put that implementation detail behind the repository pattern? That would be the better way to go rather than trying to hand roll all of that crap.

[BSpendlove]

5 minutes ago, Nuluvius said:

I really don't know why you are trying to hand code all of this when there's abstraction frameworks available such as the Entity Framework and NHibernate. Why don't you use one of those for your model and then put that implementation detail behind the repository pattern? That would be the better way to go rather than trying to hand roll all of that crap.

Looks very interesting, thank you for the share.