I must admit, I'm no database wizz. It's been a looooong time since I've played with SQL, and I never really digged into System.Data. The rare moments I required a data connection, the design-time experience was enough, oh, and that Fill call on that adapter! q

Recently, a customer explained he was using an SqlDataReader to fetch its data on demand, to avoid loading too much. One of the fields was a byte array containing a GZip file. He was using the static method GZipCompressedStream.Decompress to get the data in that GZip file. Unfortunately, the data was sometimes quite large, and this technique prevented him from using SqlDataReader.GetBytes (exposed in the IDataRecord interface), which allows to read only chunks of a field at a time.

Bummer... My "Streams everywhere" modo was challenged. You see, Xceed Streaming Compression for .NET allows you to either decompress a single byte array in one operation (stateless), or wrap a GZipCompressedStream around your source stream and read from it to decompress data on the fly. But in this case, no streams. Nada. Or if there is one, I didn't find it.

I was not going to get defeated by that mere absence. The "Streaming" in "Xceed Streaming Compression for .NET" is exactly about that scenario. It turns out it was quite easy to overcome this little problem. I created myself a DataRecordStream class, which derives from System.IO.Stream.

Apart from the usual overrides required when deriving from System.IO.Stream, I expose a constructor requiring an IDataRecord parameter and the index of the field to expose as a stream.

    public DataRecordStream( IDataRecord record, int fieldIndex )
    {
      if( record == null )
        throw new ArgumentNullException( "record" );

      if( ( fieldIndex < 0 ) || ( fieldIndex >= record.FieldCount ) )
        throw new ArgumentOutOfRangeException( "fieldIndex", fieldIndex, "Invalid field index." );

      m_record = record;
      m_field = fieldIndex;
    }

Then, when Read is called, I simply turn to my IDataRecord's GetBytes method to fill that buffer.

    public override int Read( byte[] buffer, int offset, int count )
    {
      long read = m_record.GetBytes( m_field, m_position, buffer, offset, count );

      m_position += read;

      if( ( m_length != -1 ) && ( m_position > m_length ) )
      {
        // The reported length was smaller than the actual size.
        // We update the length dynamically.
        m_length = m_position;
      }

      return unchecked( ( int )read );
    }

The rest is just glue for managing the position and allowing seeking. The good part about this new class is that you can now wrap any pass-thru stream around it, for example a GZipCompressedStream. My customer can now read text compressed in a GZip file stored in one of its database fields quite easily, without consuming too much memory.

      SqlConnection connection = new SqlConnection( 
        "integrated security=SSPI;data source=xxx;initial catalog=GZipTest" );

      connection.Open();

      try
      {
        using( SqlCommand command = new SqlCommand( "SELECT * FROM GZipTestTable", connection ) )
        {
          using( SqlDataReader dataReader = command.ExecuteReader() )
          {
            while( dataReader.Read() )
            {
              using( StreamReader textReader = new StreamReader(
                       new GZipCompressedStream(
                         new DataRecordStream( 
                           dataReader, dataReader.GetOrdinal( "GZipField" ) ) ) ) )
              {
                string line;

                while( ( line = textReader.ReadLine() ) != null )
                {
                  Console.WriteLine( line );
                }
              }
            }
          }
        }
      }
      finally
      {
        connection.Close();
      }

I have made a VB.NET version of the class too. Enjoy!

DataRecordStream.zip (3.34 KB)