Dataset from dynamic sql

[John]

I'd like to create a typed dataset based on a db-defined structure (ie
defined in records in the db, not in metadata!)

I tried a stored procedure (SQL Server 2005) that's creating some dynamic
sql based on the queried structure, something like:

create procedure test as
exec ('select cast(1 as int) as [first field], select cast(1 as bigint)
as [second field]')
return 0

(the real sproc generates the string dynamically of course). However I can't
seem to create a typed dataset from this!

Any hints?

The other approach I guess would be to just build the XSD file based on
query result, bypassing the visual designer altogether..? Loading and saving
arn't an issue - I just need the structure.

Thanks,

John

 

[Guest]

Hi John,

In this case, I would recommend youto create a XSD schema to represent the
data structure and then create a typed dataset based on this XSD. However,
in performance and security point of view, I don't recommend to use dynamic
sql in production environment. Any workaround for your solution design?

Raymond

 

[John]

Thanks for the help,

Any cool tools for making VS2005-esq XSD schemas, or it is reasonably safe
to just hack something together from the look of existing ones? A little exe
that turned a select statement into an XSD would be ideal.

I know perfomance and security of dynamic SQL is poor - it's not for use in
production, just to assist other developers at design time.

Thanks,

John

 

[John]

Ah it's easy:

DataSet ds = new DataSet("MyDataset");

// Add the query result to ds using a dataadapter

ds.WriteXmlSchema("c:\\myxsdfile.xsd");

Used to have to muck about with CodeDom's in VS2003, but nothing more than
this in VS2005.

John