2

I am performing a review on different kind of ORM tooling and DAL generators today. One of them is NetTiers.

I have a classic DB model with customer, order, orderdetail, etc..

I want to perform a complex inner join on those tables. This is the orginal SQL query:

SELECT [Contact].LastName, SUM(OrderRow.Amount * Product.Price) TotalAmount
FROM Contact
    INNER JOIN [Order] ON [Contact].ContactId=[Order].ContactId 
        INNER JOIN [OrderRow] ON [Order].OrderId=[OrderRow].OrderId
            INNER JOIN [Product]ON OrderRow.ProductId=Product.ProductId 
                GROUP BY [OrderRow].OrderId, [Contact].LastName
                    HAVING SUM(OrderRow.Amount * Product.Price) > 100

I couldn't find a way to get this done in code with NetTiers. Can you ?

(ps: using VS2008 SP1 and SQLServer2008 SP1)

Patrick Peters
  • 9,456
  • 7
  • 57
  • 106

2 Answers2

3

You can't do it without a custom stored procedure. Solution here: http://benpowell.org/paging-and-sorting-in-a-nettiers-custom-stored-procedure/

Rebecca
  • 13,914
  • 10
  • 95
  • 136
0

Why not create a custom stored procedure for that, nettiers generates specific methods for stored procedures under the TableProvider class, afterwards you can simply call your methd. the method return type will probably be a DataSet in this case(not sure!). See here

Ahmed
  • 11,063
  • 16
  • 55
  • 67
  • I know, but that would fix the project to a specific vendor. NHibernate or LLBLGenPro uses its own object query language to do that, and it is DB agnostic – Patrick Peters May 25 '09 at 12:51