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.
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.
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.
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"
