Select or Filtering on a related group

[Sam Matthews]

I'm new to ADO.NET, and so far I am not too impressed.

After plowing through the pages and pages of fancy disconnected
relational blah blah, I find myself wanting to do something so simple
- that you wouldn't think twice about it in SQL. But in ADO.NET it
appears to be impossible - without resorting to hand coding all of it.
The more things change, the more they stay the same.

Here's what I want to do...

I have a series of related tables, for simplicity I will explain only
a subset. This is a classic 3 table "many to many" relationship. In
this case I have Securities, Exchanges, and ExchSec. Securities can
be traded on several exchanges, and Exchanges support many Securites -
represented by ExchSec.

I have setup a Typed Dataset complete with relations, foreign keys and
all that jazz.

I have a GUI upon which is places two databound controls. When the
user selects a Security, I would like to display the list of availible
Exchanges for that Security.

So far, I don't see one good way of utilizing the relations in place
for doing that.

DataTable.Select does what? Return an array of DataRow. What the
hell can I do with an array of DataRow in regards to a databound
control?

DataView.RowFilter takes a string expression that appears to allow a
relation to a Child - BUT ONLY if that child represents a single value
- otherwise you have to use an aggregate function - what the hell good
is that.

Once again the senerio is this - the user selects a Security. the
Exchange(s) where that Security can be traded are derived from
ExchSec, but only the IDs. The full Exchange Description must be
derived from Exchange. This is as old as the hills.

In SQL we are talking ...

SELECT Exchange.Description
FROM Exchange, Security, ExchSec
WHERE Exchange.ExchId = ExchSec.ExchId
AND Security.SecId = ExchSec.SecId
AND Security.SecId = "3"

SQL 101.

So in short, there seems to be no way to filter a dataset on a field,
and the multiple occurences of that field in a group, dataset, array,
collection or what have you.

I'm really disapointed.

Sam

 

[W.G. Ryan eMVP]

Sam:

What you're having an issue with isn't with ADO.NET - it's with Winforms and
Binding. Anyway though, that nuance doesn't help your problem so let me try
to help you.

You could bind all three objects, the Securities ComboBox, the Exchanges
ComboBox and the ExchSec grid to three separate DataView objects, each based
on the table that they are now bound to. Now, if the user changes the first
combobox, you can set the RowFilter of the View that the grid is bound to to
match only the ids that correspond to that Security. That will cause the
grid to be filtered to only those transactions represented in the securities
combobox essentially performing an inner join on the two. Now, There can be
many different Exchanges so you need to make a determination on how you want
to handle that since the combobox can hold multiple value. If the user
clicks a row in the grid, you can grab the exchange value (which can not be
visible if you so desire) and then use that exchange ID to set the Filter of
the combobox. You can repeat any time the user clicks in the grid. The
same can work in reverse. There are a lot of little 'if's' that can
affect how you really want to do this, but that strategy will get you there.
You can also have just a textbox for Securities and Exchanges, then have a
little button that fires a dialog bog for all of the securities and all of
the exchanges and just use public properties to get those values. Either
way it's simply a matter of deciding what events you want to trap and go
from there.

Think about the complexity of what you're talking about in terms of binding.
The DataSet would need some sort of mechanism to determine if you were
constraining on Only securities or only exchanges or both. It would need to
know which one takes precedent. It would need to know if you selected
exchanges and wanted every matching record whether or not to show you every
record or only the ones that match the other combobox value too. That's a
lot of knowing.

Furthermore, you don't need to use binding at all. You can pull down the
data into your dataset and just use the events to get the values you want
using DataTable.Select for instance, and then settting the control
properties accordingly. Back in the good old days when binding was really
lame, this is what we did anyway except we actually had the overhead of
hitting the db each time which really sucked.

Either way, I'd encourage you to keep on with ADO.NET, I'll bet you'll come
to like it quickly and find out you are ahead in virtually every respect.
And if I can be of help, please don't hesitate to let me know.

Cheers,

Bill

 

[Sam Matthews]

Sam:

What you're having an issue with isn't with ADO.NET - it's with Winforms and
Binding. Anyway though, that nuance doesn't help your problem so let me try
to help you.

Point taken - I didn't notice that there was a seperate group until
later.

You could bind all three objects, the Securities ComboBox, the Exchanges
ComboBox and the ExchSec grid to three separate DataView objects, each based
on the table that they are now bound to. Now, if the user changes the first
combobox, you can set the RowFilter of the View that the grid is bound to to
match only the ids that correspond to that Security. That will cause the
grid to be filtered to only those transactions represented in the securities
combobox essentially performing an inner join on the two. Now, There can be
many different Exchanges so you need to make a determination on how you want
to handle that since the combobox can hold multiple value. If the user
clicks a row in the grid, you can grab the exchange value (which can not be
visible if you so desire) and then use that exchange ID to set the Filter of
the combobox.

I'm not sure I understand you completely...

So say I make three DataViews....

Security (SecId, Symbol) - IBM,CSCO, etc
Exchange (ExchId, ExchDescription) - NYSE,NASDAQ,INET, etc
ExchSec (SecId, ExchId) - represents "many to many" relationship

The first two are bound to a combo box.

When the user selects a Security - we want to show - available
Exchanges.

I can derive the SecId as the SelectedValue of the combobox.. I use
this to Filter the ExchSec DataView.. Now I have one or more
ExchId(s) with which to use on the next step - to Filter the Exchange
DataView.

And herin lies my question. How do I perform a Filter with a "set" of
data. How do I say "filter your contents to match the contents of
this list/array/collection or what have you? This is what I don't
see.

I am new to this, so I hope I'm missing something. It just seems to
be a gross oversight to leave this out. There should be a function
like "DataView.Filter (DataRow[] rows)" that filters the contents of
the view to the contents of the list. No?

Think about the complexity of what you're talking about in terms of binding.
The DataSet would need some sort of mechanism to determine if you were
constraining on Only securities or only exchanges or both. It would need to
know which one takes precedent. It would need to know if you selected
exchanges and wanted every matching record whether or not to show you every
record or only the ones that match the other combobox value too. That's a
lot of knowing.

In that sense your right, but as I explained above - I'm only looking
for that function that will filter based on a set.

Furthermore, you don't need to use binding at all. You can pull down the
data into your dataset and just use the events to get the values you want
using DataTable.Select for instance, and then settting the control
properties accordingly.

Hmmm. that might just be the revelation. Binding always sneaks up

Back in the good old days when binding was really
lame, this is what we did anyway except we actually had the overhead of
hitting the db each time which really sucked.

I never did that anyway - but I did resort to coding every darn filter
and repopulating control by hand - and I was hoping to get out of it.

Either way, I'd encourage you to keep on with ADO.NET, I'll bet you'll come
to like it quickly and find out you are ahead in virtually every respect.
And if I can be of help, please don't hesitate to let me know.

Oh I'm just kidding about that...I actually like it alot... but I
figure - you dont' get any attention unless your insulting someone
around here right???

Cheers,

Bill

Thanks
Sam

 

[W.G. Ryan eMVP]

To use a Set of data you can use the IN Function of the datacolumn - I think
that's probably all you need to do if I read your post correctly, looks like
you're on the right track.

"W.G. Ryan eMVP" <[email protected]> wrote in message

Sam:

What you're having an issue with isn't with ADO.NET - it's with Winforms and
Binding. Anyway though, that nuance doesn't help your problem so let me try
to help you.

Point taken - I didn't notice that there was a seperate group until
later.

You could bind all three objects, the Securities ComboBox, the Exchanges
ComboBox and the ExchSec grid to three separate DataView objects, each based
on the table that they are now bound to. Now, if the user changes the first
combobox, you can set the RowFilter of the View that the grid is bound to to
match only the ids that correspond to that Security. That will cause the
grid to be filtered to only those transactions represented in the securities
combobox essentially performing an inner join on the two. Now, There can be
many different Exchanges so you need to make a determination on how you want
to handle that since the combobox can hold multiple value. If the user
clicks a row in the grid, you can grab the exchange value (which can not be
visible if you so desire) and then use that exchange ID to set the Filter of
the combobox.

I'm not sure I understand you completely...

So say I make three DataViews....

Security (SecId, Symbol) - IBM,CSCO, etc
Exchange (ExchId, ExchDescription) - NYSE,NASDAQ,INET, etc
ExchSec (SecId, ExchId) - represents "many to many" relationship

The first two are bound to a combo box.

When the user selects a Security - we want to show - available
Exchanges.

I can derive the SecId as the SelectedValue of the combobox.. I use
this to Filter the ExchSec DataView.. Now I have one or more
ExchId(s) with which to use on the next step - to Filter the Exchange
DataView.

And herin lies my question. How do I perform a Filter with a "set" of
data. How do I say "filter your contents to match the contents of
this list/array/collection or what have you? This is what I don't
see.

I am new to this, so I hope I'm missing something. It just seems to
be a gross oversight to leave this out. There should be a function
like "DataView.Filter (DataRow[] rows)" that filters the contents of
the view to the contents of the list. No?

Think about the complexity of what you're talking about in terms of binding.
The DataSet would need some sort of mechanism to determine if you were
constraining on Only securities or only exchanges or both. It would need to
know which one takes precedent. It would need to know if you selected
exchanges and wanted every matching record whether or not to show you every
record or only the ones that match the other combobox value too. That's a
lot of knowing.

In that sense your right, but as I explained above - I'm only looking
for that function that will filter based on a set.

Furthermore, you don't need to use binding at all. You can pull down the
data into your dataset and just use the events to get the values you want
using DataTable.Select for instance, and then settting the control
properties accordingly.

Hmmm. that might just be the revelation. Binding always sneaks up

Back in the good old days when binding was really
lame, this is what we did anyway except we actually had the overhead of
hitting the db each time which really sucked.

I never did that anyway - but I did resort to coding every darn filter
and repopulating control by hand - and I was hoping to get out of it.

Either way, I'd encourage you to keep on with ADO.NET, I'll bet you'll come
to like it quickly and find out you are ahead in virtually every respect.
And if I can be of help, please don't hesitate to let me know.

Oh I'm just kidding about that...I actually like it alot... but I
figure - you dont' get any attention unless your insulting someone
around here right???

Cheers,

Bill

Thanks
Sam

 

[Guest]

Can anyone give an example of how to use the IN function? I have a similar
problem to Sam Matthews, but I can't find any good documentation on how to
use IN in a filter expression.

To use a Set of data you can use the IN Function of the datacolumn - I think
that's probably all you need to do if I read your post correctly, looks like
you're on the right track.

"W.G. Ryan eMVP" <[email protected]> wrote in message

Sam:

What you're having an issue with isn't with ADO.NET - it's with Winforms and
Binding. Anyway though, that nuance doesn't help your problem so let me try
to help you.

Point taken - I didn't notice that there was a seperate group until
later.

You could bind all three objects, the Securities ComboBox, the Exchanges
ComboBox and the ExchSec grid to three separate DataView objects, each based
on the table that they are now bound to. Now, if the user changes the first
combobox, you can set the RowFilter of the View that the grid is bound to to
match only the ids that correspond to that Security. That will cause the
grid to be filtered to only those transactions represented in the securities
combobox essentially performing an inner join on the two. Now, There can be
many different Exchanges so you need to make a determination on how you want
to handle that since the combobox can hold multiple value. If the user
clicks a row in the grid, you can grab the exchange value (which can not be
visible if you so desire) and then use that exchange ID to set the Filter of
the combobox.

I'm not sure I understand you completely...

So say I make three DataViews....

Security (SecId, Symbol) - IBM,CSCO, etc
Exchange (ExchId, ExchDescription) - NYSE,NASDAQ,INET, etc
ExchSec (SecId, ExchId) - represents "many to many" relationship

The first two are bound to a combo box.

When the user selects a Security - we want to show - available
Exchanges.

I can derive the SecId as the SelectedValue of the combobox.. I use
this to Filter the ExchSec DataView.. Now I have one or more
ExchId(s) with which to use on the next step - to Filter the Exchange
DataView.

And herin lies my question. How do I perform a Filter with a "set" of
data. How do I say "filter your contents to match the contents of
this list/array/collection or what have you? This is what I don't
see.

I am new to this, so I hope I'm missing something. It just seems to
be a gross oversight to leave this out. There should be a function
like "DataView.Filter (DataRow[] rows)" that filters the contents of
the view to the contents of the list. No?

Think about the complexity of what you're talking about in terms of binding.
The DataSet would need some sort of mechanism to determine if you were
constraining on Only securities or only exchanges or both. It would need to
know which one takes precedent. It would need to know if you selected
exchanges and wanted every matching record whether or not to show you every
record or only the ones that match the other combobox value too. That's a
lot of knowing.

In that sense your right, but as I explained above - I'm only looking
for that function that will filter based on a set.

Furthermore, you don't need to use binding at all. You can pull down the
data into your dataset and just use the events to get the values you want
using DataTable.Select for instance, and then settting the control
properties accordingly.

Hmmm. that might just be the revelation. Binding always sneaks up

Back in the good old days when binding was really
lame, this is what we did anyway except we actually had the overhead of
hitting the db each time which really sucked.

I never did that anyway - but I did resort to coding every darn filter
and repopulating control by hand - and I was hoping to get out of it.

Either way, I'd encourage you to keep on with ADO.NET, I'll bet you'll come
to like it quickly and find out you are ahead in virtually every respect.
And if I can be of help, please don't hesitate to let me know.

Oh I'm just kidding about that...I actually like it alot... but I
figure - you dont' get any attention unless your insulting someone
around here right???

Cheers,

Bill

Thanks
Sam