1

Given a query like:

SELECT table1.field1 FirstField, table2.field2 SecondField
    FROM table1
    INNER JOIN table2 ON table1.FK = table2.PK
    WHERE table1.somefield = 'somevalue';

My objective is to return a strongly typed result set using .netTiers. I assume that I cannot use a view because the WHERE clause needs a parameter, which cannot be passed to a view. A stored procedure can be passed the 'somevalue' parameter but returns a weakly typed DataSet or DataReader.

I figure I'm just missing a concept here. Just to be clear, what I'd like to end up with is to be able to write something like this:

TList <some-entity-name> entityList = DataRepository.SomeProvider.Get( "somevalue" );

foreach ( some-entity-name entity in entityList ) {
    DoSomethingWith( entity.FirstField, entity.SecondField );
}

I'd like to avoid a solution that involves a server-side filter after the query has executed; the tables involved are very large.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Bob Kaufman
  • 12,864
  • 16
  • 78
  • 107

2 Answers2

2

Create a view and use the strongly typed ParameterBuilder object to filter the view on the specific column. I can't quite remember what layer this object is in.

This is how you would use it:

MyViewParameterBuilder builder = new MyViewParameterBuilder();
builder.AppendEquals(TableColumn.Column, "value");
DataRepository.MyViewEntityProvider.Find(builder.GetParameters());

I may be wrong, but i do not believe net-tiers actually 'filters' the TList/VList object with the method above, which is what the name of the object would suggest. The builder generates there where clause and nettiers runs a query against your database using this clause.

Your 2nd option is to try generating a stored procedure, but only if the resultset of your stored proc matches the schema of one of the tables in your database. Otherwise, net-tiers will not know how to generate against it. You can read more on that here

  • Thank you for getting me on the right path. For subsequent visitors, I'm compelled to offer a few corrections: MyViewEntityParameterBuilder should be MyViewParameterBuilder, and AppendEqual should be AppendEquals. – Bob Kaufman Sep 13 '09 at 18:11
0

If you want a TList collection with a join you'll need to create a custom stored procedure. Solution here: http://benpowell.org/paging-and-sorting-in-a-nettiers-custom-stored-procedure/

If you have scenario like this:

  • Account (PK AccountId)
  • Client (PK ClientId)
  • ClientAccount (PK ClientId, AccountId)

As long as you have the correct foreign keys setup, .NetTiers will provide you with the appropriate method:

AccountService s = new AccountService();
TList<Account> accountCollection = s.GetByClientIdFromClientAccount(1);

There is also an overloaded method generated that supports paging (sadly no orderBy clause).

Rebecca
  • 13,914
  • 10
  • 95
  • 136
  • You can use Linq to do an order by like this... DataRepository.BlahProvider.GetAll().OrderByDescending() – Dave Hanson Feb 04 '11 at 13:10
  • @Dave Hanson one could use a number of alternative ORMs that are more functional than .NetTiers but that wasn't what the op asked for. – Rebecca Feb 07 '12 at 20:42