lundi 11 mai 2015

PushStreamContent using SqlDataReader.ExecuteReaderAsync to stream CSV or delimited file to client

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.
This is at my DataAccessLayer:

   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

My repository has a method with signature of: Function ExportAsync(id As String, responseStream As IO.Stream) As Task

Finally, the WebAPI Client:

   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

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.

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.

Aucun commentaire:

Enregistrer un commentaire