Simultaneous reading and writing data with Web API

Topics: ASP.NET Web API
May 25, 2012 at 11:46 PM

Is there an easy way to read a big portion of data from a database for example and simultaneously stream it to the client with Web API? In other words, start streaming right after the first X row(s) of the data-set are retrieved.

public IEnumerable<string> Get()
    for (var i = 0; i < 10; i++)
        yield return "Line number " + i;

In the example above you may assume that data will be streamed right away, when first row become available. But this is not the case. It starts streaming only after all the items are collected; in this case after 10 secs.

May 25, 2012 at 11:52 PM
Edited May 25, 2012 at 11:53 PM

Which version of bits are you using? Beta or the latest?

[updated] Also, is this a Web hosted scenario?

May 25, 2012 at 11:54 PM
Edited May 25, 2012 at 11:57 PM

I'm currently using Beta with ASP.NET MVC 4 Web API project template, .NET 4.0. It is hosted under IIS 7.5.

May 26, 2012 at 4:37 PM

There are two ways you can do this depending on what you get back from the database but in either case will you have to return an HttpResponseMessage and create the response yourself. Something like this pseudo-code:

public HttpResponseMessage Get()
    HttpResponseMessage response = Request.CreateResponse();
    response.Content = <...>
return response; }

1) If you have a readable stream then you can create an StreamContent and return that. We will then asynchronously read from that stream and pump it to the network

2) Otherwise you can create a PushStreamContent (not available in beta but you can read about it here [1]) which gives a stream to which you can write to.



May 27, 2012 at 5:30 PM
Edited May 27, 2012 at 8:34 PM

Can you please provide an example how to push data to the output stream in non-blocking manner with StreamContent and SqlDataReader? Here is a scenario:

Should I implement a custom data stream class (based on Steam) which will read data from the database, initialize it and pass it to the StreamContent constructor?

May 27, 2012 at 11:44 PM

I don't have a DB  that I can test against and I am on vacation at the moment so I apologize for the brevity but it should look something like this:

    public class ValuesController : ApiController
        private static readonly string queryString = "SELECT OrderID, CustomerID FROM dbo.Orders;";
        private static readonly string connectionString = "";

        // Note that even though this is a synchronous action the actual response content
        // generation will happen asynchronously in the PushStreamContent.
        public HttpResponseMessage Get()
            HttpResponseMessage response = Request.CreateResponse();

            // Create push content with a delegate that will get called when it is time to write out 
            // the response.
            response.Content = new PushStreamContent(
                async (outputStream, httpContent, transportContext) =>
                        // Create a DB connection
                        using (SqlConnection connection = new SqlConnection(connectionString))
                            // Set up command for the DB
                            SqlCommand command = new SqlCommand(queryString, connection);

                            // Execute the command and get a reader
                            using (SqlDataReader reader = command.ExecuteReader())
                                // Read rows asynchronously, put data into buffer and write asynchronously
                                while (await reader.ReadAsync())
                                    // TODO: Gather the read data into a byte buffer
                                    byte[] buffer = new byte[256];

                                    // Write out data to output stream
                                    await outputStream.WriteAsync(buffer, 0, buffer.Length);
                        // Close output stream as we are done

            return response;

Hope this helps,



Jun 3, 2012 at 11:57 PM

Hanrik, thanks for the sample. Though, here is the problem.. the query sent to the server returns two results sets - one with the last modified date and another one with data items. I need to be able grab that date and set Last Modified HTTP header before proceeding with writing data to the outputStream. But inside onStreamAvailable delegate new headers are no longer accepted :( I know, this this by design.. but that makes PushStreamContent useless when you work with multiple result sets or else you will end-up splitting db queries, sending multiple request in different places in code (one inside onStreamAvailable delegate, and another outside), if you're open db connection outside of onStreamAvailable delegate you will have to close it properly (btw, wouldn't it involve heavy synchronization stuff if onStreamDelegate is going to be executed on a dedicated thread?)

How would you approach this issue, create custom PushStreamContent class which would allow setting headers inside onStreamAvailable delegate?