Editable Pivot transformation using LINQ to SQL RRS feed

  • Question

  • hi guys!

    as explained in my other thread (over here i have to write a spreadsheet - like UI for a relational SQL table, where employees have to report month wise how many hours they've spent on a specific project on each day of a given month (x-axis being the days of the month, y-axis being the individual projects).

    my table has user-ID, project-ID, date and hours fields. i have to bind data retrieved from that table to a DataGrid that has a column for every day of a given month (ie. 31 columns for March).

    now what i want to do is write a class, based on the LINQ-to-SQL class generated by the designer, that exposes every single day of a month as property, ie. projectrecord._1, projectrecord._2, projectrecord._3, where projectrecord would be a set of rows from the db with the date beeing >= #2009-04-01# and <= #2009-04-30#, as an example. this class doesn't have to have dynamic properties, if a month has less then 31 days then i just set the unused days/properties to nothing.

    when the setter of projectrecord._1 is called it should create a new element in the underlying LINQ-to-SQL class which has the date field set to #xx/01/yyyy#, if the setter of ._2 is called #xx/02/yyyy#, and so on. the getter should retrieve this values correspondingly.

    the reason for all of this is that i want to bind that class to my WPF DataGrid, which, to achieve the spreadsheet-like design, has a column for each day of the current month.

    now i actually haven't got the slightest clue on how to start, how to write this class that is based on the LINQ-to-SQL class the designer already made for me.

    i'd really appreciate any help i can get as - in theory - i should have a working prototype by the end of next week :(

    i'm not really sure if my approach makes sense at all. i would be glad if somebody knew a better method to handle this. the main problem is the way that relational data is going to be displayed here, that is as a pivot table. the problem is that the layout of the grid is not a subject of discussion.

    what i already tried is to pull the data from the sql server by using the PIVOT T-SQL function, which works but (of course) renders the datagrid read-only and requires the entire editing-system to be manually implemented. another approach would be a view with an INSTEAD OF trigger for updates and inserts but i'd really like to have the logic in my code and not in the DB-backend.

    thanks in advance,
    Thursday, March 19, 2009 3:24 PM