How to make streams from BLOBs available in plain old C# objects when using SqlDataReader?

This is the scenario:

• We store files, e.g. relatively large documents (10-300MB), in blobs in our MSSQL database.
• We have a very small domain model so we use the clean SqlDataReader approach for our repository, instead of an ORM, to avoid unnecessary dependencies.
• We want to use the objects in server context on ASP.NET/ASP.NET MVC web pages.
• We do not want to temporarily store the blobs in byte[], to avoid high memory usage on the server

So what I have been doing is to implement my own SqlBlobReader. It inherits Stream and IDisposable and during instantiation we must supply a SqlCommand containing a query that returns one row with one column, which is the blob we want to stream, of course. Then my C# domain objects can have a property of type Stream which returns a SqlBlobReader implementation. This stream can then be used when streaming to a FileContentStream in ASP.net MVC, etc.

It will immediately do an ExecuteReader with SequentialAccess to enable streaming of the blob from the MSSQL server. This means that we must be careful to dispose the stream ASAP when using it, and that we always lazily instantiate SqlBlobReader when it is needed, e.g. using a repository call inside our domain objects.

My question is then:

• Is this a smart way of achieving streams of blobs on plain old domain objects when using SqlDataReader instead of an ORM?
• I'm not a ADO.NET expert, does the implementation seem reasonable?

SqlBlobReader.cs:

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace Foo
{
   /// <summary>
   /// There must be a SqlConnection that works inside the SqlCommand. Remember to dispose of the object after usage.
   /// </summary>
   public class SqlBlobReader : Stream
   {
      private readonly SqlCommand command;
      private readonly SqlDataReader dataReader;
      private bool disposed = false;
      private long currentPosition = 0;

      /// <summary>
      /// Constructor
      /// </summary>
      /// <param name="command">The supplied <para>sqlCommand</para> must only have one field in select statement, or else the stream won't work. Select just one row, all others will be ignored.</param>
      public SqlBlobReader(SqlCommand command)
      {
         if (command == null)
            throw new ArgumentNullException("command");
         if (command.Connection == null)
            throw new ArgumentException("The internal Connection cannot be null", "command");
         if (command.Connection.State != ConnectionState.Open)
            throw new ArgumentException("The 开发者_开发知识库internal Connection must be opened", "command");
         dataReader = command.ExecuteReader(CommandBehavior.SequentialAccess);
         dataReader.Read();
         this.command = command; // only stored for disposal later
      }

      /// <summary>
      /// Not supported
      /// </summary>
      public override long Seek(long offset, SeekOrigin origin)
      {
         throw new NotSupportedException();
      }

      /// <summary>
      /// Not supported
      /// </summary>
      public override void SetLength(long value)
      {
         throw new NotSupportedException();
      }

      public override int Read(byte[] buffer, int index, int count)
      {
         long returned = dataReader.GetBytes(0, currentPosition, buffer, 0, buffer.Length);
         currentPosition += returned;
         return Convert.ToInt32(returned);
      }

      /// <summary>
      /// Not supported
      /// </summary>
      public override void Write(byte[] buffer, int offset, int count)
      {
         throw new NotSupportedException();
      }

      public override bool CanRead
      {
         get { return true; }
      }

      public override bool CanSeek
      {
         get { return false; }
      }

      public override bool CanWrite
      {
         get { return false; }
      }

      public override long Length
      {
         get { throw new NotSupportedException(); }
      }

      public override long Position
      {
         get { throw new NotSupportedException(); }
         set { throw new NotSupportedException(); }
      }

      protected override void Dispose(bool disposing)
      {
         if (!disposed)
         {
            if (disposing)
            {
               if (dataReader != null)
                  dataReader.Dispose();
               SqlConnection conn = null;
               if (command != null)
               {
                  conn = command.Connection;
                  command.Dispose();
               }
               if (conn != null)
                  conn.Dispose();
               disposed = true;
            }
         }
         base.Dispose(disposing);
      }

      public override void Flush()
      {
         throw new NotSupportedException();
      }

   }

}

In Repository.cs:

  public virtual Stream GetDocumentFileStream(int fileId)
  {
     var conn = new SqlConnection {ConnectionString = configuration.ConnectionString};
     var cmd = new SqlCommand
                  {
                     CommandText =
                        "select DocumentFile " +
                        "from MyTable " +
                        "where Id = @Id",
                     Connection = conn,
                  };


     cmd.Parameters.Add("@Id", SqlDbType.Int).Value = fileId;
     conn.Open();
     return new SqlBlobReader(cmd);
  }

In DocumentFile.cs:

  public Stream GetStream()
  {
     return repository.GetDocumentFileStream(Id);
  }

In DocumentController.cs:

  // A download controller in ASP.net MVC 2

  [OutputCache(CacheProfile = "BigFile")]
  public ActionResult Download(int id)
  {
     var document = repository.GetDocument(id);
     return new FileStreamResult(document.DocumentFile.GetStream(), "application/pdf")
               {
                  FileDownloadName = "Foo.pdf";
               };
  }

---

There's a bug; you are ignoring the user's args, and you should probably guard for -ve returned:

  public override int Read(byte[] buffer, int index, int count)
  {
     long returned = dataReader.GetBytes(0, currentPosition,
         buffer, 0, buffer.Length);
     currentPosition += returned;
     return Convert.ToInt32(returned);
  }

should probably be:

  public override int Read(byte[] buffer, int index, int count)
  {
     long returned = dataReader.GetBytes(0, currentPosition,
         buffer, index, count);
     if(returned > 0) currentPosition += returned;
     return (int)returned;
  }

(otherwise you are writing into the wrong part of the buffer)

But generally looks good.

---

Note that .net 4.5 now does this OOB - SqlDataReader.GetStream()

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getstream(v=vs.110).aspx

---

That's gorgeous! Thanks for this memory saver. Besides Marc's fix I modified the constructor to open connection and dispose in case the open or execute fails to reduce code/exception handling in caller. (Didn't know Dispose could be called from constructor). Constructor mod:

try
{
    this.command = command;     // store for disposal

    if (command.Connection.State != ConnectionState.Open)
        command.Connection.Open();

    dataReader = command.ExecuteReader(CommandBehavior.SequentialAccess);
    dataReader.Read();            
}
catch (Exception ex)
{
    Dispose();
    throw;
}