如何使用SqlDataReader对象时，使从普通的老式C＃对象提供的BLOB流？ [英] How to make streams from BLOBs available in plain old C# objects when using SqlDataReader?

问题描述

这是该方案：

• 我们存储文件，例如比较大的文件（10-300MB），在我们的MSSQL数据库的斑点。
• 我们有一个非常小的领域模型，所以我们使用，而不是一个ORM清洁SqlDataReader的方式为我们的代码库，以避免不必要的依赖关系。
• 我们要使用在服务器环境中的对象上ASP.NET/ASP.NET MVC的Web页面。
• 我们不希望临时存储的BLOB的字节[]，以避免在服务器上的内存使用率很高

因此​​，我一直在做的就是实现自己的SqlBlobReader。它继承流和IDisposable接口和实例化过程中，我们必须提供包含返回一行与一列，这是我们要流当然是一滴查询一个SqlCommand。然后，我的C＃的域对象可以有Stream类型的属性，它返回一个SqlBlobReader实现。流传输到在ASP.net MVC等一个FileContentStream若这流然后可以使用。

它将立即做一个的ExecuteReader与SequentialAccess使从MSSQL服务器中的BLOB的流。这意味着，我们必须要小心，当用它来处理数据流的ASAP，而我们总是懒洋洋的实例SqlBlobReader在需要时，如内使用我们的域对象存储库调用。

我的问题是，那么：

• 这是使用，而不是一个ORM时SqlDataReader的实现斑点流于普通的旧域对象的一个​​聪明的办法？
• 我不是一个ADO.NET专家，并实行似乎是合理的？

SqlBlobReader.cs：

 使用系统;
使用System.Data这;
使用System.Data.SqlClient的;
使用System.IO;

命名空间富
{
   ///＆LT;总结＆gt;
   ///有必须是在工作里面的SqlCommand一个SqlConnection。记得使用后处置的对象。
   ///＆LT; /总结＆gt;
   公共类SqlBlobReader：流
   {
      私人只读SqlCommand的命令;
      私人只读SqlDataReader的DataReader的;
      私人布尔处置= FALSE;
      私人长currentPosition = 0;

      ///＆LT;总结＆gt;
      ///构造函数
      ///＆LT; /总结＆gt;
      ///＆LT; PARAM NAME =命令＆gt;该提供＆LT;第＆GT;的SqlCommand＆LT; /段＆GT;只能在SELECT语句中一个字段，否则流将无法正常工作。只选择一排，所有的人将被忽略＆LT; /参数＆GT;
      公共SqlBlobReader（SqlCommand的命令）
      {
         如果（命令== NULL）
            抛出新ArgumentNullException（命令）;
         如果（command.Connection == NULL）
            抛出新的ArgumentException（内部连接不能为空，命令）;
         如果（command.Connection.State！= ConnectionState.Open）
            抛出新的ArgumentException（内部连接必须打开，命令）;
         DataReader的= Command.ExecuteReader却（CommandBehavior.SequentialAccess也）;
         dataReader.Read（）;
         this.command =命令; //只存储处置后
      }

      ///＆LT;总结＆gt;
      /// 不支持
      ///＆LT; /总结＆gt;
      公众覆盖寻求长（长偏移，SeekOrigin原点）
      {
         抛出新NotSupportedException异常（）;
      }

      ///＆LT;总结＆gt;
      /// 不支持
      ///＆LT; /总结＆gt;
      公众覆盖无效SetLength（long值）
      {
         抛出新NotSupportedException异常（）;
      }

      公众覆盖INT读（byte []的缓冲区，诠释指数，诠释计数）
      {
         长返回= dataReader.GetBytes（0，currentPosition，缓冲液，0，buffer.Length）;
         currentPosition + =返回;
         返回Convert.ToInt32（返回）;
      }

      ///＆LT;总结＆gt;
      /// 不支持
      ///＆LT; /总结＆gt;
      公共覆盖无效写入（byte []的缓冲区，诠释抵消，诠释计数）
      {
         抛出新NotSupportedException异常（）;
      }

      公众覆盖布尔的CanRead
      {
         获得{返回true; }
      }

      公众覆盖布尔CanSeek
      {
         获得{返回false; }
      }

      公众覆盖布尔CanWrite
      {
         获得{返回false; }
      }

      公众覆盖长的长度
      {
         获得{抛出新NotSupportedException异常（）; }
      }

      公众覆盖多头头寸
      {
         获得{抛出新NotSupportedException异常（）; }
         集合{抛出新NotSupportedException异常（）; }
      }

      保护覆盖无效的Dispose（BOOL处置）
      {
         如果（！处置）
         {
            如果（处置）
            {
               如果（DataReader的！= NULL）
                  dataReader.Dispose（）;
               SqlConnection的康恩= NULL;
               如果（命令！= NULL）
               {
                  康恩= command.Connection;
                  command.Dispose（）;
               }
               如果（conn将！= NULL）
                  conn.Dispose（）;
               处置= TRUE;
            }
         }
         base.Dispose（处置）;
      }

      公众覆盖无效的同花顺（）
      {
         抛出新NotSupportedException异常（）;
      }

   }

}
 

在Repository.cs：

 公开虚拟流GetDocumentFileStream（INT FILEID）
  {
     VAR康恩=新的SqlConnection {的ConnectionString = configuration.ConnectionString};
     VAR CMD =新的SqlCommand
                  {
                     的CommandText =
                        选择DocumentFile+
                        从MyTable的+
                        其中Id = @Id
                     连接=康涅狄格州，
                  };


     cmd.Parameters.Add（@ ID，SqlDbType.Int）。价值= FILEID;
     conn.Open（）;
     返回新SqlBlobReader（CMD）;
  }
 

在DocumentFile.cs：

 公共流GetStream（）
  {
     返回repository.GetDocumentFileStream（同上）;
  }
 

在DocumentController.cs：

  //在ASP.net MVC 2一个下载器

  [的OutputCache（CacheProfile =大文件）]
  公众的ActionResult下载（INT ID）
  {
     变种文档= repository.GetDocument（ID）;
     返回新FileStreamResult（document.DocumentFile.GetStream（），应用程序/ PDF格式）
               {
                  FileDownloadName =Foo.pdf;
               };
  }
 

解决方案

有一个bug;你忽略了用户的指定参数时，你或许应该警惕的-ve 返回：

 公众覆盖INT读（byte []的缓冲区，诠释指数，诠释计数）
  {
     长返回= dataReader.GetBytes（0，currentPosition，
         缓冲液，0，buffer.Length）;
     currentPosition + =返回;
     返回Convert.ToInt32（返回）;
  }
 

也许应该是：

 公众覆盖INT读（byte []的缓冲区，诠释指数，诠释计数）
  {
     长返回= dataReader.GetBytes（0，currentPosition，
         缓冲区，指数，计数）;
     如果（返回大于0）currentPosition + =返回;
     返程（INT）返回;
  }
 

（否则你正在写入缓冲区的错误部分）

但总体效果不错。

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.

这篇关于如何使用SqlDataReader对象时，使从普通的老式C＃对象提供的BLOB流？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！