Hello

I am not sure if LLBLGen will be useful for this problem. I have an SQL Server view which collates time entered against a timesheet which has a date. Basically, the view sums all the time entered, grouped by Job. For reporting purposes, I would like to be able to specify a date range, but I am not sure how to go about that, since I can't include TimesheetDate in my view without applying a "GROUPBY" clause to it.

In the view, I can add to the SQL to state "WHERE Timesheet.TimesheetDate < 1 / 1/ 2007" for example, but, because LLBLGen will only let me filter by named columns, I can't load the filter dynamically in my program.

Does anyone have any ideas about how to tackle this problem?

Cheers

Mike

Your questions seems un-clear to me, I can't understand the scenario. Would you please elaborate more? and post the SQL Query of the View and any SQL statement that you want to execute.

You can add sql views as entities. Once an entity you can map relations, columns, etc. This would allow you to link your [View]Entity with [Table]Entities and create filters.

You'll want to mark the Entity/Fields of the view as readonly. And/Or create entity validators to throw an error so you don't try to change the entity and persist to the db.

Thanks

My SQL code is as follows:

SELECT   dbo.TimeInput.TimeInputGroupID, dbo.TimeInput.JobID, dbo.TimeInput.PositionID, dbo.TimeInput.ChargeoutRate, SUM(dbo.TimeInput.Units) AS Hours, dbo.TimeInputGroup.GroupDescription
FROM         dbo.TimeInput INNER JOIN dbo.Timesheet ON dbo.TimeInput.TimesheetID = dbo.Timesheet.TimesheetID 
WHERE    (dbo.Timesheet.TimesheetDate > CONVERT(DATETIME, '2006-12-31', 102))
GROUP BY dbo.TimeInput.TimeInputGroupID, dbo.TimeInput.JobID,  dbo.TimeInput.PositionID, dbo.TimeInput.ChargeoutRate

Basically, I would like to change that WHERE clause into a dynamic query, so that the user can specify a date. If I include TimesheetDate as an output, I need to add a GROUP BY clause to it, which messes up the collation.

Is there any way to achieve this in LLBL? Can I use actual SQL statements anywhere to get what I want?

Regards

Mike

If you don't have to use a view then you should be able to represent this query as a dynamic list that would behave as you would like. As a view I don't think would be able to make this happen. A stored procedure may also be an option, but I usually like to keep most logic in the code and use the DB as a datastore only.

You will have to include the GroupDescription in the aggregate. It didn't appear to be in your example and I added a join to the TimeGroup table.

Give something like this a shot and let us know.

            DataAccessAdapter adapter = new DataAccessAdapter();
            ResultsetFields fields = new ResultsetFields(6);
            fields.DefineField(TimeInputFields.TimeInputGroupID, 0);
            fields.DefineField(TimeInputFields.JobID, 1);
            fields.DefineField(TimeInputFields.PositionID, 2);
            fields.DefineField(TimeInputFields.ChargeoutRate, 3);
            fields.DefineField(TimeInputFields.Units, 4, "Hours", AggregateFunction.Sum);
            fields.DefineField(TimeGroupFields.GroupDescription, 5);
            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(TimeInputEntity.Relations.TimeInputGroupEntityUsingTimeInputGroupID);
            bucket.Relations.Add(TimeInputEntity.Relations.TimesheetEntityusingTimesheetID);

            bucket.PredicateExpression.Add(TimesheetFields.TimesheetDate > DateTime.Now);

            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[0]);
            groupByClause.Add(fields[1]);
            groupByClause.Add(fields[2]);
            groupByClause.Add(fields[3]);
            groupByClause.Add(fields[5]);
            DataTable dynamicList = new DataTable();
            adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true, groupByClause);

Thanks for the suggestion above.

I have decided to go with a stored procedure which I then map onto the typed view. It is a bit more of a maintenance problem (I have to keep the view and SP in sync) but it does make it easier with GUI databinding.

Thanks again

Mike

I have decided to go with a stored procedure which I then map onto the typed view.

How would you do that? You can use Projection to project the results of the SP into an EntityCollection for instance.

But it's much easier to use a dynamicList instead, as suggested before.

I have created a typed view with the fields that I wanted, which I then bind to the grid that I am using. In my program, I call the stored procedure which returns exactly the same view, but has a parameter with the timesheet date. I then loop through each of the rows returned from the stored procedure and copy them to the typed view (which is just a datatable underneath).

A bit clunky, but it works, and I only call it once or twice a day at most.