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

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.Op开发者_如何学Cen()
    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?

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.

I came across this problem myself and created a wrapper class around SqlFileStream that uses generic typing and reflection to allow you to map the row to a type and access the FILESTREAM column.

You do not need to write any SQL statements, it is all generated internally using the mapping information.

It also wraps the connection and transaction so that once the stream is disposed, the transaction is committed and the connection disposed, making it safe to return from a WCF Service.

It's available on https://github.com/RupertAvery/WrappedSqlFileStream