Hi,
I am currently trying to filter the grid which is the code that i am trying to use.
I have 2 table that related.
Instance and manager table.
Instance contains
-InstanceId(PK)
-StartDate
-EndDate
...
the manager contains
-RecordId(PK)
-InstanceId(FK)
-UserId
the following code will display all the instance in the instance table with no issues. But now, i need to filter if the user (current login user) is exist in manager table for the specific instance.
How can i achieve this in LLBLgen. Please advise. Thank you
public DataTable GetInstanceProgramByUserId(int userId, int pageNumber, int pageSize, out int totalRecords)
{
using (DataAccessAdapter anAdaptor = new DataAccessAdapter())
{
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.AddWithAnd(ProgramInstanceFields.SchoolProgramAdminUserId == userId);
ResultsetFields fieldCount = new ResultsetFields(1);
fieldCount.DefineField(ProgramInstanceFields.ProgramInstanceId, 0);
fieldCount[0].AggregateFunctionToApply = AggregateFunction.CountDistinct;
IDataReader reader = anAdaptor.FetchDataReader(fieldCount, filter, CommandBehavior.SingleRow, 0, true);
int totalCount = 0;
while (reader.Read())
{
if (reader == null)
{
totalCount = Convert.ToInt32(reader[Constants.ID].ToString());
}
}
totalRecords = totalCount;
reader.Close();
int fieldNo = 0;
ResultsetFields fields = new ResultsetFields(10);
fields.DefineField(ProgramInstanceFields.ProgramInstanceId, fieldNo++, ProgramInstanceFields.ProgramInstanceId.Name);
fields.DefineField(ProgramInstanceFields.InstanceStart, fieldNo++, ProgramInstanceFields.InstanceStart.Name);
fields.DefineField(ProgramInstanceFields.InstanceEnd, fieldNo++, ProgramInstanceFields.InstanceEnd.Name);
fields.DefineField(ProgramInstanceFields.InstanceName, fieldNo++, ProgramInstanceFields.InstanceName.Name);
fields.DefineField(ProgramInstanceFields.InstanceNotes, fieldNo++, ProgramInstanceFields.InstanceNotes.Name);
fields.DefineField(ProgramInstanceFields.ProgramId, fieldNo++, ProgramInstanceFields.ProgramId.Name);
fields.DefineField(ProgramInstanceFields.SchoolId, fieldNo++, ProgramInstanceFields.SchoolId.Name);
fields.DefineField(ProgramInstanceFields.SchoolProgramAdminUserId, fieldNo++, ProgramInstanceFields.SchoolProgramAdminUserId.Name);
fields.DefineField(ProgramInstanceFields.SelectAll, fieldNo++, ProgramInstanceFields.SelectAll.Name);
fields.DefineField(ProgramInstanceFields.ClosureReasonId, fieldNo++, ProgramInstanceFields.ClosureReasonId.Name);
filter.Relations.Add(ProgramInstanceEntity.Relations.ProgramEntityUsingProgramId, JoinHint.Left);
DataTable dynamicList = new DataTable();
ISortExpression aSortExpression = new SortExpression(ProgramInstanceFields.InstanceEnd | SortOperator.Ascending);
anAdaptor.FetchTypedList(fields, dynamicList, filter, 0, aSortExpression, true, null, pageNumber, pageSize);
//derive Length field from Start and End Dates
return dynamicList;
}
}