Custom streaming to read BLOBS from MS-SQL - How should I handle the connection?

Question

I have a custom stream that I am using with WCF for large BLOBs from the database. It reads the data in chunks.

What is the best way to handle the connection for the stream? Should I just open it at the construction or open/close it with each chuck read?

Thanks.

Answer 1

• Large BLOBs and UPDATETEXT (ADO.NET)
• Streaming BLOBs (ADO.NET)
• Working with binary large objects (BLOBs)
• Large Data and Streaming (WCF)

Filestream in SQL Server 2008 will save the files on the filesystem giving you streaming capabities with the use of public filesytem API along with preferred performance over normal BLOB.

From the post - Rule of thumb:

Data > 256K - Consider Filestream
Data < 256K - Keep using BLOB

Answer 2

I would load the entire blob into a memory stream then let WCF handle the Streaming and Chunking. You can enable streaming in the transportBindings, or look into using MTOM.

[ServiceContract]
public class ContentService
{
  [OperationContract]
  public Stream GetBlob(int id)
  {
    byte[] myBlob = GetBytesFromDatabase();
    return new MemoryStream(myBlob);
  }
}

If you using SQL Server 2008 try sending the stream directly through to the WCF Client

[ServiceContract]
public class ContentService
{
  [OperationContract]
  public Stream GetBlob(int id)
  {
    return GetStreamFromDatabase(id);
  }
}

See this link on Streaming Messages with WCF