views:

208

answers:

2

What's the most memory effective way to read an SQL 2005 image field using C# 3.5?

Right now I have a (byte[])cm.ExecuteScalar("...").

If I could not read all field content into memory, that would be nice.

+1  A: 

See this excellent article here or this blog post for a long explanation how to do it.

Basically, you need to use a SqlDataReader and specify SequentialAccess to it when you create it - then you can read (or write) the BLOB from the database in chunks of whatever size is best for you.

Basically something like:

SqlDataReader myReader = getEmp.ExecuteReader(CommandBehavior.SequentialAccess);

while (myReader.Read())
{
   int 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)
   {
      // write the buffer to the output, e.g. a file
      ....

      // 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 last buffer to the output, e.g. a file
   ....
}

// Close the reader and the connection.
myReader.Close();

Marc

marc_s
There are some important problems in that; you aren't incrementing the data-offset, and you aren't processing the final buffer if the last block isn't a complete buffer-load...
Marc Gravell
I think the data offset is being incremented (startIndex += bufferSize) - no? Yes - the last incomplete buffer is not shown here - this is an excerpt from the longer article I linked to, to illustrate the most important bits of the mechanism - it's **not** a complete working piece of code
marc_s
OK; "my bad" on the offset ;-p
Marc Gravell
A: 

The trick here is to use ExecuteReader in sequential mode, and read the data from the IDataReader. Here's a version for CLOBs - BLOBs are virtually identical, but with a byte[] and GetBytes(...).

Something like:

using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
    byte[] buffer = new byte[8040]; // or some multiple (sql server page size)
    while (reader.Read()) // each row
    {
        long dataOffset = 0, read;
        while ((read = reader.GetBytes(
            colIndex, dataOffset, buffer, 0, buffer.Length)) > 0)
        {
            // TODO: process "read"-many bytes from "buffer"
            dataOffset += read;
        }
    }
}
Marc Gravell