Timesheets: Retrieve data using SQL, LINQ or class?

Question

My problem is that I want a grid that is populated with a set of times spent on tasks. It needs to have the days of the week at the top (these would preferably be fixed - i.e. Sun, Mon... Sat) and task names down the left column. The contents of the grid will have the individual hours spent for that day on that task.

What would the best approach to take to achieve this? Option one would be to try and put it all in SQL statements in the database. Option two is a collection of LINQ queries that pull raw data from a Tasks and TimeEntries and structure it correctly. Option three is to use LINQ and pull the results into a class (or collection), which is then bound to the control (probably a gridview).

How would you do this?

Answer 1

You seem to imply that the data currently resides in a database. The optimal solution depends on the amount of internal processing of the data and the existing infrastructure of the project.

I would either use a big SQL query to pull all the data together and bind the ResultSet directy to the Grid (little to no processing or infrastructure) or use LINQ to populate a kind of TimeSheetModel class, using existing infrastructure and enabling further processing.

Answer 2

I'll take a rough hack at it not knowing your structure but guessing you have a task table and a tasktime table that stores the actual times and dates that are charged to each task. This isn't tested 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) as Monday from tasktable t join tasktime tt on t.taskid = tt.taskid where tt.taskdate >= @begindate and tt.taskdate<= @enddate

The where clause is important because you probably only want to display a week at time (usually the current week) on your grid. This also assumes you have properly stored the ddates of your hours charged as datetime data type. (if you haven't fix that now - you will thank me later.) The variables would be submitted from the user interface in some fashion. I personally would do that as a stored proc. I left Tues through Saturday for you to do.

Answer 3

You can use LinqToSql to fetch the rows into memory, and then LinqToObjects to aggregate those rows into your table format (it's a pivoting aggregation).

This post shows a linq pivot query. In your case it will work quite well, since you know the columns that you want to produce at design time. http://stackoverflow.com/questions/183585/sql-command-to-linq-pivoting#184032

I recommend using 0 anonymous classes. There should be (at minimum) a class that represents a row in the database, and a class that represents the values of a row in the grid.

---

And because I can say whatever I want here, I wanted to comment on HLGEM's SUM(CASE) technique. I've used this before on reports where I -had- to write in SQL only. It works very well... if you want to write in SQL.