How to: Can you build a Typed DataSet for a stored procedure w/o using a DataAdapter?

[Eric]

If you want to create a DataSet, you can drag tables from Server explorer
into it, but not stored procedures. Is there any other way than using a
DataAdapter and calling Generate DataSet to get a typed dataSet that uses
sprocs instead of tables?

p.s. Does anyone know if in VS 2005 you will be able to drag and drop sprocs
onto a DataSet object?

 

[David Browne]

If you want to create a DataSet, you can drag tables from Server explorer
into it, but not stored procedures. Is there any other way than using a
DataAdapter and calling Generate DataSet to get a typed dataSet that uses
sprocs instead of tables?

Yes. It may seem terribly obvious, but use the DataSet designer and just do
it by hand.

If you don't want to do that, write a view which returns the same row type
and drag that into the DataSet designer. I always write views to "publish"
the row type returned by functions and stored procedures.

p.s. Does anyone know if in VS 2005 you will be able to drag and drop
sprocs
onto a DataSet object?

The problem is that it's difficult to get SqlServer to return table metadata
information from a stored procedure. It tries to run the proc with SET
FMTONLY ON, but it's complicated and unreliable.

David

 

[Robbe Morris - Microsoft MVP C#]

If you want do this without typed datasets but still get intellisense in
your returned results, you could utilize this ADO.NET code generator
(includes source code) at eggheadcafe.

http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp

 

[Mythran]

If you want to create a DataSet, you can drag tables from Server explorer
into it, but not stored procedures. Is there any other way than using a
DataAdapter and calling Generate DataSet to get a typed dataSet that uses
sprocs instead of tables?

p.s. Does anyone know if in VS 2005 you will be able to drag and drop
sprocs
onto a DataSet object?

What?!? I can drag-n-drop sprocs from SQL Server 2000 onto a DataSet just
fine, and it creates the typed dataset with the results the stored procedure
returns along with their data types just as if it were a table. I'm using
VS 2003...what are you using?

Mythran

 

[Eric]

--- Stuff deleted for Brevity

The problem is that it's difficult to get SqlServer to return table metadata
information from a stored procedure. It tries to run the proc with SET
FMTONLY ON, but it's complicated and unreliable.

Tell me about it. In Crystal Reports, when you attempt to set the
DataSource.Location Property of a report in code, if it's a sproc, it
actually executes the sproc in question whereas if it's a table it makes
some quick system calls to get metadata. I dont even think SET FMTONLY ON
was used. For a report against a giant database that was.....problematic,
especially if there were multiple tables in the report.