How to count data in DataGrid?

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 1390 Views - Last Post: 15 July 2016 - 05:44 AM Rate Topic: -----

#1 JapanDave  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 366
  • Joined: 01-February 16

How to count data in DataGrid?

Posted 13 July 2016 - 05:24 AM

This is a WPF application.

I am stuck on this issue, I have a requirment to count how many times data appears in a Datagrid_1 that is databound and having that count data show in another datagrid_2 that is also databound. My biggest problem is I don't know how I should even approach this?

I have a datagrid_1 that has 5 columns, I only want to count the data in column 2 for one month at a time.

ie,
This datgrid_1 table is databound to SQL Server Table_1

Attached Image

This datgrid_2 table is databound to SQL Server Table_2

Attached Image

I have limited the data number and columns just to make illustrating the question a little easier.
As you can see I 3 different data in the first table and the second table counts how many times that data appears.
Also, the user can change the data in column_2 via a databound combo_box.

1) Should I I be counting how many times something appears and directly updating the SQL Server Table_2, then refresh the Datagrid holding the count data?
2) Should I be counting from the datagrid (loop through the datagrid values) or directly from the SQL server table_1
3) Or is there a better way to approach this?

Thanks for any advice.

This post has been edited by JapanDave: 13 July 2016 - 05:27 AM


Is This A Good Question/Topic? 0
  • +

Replies To: How to count data in DataGrid?

#2 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 5925
  • View blog
  • Posts: 20,256
  • Joined: 05-May 12

Re: How to count data in DataGrid?

Posted 13 July 2016 - 05:56 AM

View PostJapanDave, on 13 July 2016 - 08:24 AM, said:

3) Or is there a better way to approach this?


Yes. Instead of:
Data_1   Data_2   Data_3
    10       10       10



Do:
Value    Count
Data_1   10
Data_2   10
Data_3   10



So that you can simply do something like the following pseudo code either in LINQ or in SQL:
Table1.Rows
      .GroupBy(r => r["info"])
      .Select(g => new { Value = g.Key, Count = g.Count })



If you do this in SQL, then you can completely throw away your Table2, and replace the table with view, and let the database all the hard work, instead of the desktop machine doing the hardwork.
Was This Post Helpful? 1
  • +
  • -

#3 JapanDave  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 366
  • Joined: 01-February 16

Re: How to count data in DataGrid?

Posted 13 July 2016 - 06:30 AM

Do you mean get rid of the table on the database end of things? If so, that may not be an option.
The only problem with that is, I need the counted data stored in a table. The tables above represent a single staff members timetable and I need to count each days data from column 2. Then that data is stored in the database so at the end of each month , I can output that count data that each staff member did, into a CSV file. There are over 2000 staff members that need each days data recorded.
Was This Post Helpful? 0
  • +
  • -

#4 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 5925
  • View blog
  • Posts: 20,256
  • Joined: 05-May 12

Re: How to count data in DataGrid?

Posted 13 July 2016 - 06:32 AM

So Table_2 has 2000 columns, but only 1 row? Who came up with that database design?

It would have been better if Table_2 had 2 columns and 2000 rows.

Anyway, a table and a view are essentially the same from the resulting data perspective. So as I said, Table_2 can simply be a view of the data in Table_1 where the view just presents the values and counts.
Was This Post Helpful? 1
  • +
  • -

#5 JapanDave  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 366
  • Joined: 01-February 16

Re: How to count data in DataGrid?

Posted 13 July 2016 - 06:50 AM

Sorry explaination really do suck.

The actual Table has 33 columns, [ Staff Number Column, Year Column, Month Column, Then 30 x columns for various unique data ]

Attached Image


One row per staff member, so for one months data there will be 2000 rows.
Was This Post Helpful? 0
  • +
  • -

#6 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 5925
  • View blog
  • Posts: 20,256
  • Joined: 05-May 12

Re: How to count data in DataGrid?

Posted 13 July 2016 - 06:58 AM

Ah, so you need to update a column in Table 2 per staff member. This is where SQL stored procedures really shine. Within the stored procedure, you'll still need to do the grouping and counting, but the last step will be to store the results in the corresponding rows for the staff member in Table 2. I hope that baavgai jumps into this thread because this is right up his alley.
Was This Post Helpful? 1
  • +
  • -

#7 JapanDave  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 366
  • Joined: 01-February 16

Re: How to count data in DataGrid?

Posted 13 July 2016 - 07:04 AM

Yes, that is correct. Are you talking about a stored procedure in the SQL Server database? Or in C# on the application end?
Was This Post Helpful? 0
  • +
  • -

#8 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 5925
  • View blog
  • Posts: 20,256
  • Joined: 05-May 12

Re: How to count data in DataGrid?

Posted 13 July 2016 - 07:12 AM

Ideally a stored procedure on the SQL server. Let the server do all the hard work. This will beat having to ship the data to your desktop, perform the grouping and counts on your machine, and then your machine needs to send the update commands back to the SQL server to update the other table.
Was This Post Helpful? 0
  • +
  • -

#9 JapanDave  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 366
  • Joined: 01-February 16

Re: How to count data in DataGrid?

Posted 13 July 2016 - 06:21 PM

OK, so I was able to make a SQL statement that will count the instances of Data_1.

SELECT Staff_No, Info_Data, COUNT(*) cCount
        FROM Staff_Manager.dbo.Staff_Time_TBL
        WHERE Staff_No = 3201 AND Date_Data BETWEEN '2016/6/15' AND '2016/7/16' AND Info_Data = 'Data_1' 
		GROUP BY Staff_No, Info_Data


Now all I need to figure out is how to make a a stored procedure and have it insert the counted data into the correct column on the different table.
Was This Post Helpful? 0
  • +
  • -

#10 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 5925
  • View blog
  • Posts: 20,256
  • Joined: 05-May 12

Re: How to count data in DataGrid?

Posted 13 July 2016 - 07:18 PM

In your query above, why are you setting a specific Staff_No and Info_Data?

Before you go all out with the stored procedure, try doing an update from the client, but letting the server still do most of the work.

It'll be something like:
UPDATE Table_2
SET Table_2.Info = ComputeTable.cCount
FROM Table_2
INNER JOIN (
    SELECT Staff_No, Info_Data, COUNT(Info_Data) AS cCount
    FROM Staff_Manager.dbo.Staff_Time_TBL
    WHERE Date_Data BETWEEN '2016/6/15' AND '2016/7/16'
    GROUP BY Staff_No, Info_Data
           ) as ComputeTable
ON Table_2.Staff_No = ComputeTable.Staff_No AND
   Table_2.Info_Data = ComputeTable.Info_Data



SQL experts please correct me. Complex SQL is far out of my comfort zone.
Was This Post Helpful? 1
  • +
  • -

#11 JapanDave  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 366
  • Joined: 01-February 16

Re: How to count data in DataGrid?

Posted 13 July 2016 - 08:22 PM

@Skydiver, just to clear things up a little on my behalf. I thought I was going to have to run a SQL Statement on each column in Table_2 and specify what the data is to be counted. And do that 30 times with a different piece of data, Info_Data = 'Data_1' then for the next column to count Info_Data = 'Data_2' ,until all 30 columns were done.


If I have lets say, 3 sets of data that need to be counted.

Data_1, Data_2, Data_3 this data is in entered into the Info_Data Column on Table_1

So now for Table_2, to effectively count this data, I am going to have to name each column the same as the entered data.
Eg, imagine design veiw in SQL Server.
Column No     Column Name
Column No 1 = Staff_No
Column No 2 = Year
Column No 3 = Month
Column No 4 = Data_1
Column No 4 = Data_2
Column No 4 = Data_3


Otherwise ,how does the statement know where to put the counted data into the correct columns on Table_2. Can the SQL statement read the column header and place the correct data in that column?

I hope that makes sense?
Was This Post Helpful? 0
  • +
  • -

#12 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 5925
  • View blog
  • Posts: 20,256
  • Joined: 05-May 12

Re: How to count data in DataGrid?

Posted 13 July 2016 - 08:34 PM

Ah, that makes sense. I misunderstood your post #5. I mistook the 30 columns as some other data columns not really relevant to the discussion. I did not understand that what you meant was that each of those 30 columns corresponded to Data_1, Data_2, Data_3, ... Data_30.

OMG! Is there any chance that the person who designed the database table schemas for SharePoint retired to work for your company?

Hopefully the answer to this StackOverflow question about how to covert rows to columns helps.
Was This Post Helpful? 1
  • +
  • -

#13 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 5925
  • View blog
  • Posts: 20,256
  • Joined: 05-May 12

Re: How to count data in DataGrid?

Posted 13 July 2016 - 08:40 PM

Anyway, this is getting more and more complex. As a non-SQL expert, and actually a SQL hater, to me the code would almost be more maintainable if I just did everything client side and the only SQL that I would ever see is whatever Entity Framework or NHibernate may leak out to me in some trace logs. At least, I would understand the C# code that I wrote instead of the esoteric SQL incantations I would have to use. :) So forget all my suggestions about letting the server do the hardwork. :)
Was This Post Helpful? 0
  • +
  • -

#14 JapanDave  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 366
  • Joined: 01-February 16

Re: How to count data in DataGrid?

Posted 13 July 2016 - 08:46 PM

How embarrassing, I designed the SQL Database. But, in my defence, I have this database working with an Excel as a front end, it is a functional application with Excel in mind as its main design focus. But, I really wanted to do this in C# which I started to learn this year.

I can Change the database to however it needs to be and I should mention I am not getting paid at all for doing this. This a job of love a programming, I enjoyed programming in VBA for many years, and there is not much I can't do these days. But I wanted to learn a language that was a little more with the times, that is why I am learning C# right now.

OK, forget about the stored queries. I am still going to need to figure out how to get that count data into the corresponding columns in the Database table.
Was This Post Helpful? 0
  • +
  • -

#15 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 5925
  • View blog
  • Posts: 20,256
  • Joined: 05-May 12

Re: How to count data in DataGrid?

Posted 13 July 2016 - 08:56 PM

Oops! Foot in mouth, and swallowing the rest of my leg. Sorry!

Anyway when designing database schemas, whenever you start seeing something like:
ID, Column_Value_1, Column_Value_2, Column_Value_3, ... Column_Value_10



and if in particular, most of the rows will have zeros or nulls for the column values, it is better to change the schema to be:
ID, Column_Index, Column_Value



So an old row that looked like:
M29, 100, null, null, -4, null, 6.26, 77, null, 9, null



would be stored as:
M29, 1, 100
M29, 4, -4
M29, 6, 6.26
M29, 7, 77
M29, 9, 9



In a database, rows are cheap, columns are not.
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2