SQLDataAdapter filled from temporary table

[Willy Esteban]

How do I configure a SQLDataAdapter to use the results of a temporary table
from a stored procedure?
I also want to produce a strongly typed dataset from the SQLDataAdapter.

The VS wizard states "Invalid object name '#MyTempTable'" The table mappings
is empty.

 

[David Browne]

How do I configure a SQLDataAdapter to use the results of a temporary table
from a stored procedure?
I also want to produce a strongly typed dataset from the SQLDataAdapter.

The VS wizard states "Invalid object name '#MyTempTable'" The table mappings
is empty.

VS has only a limited ability to retrieve tabular metadata from stored
procedures, and as you have discovered, temp tables are beyond those limits.
You should have a dummy table or view in your database which has the same
structure as your temp table. In essence this "publishes" the temp table
metadata to the world and lets you easily build strongly-typed datasets from
it.

In SQL2k , if you change your SP to a table-valued user-defined function,
and then wrap the UDF in a view, and perhaps also in a stored procedure,
then you won't have to keep your dummy table up to date. It will all be
driven off the UDF.

like

create function dbo.MytableValuedFunction(@i int, @a varchar)
....

create view v_MyTableValuedFunction
as
select * from dbo.MyTableValuedFunction(null,null)

David

 

[Willy Esteban]

Thanks, David.
The creation of a dummy table was in my plans but I just wanted to make sure
that I wasn't barking up the wrong tree. I really don't like the alternative
because the purpose of the table is not that obvious (it ain't for data it's
for convenience) and it therefore requires detailed documentation that
nobody (including me) ever reads.

 

[bruce barker]

you don't need to creat a dummy table, you can create the schema directly
with the schema tool.

 

[David Browne]

you don't need to creat a dummy table, you can create the schema directly
with the schema tool.

In either case you have the row type for the table defined in 2 places. The
temp table and the dummy table, or the temp table and the schema file. You
still must manually marshall changes over.

If you want to create your dataset schema automatically, you must define the
table in the database.
Or you can create and maintain the schema file manually.

David

 

[David Sceppa]

Willy,

The DataAdapter Configuration Wizard is calling
DataAdapter.FillSchema under the covers to retrieve schema
information about the procedure without actually executing it.
However, since the temporary table does not exist without
executing the stored procedure, the call throws the error you
described.

The suggestions that David and Bruce offered are your best
alternatives.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.

 

[Willy Esteban]

Bruce, thanks for the response. Your method was my second choice. As a
matter of fact I was examining all that I had to do to implement it inside
VS when I got David's response. After some thought, I opted for the dummy
table approach. It is, I think, easier and more intuitive to maintain the
schemas in the same environment.

I had also considered actually using the dummy table instead of the temp and
marking each row with a GUID created at the start of the procedure and then
deleting them after the result set was processed and built; but that too has
some disadvantages.

Thank you all for the responses.

 

[Willy Esteban]

Yes, as I stated earlier, I opted for the dummy table approach.

I guess I understand it now but at first it seemed unreasonalble that it
could not figure out the schema. After all, there is a CREATE TABLE
statement in there.

I often add ad-hoc columns to result sets and the FillSchema seems to figure
those out without a problem even thought they do not exist. I just thought
the temp table would be considered in that same manner.

Willy