SqlDataSource with multiple data tables

[scartin]

I'm attempting to work with SqlDataSource to pull values from a
database and populate a form, which by itself is no problem. However,
I'm trying to visualize how to also iterate through values on a joined
table.

My situation is that there is a form representing a Rule filled out
mostly from values contained in one table, but that form also contains
a ListBox that allows multiple selection which represents the locations
this rule applies to. The rule can be applied to any, all or zero
locations. I'd like to set this up such that when a Rule is
retrieved, the Locations are retrieved in the same query (I know I can
accomplish this with 2 separate SqlDataSources), and all values are
populated at once.

The problem I have is that a JOIN seems to just repeat the form data
across all rows, when I really only need the Location ID. If I execute
two SELECT statements within a single SqlDataSource without any
relationships defined with the query, can I access both the returned
tables and create those relationships in the code?

I don't need the SqlDataSource's INSERT, UPDATE or DELETE commands for
this function, as the work required for those is a bit more than can be
handled automatically. But I would like to get this part working with
minimal trips to the database, since it just seems unnecessary to
execute these two queries independently. What is the best option for
doing this? Revert back to DataSet's ability to store and retrieve
multiple tables and the ability to create DataRelations?

Thanks very much for your time.
Simon Cartin

 

[Miha Markic [MVP C#]]

One way is to use lookup controls while the other way is to create a new
datasource class that would combine both tables (actually just a wrapper on
both tables).
There is also a product that executes limited sql commands over tables (it
is advertised in this newsgroup from time to time) that you might find
useful.

 

[scartin]

Thanks for your reply. In the end, I just used a second query since we
determined that the performance impact of doing so was going to be
negligible in the context of our application.

For what it's worth, one interesting idea that came out of discussions
was to convert the related table data to XML using the SQL FORXML
command, and pass that back as a single string field. Then either
construct a DataSet from that XML, or simply parse it to populate the
Locations list box.