You cant 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")
youd get NewTableName1, NewTableName2, etc.
Theres 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 youd like, but its 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:
Code:
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 tables rows 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 its all up to you to code it.
-Ner
