title: PushStreamContent using SqlDataReader.ExecuteReaderAsync to stream CSV or delimited file to client
body: <p>I'm trying to utilize WebAPI to stream a large amount of data from SQL Server to a delimited file without waiting for the entire result set to return from the database.  I'm really terrible with Async and Await, haven't wrapped my head around it.  It seems what I'd like to do should be possible using the PushStreamContent and the SqlDataReader async methods.<br>
  This is at my DataAccessLayer:</p>
  
  <pre><code>   Public Async Function ExecuteToResponseStream(command As SqlCommand, responseStream As IO.Stream) As Threading.Tasks.Task
        Using responseStream
           Dim newConString As String = _conString
           Using con As SqlConnection = New SqlConnection(newConString)
              Await con.OpenAsync()
              'Add params
              command.Connection = con
  
              Using ms As IO.MemoryStream = New IO.MemoryStream
                 Using ts As IO.StreamWriter = New IO.StreamWriter(ms)
  
                    Using reader As SqlDataReader = Await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess)
                       While Await reader.ReadAsync()
                          Dim loValues As New List(Of String)
                          For i = 0 To reader.FieldCount - 1
                             loValues.Add(reader(i).ToString)
                          Next
                          Await ts.WriteAsync(String.Join(vbTab, loValues.ToArray))
                          Await ms.CopyToAsync(responseStream)
                       End While
                    End Using
                 End Using
              End Using
           End Using
        End Using
     End Function
  </code></pre>
  
  <p>My repository has a method with signature of:
      Function ExportAsync(id As String, responseStream As IO.Stream) As Task</p>
  
  <p>Finally, the WebAPI Client:</p>
  
  <pre><code>   Public Function Export(id As String) As HttpResponseMessage
        Dim resp As New HttpResponseMessage(HttpStatusCode.OK)
        Dim onStreamAvailable As Action(Of IO.Stream, HttpContent, TransportContext) = Async Sub(responseStream, content, context)
          Await repository.ExportAsync(rc, responseStream)
       End Sub
  
        resp.Content = New PushStreamContent(onStreamAvailable)
        resp.Content.Headers.ContentType = New Headers.MediaTypeHeaderValue("application/octet-stream")
        resp.Content.Headers.ContentDisposition = New Headers.ContentDispositionHeaderValue("attachment")
        resp.Content.Headers.ContentDisposition.FileName = "Somefilename"
  
        Return resp
     End Function
  </code></pre>
  
  <p>The client function seems to exit appropriately and return the response, eventually.  The eventual response to the browser does indeed look like an attachment, it's empty, I suck at streams, but it seems like it not happening in that magical asynchronous way.</p>
  
  <p>Also, it seems to me that this would be a somewhat simple exercise though I can't find a good implementation out there on the webs.  Perhaps I'm going about this completely wrong. </p>