Schedules: getting data using SQL, LINQ, or a class?
My problem is that I want the grid to be filled with many times spent on tasks. It should have the days of the week at the top (preferably fixed - ie Sun, Mon ... Sat) and the task names in the left column. The contents of this grid will contain the individual hours spent on that day.
What would be the best approach to achieve this? The first option is to try and put it all into SQL queries in the database. The second option is a set of LINQ queries that retrieve raw data from Jobs and TimeEntries and structure it correctly. The third option is to use LINQ and output the results to a class (or collection), which is then bound to a control (perhaps a gridview).
How do you do it?
source to share
I'll hack it roughly without knowing your structure, but guessing that you have a task table and a task table that stores the actual times and dates that are charged for each task. This is untested, but:
select t.taskname, sum (case when datepart (d, tt.taskdate) = 1, tt.taskhours) else 0 end) as Sunday, sum (case when datepart (d, tt.taskdate) = 2, t. taskhours) else 0 end) on Monday from the task table t join tasktime tt to t.taskid = tt.taskid where tt.taskdate> = @begindate and tt.taskdate <= @enddate
The where clause is important because you probably only want to display the week (usually the current week) in your grid. This also assumes that you have correctly stored the dates of your watch, charged as the datetime datatype. (if you don't fix this now - you will thank me later.) Variables will be passed from the UI in some way. I will personally do this as a stored procedure. I went from Saturday to Saturday so you can do.
source to share
You seem to be implying that the data is currently in the database. The optimal solution depends on the amount of internal data processing and the existing infrastructure of the project.
I would either use a large SQL query to put all the data together and bind the directive ResultSet
to a grid (with little or no processing or infrastructure), or use LINQ to populate the class TimeSheetModel
using existing frameworks and provide further processing.
source to share
You can use LinqToSql to fetch rows into memory and then LinqToObjects to concatenate those rows into a table format (this is a pivot aggregation).
This post shows linq summary query. This will work very well in your case, since you know the columns you want to create at design time. SQL Command for LINQ (Pivot)
I recommend using 0 anonymous classes. There should be (at least) a class that represents the row in the database and a class that represents the values of the row in the grid.
And since I can say whatever I want here, I would like to comment on the HLGEM SUM (CASE) technique. I've used this before in reports where I -had- was only writing in SQL. It works really well ... if you want to write in SQL.
source to share