Asp.net 2.0 - C# Self servicing LLBLGEN Pro 2.0.0.0 FINAL Microsoft Sql 2005

Hi,

I have a question about the GetMultiAsDataTable.

I want to use the paging and then databind it, but the code below just returns all rows in the Releases table, so no filter is involved.

bll.DaoClasses.ReleasesDAO r = new bll.DaoClasses.ReleasesDAO();
        
repReleases.DataSource = r.GetMultiAsDataTable(0, null, null, null, 2, 4);
repReleases.DataBind();

2 - is the pageNumber 4 - is the pageSize

When I run the following code the SQL syntax looks like this:

SELECT DISTINCT [presszone2].[dbo].[Releases].[ReleaseId], [presszone2].[dbo].[Releases].[UserId], [presszone2].[dbo].[Releases].[CatId], 
[presszone2].[dbo].[Releases].[CreateDate], [presszone2].[dbo].[Releases].[Header], [presszone2].[dbo].[Releases].[Content], [presszone2].[dbo].[Releases].[Intro] FROM 
[presszone2].[dbo].[Releases] 

How come it doesn't filter with pages and pagesize like I gave it and the parameteres?

Thanks Mark

Hi Mark, Could you post the entire snippet of the code you are trying to getting work?

Please read LLBLGenPro Help - Using generated code - Using TypedViews, TypedLists and DynamicLists - Using dynamic lists.

This is an example of DynList with paging. This work perfectly:

// define fields of my resulset
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(OrdersFields.OrderId, 0, "OrderID", "Order");
fields.DefineField(OrderDetailsFields.ProductId, 1, "AmountItems", "OrderDetails", AggregateFunction.Count);

// relation to use
IRelationCollection relations = new RelationCollection();
relations.Add(OrdersEntity.Relations.OrderDetailsEntityUsingOrderId, "Order", "OrderDetails", JoinHint.None);

// group clause to use
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);

// fetch data (page = 2, pageSize = 10)
DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dynamicList, 0, null, null, relations, true, groupByClause, null, 2, 10);

// show results
foreach (DataRow r in dynamicList.Rows)
{
    Console.WriteLine("Order {0}: {1} items", r[0], r[1]);
}

Hi,

Thanks for the answer.

        // define fields
        ResultsetFields fields = new ResultsetFields(6);
        fields.DefineField(ReleasesFields.ReleaseId, 0, "ReleaseId", "Releases");
        fields.DefineField(ReleasesFields.Header, 1, "Header", "Releases");
        fields.DefineField(ReleasesFields.CreateDate, 2, "CreateDate", "Releases");
        fields.DefineField(ReleasesFields.Intro, 3, "Intro", "Releases");
        fields.DefineField(CategoriesFields.Name.SetObjectAlias("Categories"), 4, "CatName", "Categories");
        fields.DefineField(AspnetUsersFields.UserName.SetObjectAlias("AspnetUsers"), 5, "UserName", "AspnetUsers");


        SortExpression sortFilter = new SortExpression();
        sortFilter.Add(ReleasesFields.CreateDate.SetObjectAlias("Releases") | SortOperator.Ascending);


        // approve filter
        IPredicateExpression customFilter = new PredicateExpression();
        customFilter.Add(VerifyFields.IsApproved.SetObjectAlias("Verify") == "True"); // must be approved

        // select filters
        IPredicateExpression selectFilter = new PredicateExpression();


        // relations to use
        IRelationCollection relations = new RelationCollection();
        relations.Add(ReleasesEntity.Relations.VerifyEntityUsingReleaseId, "Releases", "Verify", JoinHint.None).CustomFilter = customFilter;
        relations.Add(ReleasesEntity.Relations.CategoriesEntityUsingCatId, "Releases", "Categories", JoinHint.Left);
        relations.Add(ReleasesEntity.Relations.AspnetUsersEntityUsingUserId, "Releases", "AspnetUsers", JoinHint.None);

        // group clause to use
        IGroupByCollection groupByClause = new GroupByCollection();
        groupByClause.Add(fields[0]);
        groupByClause.Add(fields[1]);
        groupByClause.Add(fields[2]);
        groupByClause.Add(fields[3]);
        groupByClause.Add(fields[4]);
        groupByClause.Add(fields[5]);

        // fetch data with the filters
        DataTable dynamicList = new DataTable();
        TypedListDAO dao = new TypedListDAO();
        dao.GetMultiAsDataTable(fields, dynamicList, 0, sortFilter, selectFilter, relations, false, groupByClause, null, 0, 0);

exec sp_executesql N'SELECT DISTINCT [LPA_R2].[ReleaseId], [LPA_R2].[Header], [LPA_R2].[CreateDate], [LPA_R2].[Intro], [LPA_C3].[Name] AS [CatName], [LPA_A4].[UserName] FROM ((( 
[presszone2].[dbo].[Verify] [LPA_V1]  INNER JOIN [presszone2].[dbo].[Releases] [LPA_R2]  ON  [LPA_V1].[ReleaseId]=[LPA_R2].[ReleaseId] AND ( [LPA_V1].[IsApproved] = 
@IsApproved1)) LEFT JOIN [presszone2].[dbo].[Categories] [LPA_C3]  ON  [LPA_C3].[CatId]=[LPA_R2].[CatId]) INNER JOIN [presszone2].[dbo].[aspnet_Users] [LPA_A4]  ON  
[LPA_A4].[UserId]=[LPA_R2].[UserId]) GROUP BY [LPA_R2].[ReleaseId], [LPA_R2].[Header], [LPA_R2].[CreateDate], [LPA_R2].[Intro], [LPA_C3].[Name], [LPA_A4].[UserName] ORDER BY 
[LPA_R2].[CreateDate] ASC',N'@IsApproved1 bit',@IsApproved1=1

I used the code you suggested and changed it to fit my needs (see c# code above), but the SQL query still looks the same, no paging gets done.

I think I will try to run the code just like you wrote it on the Northwind Database, to see if that works like i imagined

I will return , but thanks for your time.

dao.GetMultiAsDataTable(fields, dynamicList, 0, sortFilter, selectFilter, relations, false, groupByClause, null, 0, 0);

I used the code you suggested and changed it to fit my needs (see c# code above), but the SQL query still looks the same, no paging gets done.

You have passed 0, 0 for PageSize and PageNumber.

yes, you're right. my bad.

but I ran the code exact as from the example, and from what i can see, no paging is happening

Here is the sql query that the code executed:

SELECT [LPA_O1].[OrderID], COUNT([LPA_O2].[ProductID]) AS [AmountItems] FROM ( [Northwind].[dbo].[Orders] [LPA_O1]  INNER JOIN [Northwind].[dbo].[Order Details] [LPA_O2]  ON  
[LPA_O1].[OrderID]=[LPA_O2].[OrderID]) GROUP BY [LPA_O1].[OrderID]

Here is the code:

// define fields of my resulset
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(OrdersFields.OrderId, 0, "OrderID", "Order");
fields.DefineField(OrderDetailsFields.ProductId, 1, "AmountItems", "OrderDetails", AggregateFunction.Count);

// relation to use
IRelationCollection relations = new RelationCollection();
relations.Add(OrdersEntity.Relations.OrderDetailsEntityUsingOrderId, "Order", "OrderDetails", JoinHint.None);

// group clause to use
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);

// fetch data (page = 2, pageSize = 10)
DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dynamicList, 0, null, null, relations, true, groupByClause, null, 2, 10);

// show results
foreach (DataRow r in dynamicList.Rows)
{
    Console.WriteLine("Order {0}: {1} items", r[0], r[1]);
}

Am I missing something?

And i fixed the pageSize and pageNumber parameters.

Thanks.

The query you posted, is that the one 'generated' or executed? Because after the query is generated (and which is then displayed by the DQE tracer) the query is 'wrapped' into the actual query executed, which in your case will use a temptable, not a CTE because of the 1:n relation which could lead to wrong results)

When I use your code, I get (doesn't matter which comp. level)

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPA_O1].[OrderID], COUNT([LPA_O2].[ProductID]) AS [AmountItems] FROM ( [Northwind].[dbo].[Orders] [LPA_O1]  INNER JOIN [Northwind].[dbo].[Order Details] [LPA_O2]  ON  [LPA_O1].[OrderID]=[LPA_O2].[OrderID]) GROUP BY [LPA_O1].[OrderID]

Method Exit: CreateSelectDQ
Generated Sql query: 
    Query: CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT NULL,[OrderID][Int] NULL,[AmountItems][Int] NULL);INSERT INTO #TempTable ([OrderID],[AmountItems]) SELECT TOP 21 [LPA_O1].[OrderID], COUNT([LPA_O2].[ProductID]) AS [AmountItems] FROM ( [Northwind].[dbo].[Orders] [LPA_O1]  INNER JOIN [Northwind].[dbo].[Order Details] [LPA_O2]  ON  [LPA_O1].[OrderID]=[LPA_O2].[OrderID]) GROUP BY [LPA_O1].[OrderID];SELECT [OrderID],[AmountItems] FROM #TempTable WHERE [__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd ORDER BY [__rowcnt] ASC;DROP TABLE #TempTable
    Parameter: @__rownoStart : Int32. Length: 4. Precision: 10. Scale: 0. Direction: Input. Value: 10.
    Parameter: @__rownoEnd : Int32. Length: 4. Precision: 10. Scale: 0. Direction: Input. Value: 20.

Method Exit: CreatePagingSelectDQ
Order 10332: 3 items
Order 10333: 3 items
Order 10334: 2 items
Order 10335: 4 items
Order 10336: 1 items
Order 10337: 5 items
Order 10338: 2 items
Order 10339: 3 items
Order 10340: 3 items
Order 10341: 2 items

As you can see, the query which is produced by the DQE is emitted first in the trace, however after that the paging query is shown, with the temptable creation statement wrapped around it.

Ok. Thats how it should look like.

Heres is my trace from the DQE.

Method Enter: DaoBase.PerformGetMultiAsDataTableAction(11)
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPA_O1].[OrderID], COUNT([LPA_O2].[ProductID]) AS [AmountItems] FROM ( [Northwind].[dbo].[Orders] [LPA_O1]  INNER JOIN [Northwind].[dbo].[Order Details] [LPA_O2]  ON  [LPA_O1].[OrderID]=[LPA_O2].[OrderID]) GROUP BY [LPA_O1].[OrderID]

Method Exit: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPA_O1].[OrderID], COUNT([LPA_O2].[ProductID]) AS [AmountItems] FROM ( [Northwind].[dbo].[Orders] [LPA_O1]  INNER JOIN [Northwind].[dbo].[Order Details] [LPA_O2]  ON  [LPA_O1].[OrderID]=[LPA_O2].[OrderID]) GROUP BY [LPA_O1].[OrderID]

Method Exit: CreatePagingSelectDQ
Method Enter: DaoBase.ExecuteMultiRowDataTableRetrievalQuery(4)
Method Exit: DaoBase.ExecuteMultiRowDataTableRetrievalQuery(4)
Method Exit: DaoBase.PerformGetMultiAsDataTableAction(11)

There is not "CREATE TABLE #TempTable" as your trace looked like.

and here is the code:

        // define fields of my resulset
        ResultsetFields fields = new ResultsetFields(2);
        fields.DefineField(OrdersFields.OrderId, 0, "OrderID", "Order");
        fields.DefineField(OrderDetailsFields.ProductId, 1, "AmountItems", "OrderDetails", AggregateFunction.Count);

        // relation to use
        IRelationCollection relations = new RelationCollection();
        relations.Add(OrdersEntity.Relations.OrderDetailsEntityUsingOrderId, "Order", "OrderDetails", JoinHint.None);

        // group clause to use
        IGroupByCollection groupByClause = new GroupByCollection();
        groupByClause.Add(fields[0]);

        // fetch data 
        DataTable dynamicList = new DataTable();
        TypedListDAO dao = new TypedListDAO();
        dao.GetMultiAsDataTable(fields, dynamicList, 0, null, null, relations, true, groupByClause, null, 2, 2);

        // show results
        foreach (DataRow r in dynamicList.Rows)
        {
            Trace.Warn(string.Format("Order {0}: {1} items", r[0], r[1]));
        }

It returns all the rows, or orders.

It's strange that I don't get the same DQE trace as you do.

Which LLBLGen Pro runtim library build are you using?

Walaa wrote:

Which LLBLGen Pro runtim library build are you using?

I'm using version: 2.0.7.129

kirkov wrote:

Walaa wrote:

Which LLBLGen Pro runtim library build are you using?

I'm using version: 2.0.7.129

Have you set the compatibility mode of the DQE to a specific value? Like sqlserver2005 compatibility ?

The builds after that date don't contain a change in the paging code so it's very obscure why this fails... You could try though, as it works here.

I can't reproduce it, when I pick your code and literarly use it as a unittest, it succeeds with a temptable paging (I set the comp. level to sqlserver2005, but the DQE switches back to temptable paging because of the 1:n relation which could lead to wrong results):

Order 10324: 5 items Order 10325: 5 items

but the DQE switches back to temptable paging because of the 1:n relation which could lead to wrong results

It would be useful to update the documentation if this is the case.

Yes!

It was the compatibility mode that caused that behavior. So i guess i don't need to set that. Now it works just like I had in mind.

Thanks to all for the great and fast help!