Multi Table Join and retrieving the DB-ORIGINAL table name...

[Matt]

I have a multi table join...i.e.

string sql = "select customers.name, customers.id, address.street,
address.city
from customers, address
where customers.id = address.id"

then I execute the following code:

Dataset ds = new DataSet();
System.Data.SqlClient.SqlDataAdapter dataAdapter = new
System.Data.SqlClient.SqlDataAdapter();
dataAdapter.SelectCommand = new System.Data.SqlClient.SqlCommand(sql,
dbConnection);
int updateCount = dataAdapter.Fill(ds);

QUESTION: Although I can get the column names from the dataset there
seems to be no way to retrieve the ORIGINAL table names from the
database...

The SQL statement can be totally arbitrary, so the adapter has to tell
me what the table names were...Can this be done???

Thanks

Matt
(e-mail address removed)

 

[William \(Bill\) Vaughn]

That's by design. The DataAdapter is designed to work with a single root
table.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[William Ryan]

I'd highly recommend changing your query, and you'll solve
your problem as well. Pulling over joined tables is
extremely costly in terms of size and speed. You pull
over a lot of data that you don't need per se.

Instead, run query both tables individually , then pull
them into tables individually named after the real table
names. Link them togethter with a DataRelation object and
you'll 1) Still have both tables in the DataSet 2) Know
there respective names 3) Use fewer resources.

Since putting both tables in a DataSet via a DB Join
obscures there respective names, you'll know what they are
by their position. The first table to be filled will have
an index of 0 in the DataSet.Tables collection.
It's 'name' will be what you name the variable, but you
know the table names by virtue of the query (unless you
are using stored procs).

I hope this helps.

Bill

 

[Matt Tatro]

Thanks for the responses...

Changing the query is not an option (and I disagree it is more costly to
returned qureies which have been properly joined and executed by the
database engine. Joining them later in memory has to be by far more
costly than on the database server...) because the queries are totally
arbitrary and created by end users.

Seems to be pretty limiting to not be able to return joined datasets and
figuring out what the table names where...

Matt
(e-mail address removed)

 

[MSFT]

Hi Matt,

You may change your Select statement to:

string sql = "select customers.name as 'customers.name', customers.id as
'customers.id', address.street as 'address.street',address.city as
'address.city'
from customers, address
where customers.id = address.id"

Now you can get the source table name from the column name.

Hope this help


Luke
Microsoft Online Partner Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no

rights.)

 

[Matt]

OK, that works...but, that is really not a very good solution.

I sure wish the DataSet had enough smarts to properly populate the
REAL table name, not the imaginary one...

Using this solution one still has to parse out the table name using
the Split function... :-(

Nevertheless, thanks!

Matt...
(e-mail address removed)

 

[Renaud Langis]

The DataSet class is inheritable. You could use that to add properties
to store those table names. Yet, table names are not always easy to
get. For example

Select ID
From (Select A.ID
From TABLE1 A, TABLE2 B
Where <some condition>
Union
Select B.ID
From TABLE3 A, TABLE4 B
Where <some condition>
) Z

 

[Matt Tatro]

Agreed, they are not easy to get. The table name at times should be
coming from the oledb or sql provider itself...

However, you have a good point on the DataSet inheritance. I will give
that a try. Thanks for the tip!

Matt...
(e-mail address removed)