Streaming SQL Data

20 Apr 2006 4:19 PM

ewellnitz

I have a large SQL result set that I am returning to a client from a
WCF Service. The client and the service both are configured to use
TransferMode.Streamed, and the operation signiture is:

System.IO.Stream GetData();

Within the service, I am reading all the data using a SqlDataReader and
writing the data out to the return stream, and then returning the
stream.

The problem with the above solution is that the service reads the
entire result set into the stream, and then returns the stream.

What I would like to be able to do is return the underlying stream the
SqlDataReader uses to the client, or return the stream to the client
while the sqldatareader is still pumping data into it within the
service.

The behavior I would like to have is similar to the behavior you get
when you return a FileStream from a WCF Service. On the client, you
are immediately able to start accessing lines within the file
regardless of file size.

Any suggestions?

-Eric Wellnitz (ewelln***@solarc.com)

23 Apr 2006 12:04 AM

JT

Hi Eric,
Perhaps you might be able to adapt the datareader's
CommandBehavior.SequentialAccess to do what you want. An example that deals
with retrieving Blobs is at

--
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconobtainingblobvaluesfromdatabase.asp

Here is an excerpt from the page:

The default behavior of the DataReader is to load incoming data as a row as
soon as an entire row of data is available. Binary large objects (BLOBs) need
to be treated differently, however, because they can contain gigabytes of
data that cannot be contained in a single row. The Command.ExecuteReader
method has an overload which will take a CommandBehavior argument to modify
the default behavior of the DataReader. You can pass
CommandBehavior.SequentialAccess to the ExecuteReader method to modify the
default behavior of the DataReader so that instead of loading rows of data,
it will load data sequentially as it is received. This is ideal for loading
BLOBs or other large data structures. Note that this behavior may differ
depending on your data source. For example, returning a BLOB from Microsoft
Access will result in the entire BLOB being loaded into memory rather than
loading data sequentially as it is received.

John

Show quote

"ewelln***@solarc.com" wrote:

> I have a large SQL result set that I am returning to a client from a
> WCF Service. The client and the service both are configured to use
> TransferMode.Streamed, and the operation signiture is:
>
> System.IO.Stream GetData();
>
> Within the service, I am reading all the data using a SqlDataReader and
> writing the data out to the return stream, and then returning the
> stream.
>
> The problem with the above solution is that the service reads the
> entire result set into the stream, and then returns the stream.
>
> What I would like to be able to do is return the underlying stream the
> SqlDataReader uses to the client, or return the stream to the client
> while the sqldatareader is still pumping data into it within the
> service.
>
> The behavior I would like to have is similar to the behavior you get
> when you return a FileStream from a WCF Service. On the client, you
> are immediately able to start accessing lines within the file
> regardless of file size.
>
> Any suggestions?
>
> -Eric Wellnitz (ewelln***@solarc.com)
>
>

16 May 2006 12:33 PM

ewellnitz

Thank you for the response, John.

I got streaming working by simply wrapping the SqlDataReader within a
System.IO.Stream class. I overloaded the Read method of the stream to
read the data from the SqlDataReader into the buffer chunks at a time.
My operation is defined as:

[OperationContract]
Stream GetData(string);

When I host the WCF service using a console app (ServiceHost), the
stream works fine. The client immediately returns from the GetData
call and can then start processing the stream.
The only issue I have remaning is that I can't seem to get the stream
to act properly when the service is hosted within IIS. The operation
seems to wait until the stream is completely populated and closed by
the service before it returns from GetData.

Has anyone successfully been able to retrieve a stream from a WCF
service hosted within IIS?