Getting group by and count data from dataset

[LabProARW]

My code below gathers the total number of samples from tbl_Sample_Login_table between a set of dates. That's nice for a total of all samles - however what I need to do is group by ProductName - capture that product name - and get the total count of that product... continuing on for all different products. This is for making a summary report for all products within the date range. I have been unable to find in searches how to get to my end goal of the report. I'm using VS2019.

     using (OleDbConnection dbConnection = new OleDbConnection(GlobalVar.networkConnectionString))
            {
                // CREATE THE COMMAND AND PARAMETER OBJECTS.
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
                OleDbCommand cmd;
                cmd = new OleDbCommand("SELECT ProductName FROM tbl_Sample_Login_table WHERE LoginDate BETWEEN @startingDate AND @endingDate ORDER BY ProductName", dbConnection);
                cmd.Parameters.AddWithValue("@startingDate", startingDate);
                cmd.Parameters.AddWithValue("@endingDate", endingDate);
                dbConnection.Open();
                dataAdapter.SelectCommand = cmd;
                DataSet ds = new DataSet();
                dataAdapter.Fill(ds, "tbl_Sample_Login_table");
                int recordCount;
                recordCount = ds.Tables[0].Rows.Count;

Any constructive guidance?
Thanks.

 

[Skydiver]

I noticed you are using OLEDB above. What kind of database are you using behind that OLEDB connection, and does it support grouping?

If it does support grouping, in general, you want to let the database do the hard work of grouping and computing the sub-totals for each group. Let it return those results to you because it can do it more efficiently, and also less data will be sent across the wire. You'll need to do searches on how to "grouping with subtotals using SQL"

If it does not support grouping, then you'll need to do the hard work of grouping and computing the sub-totals for each group. Before the introduction of LINQ and LINQ extension methods you would have to roll your own code, but this gets much easier using LINQ's GroupBy() and Sum() extension methods. The downside is that ends to be less efficient because all the data first needs to be sent from the database back to your C# code, and then your code needs to go do the processing the rows using those extension methods. Grouping costs memory and CPU time.

 

[LabProARW]

I noticed you are using OLEDB above. What kind of database are you using behind that OLEDB connection, and does it support grouping?

If it does support grouping, in general, you want to let the database do the hard work of grouping and computing the sub-totals for each group. Let it return those results to you because it can do it more efficiently, and also less data will be sent across the wire. You'll need to do searches on how to "grouping with subtotals using SQL"

If it does not support grouping, then you'll need to do the hard work of grouping and computing the sub-totals for each group. Before the introduction of LINQ and LINQ extension methods you would have to roll your own code, but this gets much easier using LINQ's GroupBy() and Sum() extension methods. The downside is that ends to be less efficient because all the data first needs to be sent from the database back to your C# code, and then your code needs to go do the processing the rows using those extension methods. Grouping costs memory and CPU time.

I am using an ACCESS back end. I cannot get any tool for report viewing to show on my tool bar in my MS VS2019 - which I thought would be an easier way to make a printable report. No report tools at all. I downloaded a couple of Report related Tools and think they installed, but nothing but an 'axRDPViewer' is possible.

 

[Skydiver]

Looks like MS Access supports GROUP BY:

GROUP BY clause (Microsoft Access SQL)

Office VBA reference topic

docs.microsoft.com