What 'length' parameter should I pass to SqlDataReader.GetBytes()

Question

I have a SqlDataReader and need to read a varbinary(max) column from it using the SqlDataReader.GetBytes() method. This method populates a byte array and therefore needs to know what length of data to read.

This is where I get confused.. Clearly I want to read all the data that has been returned from the database in this row/column so what 'length' parameter should I pass?

As far as I can see, the SqlDataReader doesn't provide any methods to discover what length of data is available, therefore this method seems fairly awkward to me.

I'm tempted to just pass int.MaxValue here and forget about the issue but something about this doesn't sit right with me.

I am aware that I can instead call

byte[] value = (byte[])dataReader["columnName"];

.. and this seems to completely take care of the length issue internally. However I am working with a set of complicated code generation templates that have been built around the SqlDataReader.GetXXXX() methods. So I am tied into using GetBytes and need to understand its proper usage.

Answer 1

When dealing with varbinary(max), there are two scenarios:

• the length of the data is moderate
• the length of the data is big

GetBytes() is intended for the second scenario, when you are using CommandBehaviour.SequentialAccess to ensure that you are streaming the data, not buffering it. In particular, in this usage you would usually be writing (for example) in a stream, in a loop. For example:

// moderately sized buffer; 8040 is a SQL Server page, note
byte[] buffer = new byte[8040]; 
long offset = 0;
int read;
while((read = reader.GetBytes(col, offset, buffer, 0, buffer.Length)) > 0) {
    offset += read;
    destination.Write(buffer, 0, read); // push downstream
}

However! If you are using moderately sized data, then your original code:

byte[] data = (byte[])reader[col];

is fine!!. There is nothing wrong with this approach, and in fact the Get* API is broken in a few cases - GetChar() being a notable example (hint: it doesn't work).

It doesn't matter that you have existing code that uses Get* - in this case, the cast approach is perfectly appropriate.

Answer 2

You could probably do this. Found on MSDN. Probably it could server your purpose

    // Reset the starting byte for the new BLOB.
  startIndex = 0;

  // Read the bytes into outbyte[] and retain the number of bytes returned.
  retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);

 // Continue reading and writing while there are bytes beyond the size of the buffer.
  while (retval == bufferSize)
  {
    bw.Write(outbyte);
    bw.Flush();

    // Reposition the start index to the end of the last buffer and fill the buffer.
    startIndex += bufferSize;
    retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
  }

  // Write the remaining buffer.
  bw.Write(outbyte, 0, (int)retval - 1);
  bw.Flush();

http://msdn.microsoft.com/en-us/library/87z0hy49%28v=vs.71%29.aspx#Y132

Or this one

int ndx = rdr.GetOrdinal("<ColumnName>");
            if(!rdr.IsDBNull(ndx))
           {
            long size = rdr.GetBytes(ndx, 0, null, 0, 0);  //get the length of data
            byte[] values = new byte[size];

            int bufferSize = 1024;
            long bytesRead = 0;
            int curPos = 0;

            while (bytesRead < size)
            {
                bytesRead += rdr.GetBytes(ndx, curPos, values, curPos, bufferSize);
                curPos += bufferSize;
            }
           }