queries across multiple tables

  • Thread starter Thread starter Maersa
  • Start date Start date
M

Maersa

Hi All,

I need to make a sql query build which needs to do the following :
- span across 20-30 tables
- Filters
- provide a UI (end user would use this)

So the idea is that the end user can pickup any field from any table and
apply some conditions (filters) to it and then retrieve the resulting
data --- fields that make up the resulting data can also be defined by the
end user.

Does anybody have any suggestions on how this can be implemented ?

thanks,
 
Take extensive advantage of the DataRelation Object. I can't get more
specific without knowing how they all relate to each other, but if you
google on DataRelation, there are tons of great links.

For filtering..you'll want to take advantage of the DataView and its
..RowFilter property, and probably the Select method of a DataTable.

Depending on how all of this is being used (ie, is it UI driven, Web Service
Driven etc), you'll probably need to use a BindingContext/BindingManager.

You probably also want to break it up into as many logical pieces as
possible, unless all 30 tables are all physically related all the time. If
not, then try to break up logical areas and let your objects talk to each
other, passing the needed data back and forth. Once again, this
implementation is kind of difficult to suggest without knowing the nature
off the tables and how they relate to each other.

Is everything related? Are many of these lookup tables? Is this a UI
driven app? If so, are all of the tables coupled to the UI? Or is it a
service driven app?

If you can post some more info, I can probably give you some more specific
recommendations.

HTH,

Bill
 
Hi Bill,
Is everything related?
Click to expand...
Is all of them are related expect for a few.
Are many of these lookup tables?
Click to expand...
Maybe like 70% of them are lookup's.
Is this a UI driven app?
Click to expand...
Yes. User can pick a field decide to set filters, sort etc. and
they can decide which fields to display in the resulting data.

thanks,
 
If they are purely lookups you'll want to play with the DisplayMember,
ValueMember properties of the binding..there are many examples of this on
the net.

The DataRelation will get you where you want to go, but the number of tables
will make it a little cumbersom to write..it is still pretty simple, you'll
just basically need to do the same thing over and over and you'll no doubt
have to adjust the relationships. One other thing that may give you trouble
is that you need non-null values to build datarelations so if you have Nulls
in the related table, you'll need to put somethign in there or get another
field.
 
Back
Top