Returning objects from CLR Stored Procedure

Hello All

I have a few questions about how to return a DataSet object from a CLR Stored Procedure as an output parameter:

Can this be done? I am having problems at execution time with declaring an output parameter of the type "DataSet". "Cannot find type DataSet" message, compiler/build does not complain, error occurs at execution time.

This seems like one of the most obvious ways to create a more object-oriented approach using the technology offered in the 2005 product stack
(.NET2.0/VS2005/SQL2005),
Do you agree? if not, what other design would you use.

If this cannot be done, returning the serialization of the DataSet would be my next preference. There appears to be extra steps required to pregenerate serializer assemblies with sgen, any good detailed references how to do this?

I hope my questions are clear and I will provide code examples or clarification on request.

Look forward to feedback, Thanks in advance

A .NET proc has to have datatypes compatible with T-SQL types, as the .NET code will be wrapped in a T-SQL proc. So returning a DataSet as output param is out of the question.

Is there any particular reason you need to return a DataSet? Can't you just return a datareader through the "normal" SqlPipe.Send(DataReader)?

As for returned a serialized DataSet, the same issues as with the dataset as an output param still stands; if you want to use an output param - the type has to be SQL compatible.

As for Xml serialization in general, you need to pregen the serialization assembly by using sgen (exactly as you say). So what you do is, you run sgen over your assembly/class which you serialize and catalogue the sgened assembly oin SQL Server.

Niels
|||

Thank you for your feedback Niels

//So returning a DataSet as output param is out of the question.
I thought that was the case, just wanted confirmation

//Is there any particular reason you need to return a DataSet?
Basically, to create an Object Oriented Data Access Layer in an n-tier architecture.
Stored procedures capable of returning objects, in my opinion, would be a significant progammability enhancement
The DataSet object containing multiple result sets seemed like a good mechanism

//Can't you just return a datareader through the "normal" SqlPipe.Send
//(DataReader)?
Isn't there an 8K limit on the pipe? My objects can potentially be larger

//As for returned a serialized DataSet, the same issues as with the dataset as
//an output param

Can I put the XML in a SQLCHARS or SQLBYTES variable and return it in an output parameter?

//run sgen over your assembly/class which you serialize and catalogue the sgened assembly
I thought this was what I did, will try again

Thanks again
Scott

|||

//Isn't there an 8K limit on the pipe? My objects can potentially be larger

The pipe can send whatever size. However, you can not send objects. So what you'd send is either the raw data or XML. In either case you'd need to do some processing client side, if you want OO.

////As for returned a serialized DataSet, the same issues as with the dataset as
////an output param

//Can I put the XML in a SQLCHARS or SQLBYTES variable and return it in an output //parameter?

Sorry, I had a brain-fart when I wrote the above about serialized data set. If you serialize it into XML you can just return the xml as an output param.

Niels

|||Scott,

Take a look at this link:
http://www.codeproject.com/cs/database/PassingArraysIntoSPs.asp

They take DataTables and Arrays and convert them to binary data and pass them in as IMAGE types. I'm sure you can extend this idea to have that IMAGE type passed back to .NET code where you can convert it back into DataTable format.

I know this is working with DataTables instead of DataSets but perhaps it is enough to allow you to do what you need to do.

As a worst-case scenario you could always have pass in some connection information and IDs and have your SP communicate with a Web Service to get the real data passed in and out.

-Shane