DataSet TableNames from query?

[cybvek]

Hi everyone!

 

I have a big problem,

 

When I fill a dataset with an adapter, then it uses table1, tabl2, etc. tablenames by default, but I want that thouse tablenames fill from my query what I used, how can I do that but not manual?

 

 

regards,

 

viktor

[Nerseus]

You can't do it automatically. The best you can do is change Table to some other word, but all subsequent tables in the DataSet will still have a number at the end, but use your new table name. So if you did:

da.Fill(ds, "NewTableName")

you'd get NewTableName1, NewTableName2, etc.

 

There's no way for ADO.NET to pull out the table name from your query to put in the DataSet automatically. A resultset from SQL Server or Access (using a SELECT) only contains columns and a "table definition" that matches those columns. You may be doing "SELECT * FROM MyTable" but you might also be doing "SELECT * FROM MyTable INNER JOIN MySecondTable". Which table name is right? What if you selected from a View (a SQL Server object that only looks like a table), which might join two or three tables?

 

Manual is the only way to go in this case. You can automate if you'd like, but it's still manual in some way. For instance, you could have every query return the table name through a simple SELECT statement just before the real select, as in:

SELECT 'MyFirstTable'
SELECT * FROM MyFirstTable

SELECT 'MySecondTable'
SELECT * FROM MyFirstTable INNER JOIN MySecondTable...

 

After the fill, loop through the even numbered tables and set their name to the previous table's row's value. Then delete those odd numbered tables.

 

At my work, we define all query results through XSDs. We use a matching metadata file for the XSD (DataSet) that contains table names (the order in the XML metadata must match the order returned from a Stored Proc). The meta data can contain other information, too. But it's all up to you to code it.

 

-Ner

[cybvek]

Hi!

 

Thanks your answer but the situation changed. My problem was that, my "dataprovider" communicate with our ODBC

driver and I didn't know I don't have to send back the "real" tablenames to the driver, but thanks

 

Regards,

 

V.