What is the clean way return an SQLFileStream from a WCF Service?

Question

We download documents stored in SQL Server using FileStream from a WCF service by passing back the SqlFileStream object to the client. For this to work we leave the DB Transaction and Connection in the service open. We close the SqlFileStream in the Client.

'Service
Public Function GetDokumentStream(dokumentId As Integer) As System.IO.Stream Implements IDataService.GetDokumentStream
  Dim cnx = New SqlConnection(...)
  cnx.Open()
  Dim tran = cnx.BeginTransaction()
  Dim cmd As New SqlCommand("Select Dokument.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() from Dokument where ID= @ID", cnx, tran)
  cmd.Parameters.AddWithValue("ID", dokumentId)
  Using rdr = cmd.ExecuteReader()
    If rdr.Read() Then
        Dim pathName = rdr(0).ToString()
        Dim context = CType(rdr(1), Byte())
        Dim sqlFileStream As New SqlFileStream(pathName, context, IO.FileAccess.Read)
        Return sqlFileStream
    Else
        '...
    End If
  End Using

'Client
Dim sqlFileStream = _satDataService.GetDokumentStream(dokumentInfo.DokumentID)
Using fileStream As New IO.FileStream(fileName, IO.FileMode.OpenOrCreate)
    sqlFileStream.CopyTo(fileStream)
    sqlFileStream.Close()
End Using

Must we implement something to manually close the Connection in the service or does the WCF infrastructure clean up automatically? Is it OK to close the returned Stream in the client or should we better create a complex type for the Stream implementing IDisposable?

Alternatively we could copy the SQLFileStream to a MemoryStrean, close the connection and return the memory stream:

Using cnx = New SqlConnection(...)
    cnx.Open()
    Using tran = cnx.BeginTransaction()
        Dim cmd As New SqlCommand("Select Dokument.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() from Dokument where ID= @ID", cnx, 

tran)
        cmd.Parameters.AddWithValue("ID", dokumentId)
        Using rdr = cmd.ExecuteReader()
            If rdr.Read() Then
                Dim pathName = rdr(0).ToString()
                Dim context = CType(rdr(1), Byte())
                Dim context1 = rdr(1)
                Dim sqlFileStream As New SqlFileStream(pathName, context, IO.FileAccess.Read)
                sqlFileStream.CopyTo(memoryStream)
                _trace.InfoFormat("Returning file {0} size {1}bytes", pathName, memoryStream.Length)
                memoryStream.Position = 0
                Return memoryStream
            Else
                Throw New ApplicationException("Dokument File not found")
            End If
        End Using
    End Using
End Using

Does this solutions use more memory on the server that returning the SqlFileStream directly? Or does WCF internally copy the SqlFileStream to memory anyway?

Answer 1

You must deal with your connection and transaction yourselves. It is not responsibility of WCF to handle your internal implementation of the operation. The stream passed as parameter or returned from operation is by default closed by WCF.

Your second example will load all data from the stream to memory of your service host. It will spoil the purpose of streaming on the service side.