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?

score 0 · Answer 1 · answered Mar 04 '16 at 23:47

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

score 0 · Accepted Answer · edited May 23 '17 at 11:45

0

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.

edited May 23 '17 at 11:45

Community

1
1

answered Aug 02 '11 at 08:23

Ladislav Mrnka

360,892
59
660
670

OK, now I do understand how to deal with the streams. How can I close the connection myself? My service is InstanceContextMode.PerCall. Closing the connection at the end of the function throws error "The socket connection was aborted". – Peter Meinl Aug 04 '11 at 06:39
You can try to make your service disposable and close connection when dispose is called (default instance provider does it when instance is released). – Ladislav Mrnka Aug 04 '11 at 08:20

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

2 Answers2