Filtering question with related tables

  • Thread starter Thread starter rew190
  • Start date Start date
R

rew190

Hi guys, I've been struggling with this one for a while.

I have a typed dataset. It has two tables inside of it; Resources and
EmployeeMaster. Both EmployeeMaster and Resources have a column called
"EmpID", an int, which is also both of their primary keys. This is the
relationship between the two, and is present in the dataset.

EmployeeMaster has detailed data about employees such as full name,
address, email, etc. Resources is a table that I created that is used
to determine which employees are using a program I am writing right
now, and what their security level is. That column is of type int
called "UserType".

So this is all set up, my dataset is populating nicely with every row
from both tables, everything seems to be set.

Now, what I want to do is populate a listbox on a C# Windows.Forms app
with employee names (stored only in EmployeeMaster) of only those
employees who have a UserType (stored only in Resources) of 5.

The equivalent SQL for this is simple. "SELECT emp.LastName FROM
EmployeeMaster emp, Resources r WHERE r.UserType = 5"

I can't figure out how to get the equivalent of this to work in my
typed DataSet so I can databind it to my listbox. I looked at
DataViews, but couldn't find any decent examples of the RowFilter that
was filtering on a child table. I did find one page that said there's
a "[child]" keyword that should be put in there, but I couldn't figure
that out. I also looked at DataViewManagers, but hit a dead end on
that one as well.

Can one of you gurus give me a little guidance, please? I feel silly
asking what's probably a really simple question, but I've scoured the
net for hours and haven't found anything.

Thank you VERY much to any and all help!
Andy K
 
Quick edit, my SQL Query should've been "SELECT emp.LastName FROM
EmployeeMaster emp, Resources r WHERE r.UserType = 5 AND emp.empID =
r.empID"

Any suggestions?
 
Andy, is the value of 5 hardcoded? If so , you can just create a dataview
on the child table directly, set the rowfilter to UserType = 5 and bind to
the view. The other thing you can do is grab the row you want in the
parent, use GetChildRows and bind to its results. If you need this to be
dynamic though, populate one control based on what the other one has in it
(so if the 5 is switched to 6 you get only the values for 6 for example) you
can use a BindingManagerBase/BindingContext to do it
http://www.oreilly.com/catalog/adonetian/chapter/ch12.pdf
 
Thanks for the reply.

"If so , you can just create a dataview on the child table directly,
set the rowfilter to UserType = 5 and bind to
the view."

Right, but the problem is that the data that I want to show is not IN
the child table, but in the parent table. I need a filter that will
give me the first and last name of all employees (the employee parent
table) where their UserType (in the child table Resources) is equal to
5. So I see what you're saying, but that dataview would not get me the
data that I actually want to see (the first and last names in
Employees); it would only let me view the rows in the child table.

I have a feeling that the dataview should be on the parent table since
that table has the data that I actually want to see, but I'm not sure
how to set the RowFilter to say "where your related table, Resources,
has those employees as having a UserType of 5."

Is this DataViewManager territory, maybe?

Again, thank you for all help.
Andy
 
Thanks for the response, Cor.

So basically, what we're looking at is that there isn't really any
support for this sort of query in .Net, right? I guess I'm a little
surprised; I didn't see too many people asking something like this
through my searches, and it seems relatively simple; I figured I must
have been doing something wrong! I guess not.

Thanks again.
Andy
 
If you look on our website you can see a lot of relations used.

The only thing is that you cannot set simple bind that AFAIK .

From a relation you can get in the parent back a collection of datarows. I
never tried it really to set that as a datasource, because my experience
with all datasources beside the datatable is in 1.x bad.

In the 2.0 version the use of more collections related as datasource is
improved. As I told already, I did not try that yet. So I can only tell that
I if that now is easy.

Cor
 
Back
Top