Fun with products and Expression columns

[Earl]

I have somewhat of an interesting scenario:

The form allows the user to select a service, which populates a a grid of
product information related to that service ("service grid"). The user can
then select from the products in the first grid and those choices populate
the second "choices grid".

The product grid is bound to a strongly-typed dataset table which can be
filled in a couple of different ways. More on this shortly.

The choices datatable contains expression columns related to a product
table. So if the user choses Widget A, B, and C, the product prices and a
lot of other details for that service are reflected into the choices grid
without those values actually being duplicated into the choices table.

All of the structure works well technically. But there is a fundamental
concept decision to be made. That decision is in how to populate the
products table. At some future date, the product catalog may grow to be
50,000, 100,000 or even greater. My instinct is to individually populate the
products datatable with ONLY the Widgets that relate to Service1 when it is
selected, and then ONLY populate the widgets that releate to Service 2 when
it is selected (each service requires a subset of the entire product
catalog, thus limiting how big the product table may be).

Where this becomes problematic is that the user may need 2 or more Services.
So if they select products that relate to Service 1, Service 2, etc., then
the catalog to be displayed must grow much bigger. Displaying just one set
of products at a time will not work because this would cause a partial
display of the expression columns in the choices grid (since the
non-populated products still have a primary key back to the products table,
they display a row, but not the expression data).

The easiest answer would be to just populate the entire catalog in the
products grid. Of course, the easiest solution is the most expensive in
terms of database and processor resources. The next easiest answer would be
to populate the product grid with just one set of products at a time. But
this creates the above-mentioned issue with the expression columns.

The technical implementation is not an issue. I'm just looking for some
ideas or thoughts from anyone who has been down this road with multiple
grids and expression columns.

 

[Scott M.]

How does this question relate specifically to C# and VB.NET? Wouldn't you
think that ADO.NET is solely the perfect place for this post? Guess not.
How about sharing the project type you are working on (ASP.NET/Windows
Forms)?

 

[Earl]

I've often heard that the definition of a literalist is one who, upon seeing
the writing on the wall, stops to examine the penmanship. "Specifically," C#
and VB.Net are quite broad in scope. Since the question involved the
resources of both ADO.Net and the concept as a whole, it would seem that a
more general audience might have had some insight (the resolution was in
fact to simplify the form and roll up one of the tables). It was a winforms
app.

 

[Scott M.]

In general, it is best to post your question to the group that is most
relevant to your question and then if you don't find what you are looking
for to broaden the scope of the audience. That is simple newsgroup
etiquitte.

Your question is clearly an ADO.NET question who's solution is NOT language
specific.

 

[Guest]

Earl,

I think I understand what you are asking. See if this makes sense.

It sounds as though you could use a seperate datatable to populate with the
selected products only. You're choices would then link to that datatable
instead of the selection datatable.

 

[Earl]

Thanks Tom, I think you are right. My short-term solution was to simplify
the concept which I may later need to revisit.

 

[Earl]

I don't need a lecture from some twinkie. If you don't have an answer, stay
out of the thread.

 

[Cor Ligthert [MVP]]

Earl,

If you cannot stand critique, stay out of the newsgroups.

However to answer your question. Forever look where is your biggest bottle
neck.

Making a column on a client will probably go in for humans not recognize
time while it is doing for one client x times and not for x clients x time.

Therefore if you can bring processing to the client than do that.

However it that means that you have to process and collect more data than
needed for the process on the server, than the server is in my idea the
right place.

Cor

 

[Scott M.]

And, then come the insults....Instead of the "Yeah, I see your point,
sorry.".

 

[Earl]

If I needed useless and trivial critique, I would post in the more demanding
forums more often.

Funny how the programmers always to push processing back onto the server and
the DBAs want to push the processing back onto the clients.