How to get the DataSet to recognize the table names from stored procedure?

I have a stored procedures which returns about 5 tables and I am returning them something like this:

select <fields> from Products where ProductId = @ProductId
select <fields> from RelatedProducts where ProductId = @ProductId
select <fields> from MetaData where ProductId = @ProductId

well, you get the gist. Now in the Dataset, If I do this:

DataSet ProductDs = DAL.RetreiveProductMetadata(someInteger);

ProductName = DataSet.Tables["Products"].Rows[0]["columnName"].ToString();

Its throwing up an exception... well, error saying TableName is null and upon debugging, turns out, the table names are actually named as "Results1", "Results2" and so on.

I tried changing stored proc to:

select * From products AS Products

no effect.

How can I get C# to detect and use the Table Names from the database?

ps: I have all sorts of Coalesces and cases in the field, that shouldn't present a problem right?

Also, tried googling and 开发者_开发问答banging head - neither worked.

It's actually possible, you just need to load the table scheme first, which contains no data but information about the table structure including table names:

adapter.FillSchema(dataset, SchemaType.Source);
adapter.Fill(dataset);

Seems this doesn't work on queries that are contained in the database (like you might use in MS Access), but it does the job for a regular table.

You can't. The table name has no meaning in the result set because a query can contain many tables.

You should know what your resultsets are and should not have to derive table names.

Otherwise,

select 'Products' AS ThisTable, <fields> from Products where ProductId = @ProductId

Or JOIN first and unpick later

Or define your dataset.xsd etc up front and map according.

The only way to retrieve a table by name from a dataset is: if you name it when filling from an adapter or manually name them later one table at a time:

adapter.fill(dataset, "nameoftable")

now when you access the ds in the future you can access by name; ds.tables("nameoftable").rows etc.

or name them later.

_ds.tables(0).tablename = "nameoftable"