Retrieving Large Data with ADO.NET (BLOB)

For couple of days, I was working on an issue occurring while I`m trying to download files from Sharepoint 2003 document library. The problem was some of the files were too big (more than 256mb) so when I try to open binary visual studio was giving SystemOutOfMemory Exception. Later I understood that it was hitting the limit of the byte[]. So I thought there should be a way of streaming big files, I searched on internet for 5 hours and when most of my fate lost, my colligue found out about BLOB`s.

Then we found the following article that explains how to stream a big binary file from SQL database.

MSDN Article: http://msdn.microsoft.com/en-us/library/87z0hy49.aspx

C# Version: (In case link breaks)

// Assumes that connection is a valid SqlConnection object.
SqlCommand command = new SqlCommand("SELECT pub_id, logo FROM pub_info", connection);

// Writes the BLOB to a file (*.bmp).
FileStream stream; 

// Streams the BLOB to the FileStream object.
BinaryWriter writer;                      

// Size of the BLOB buffer.
int bufferSize = 100;                   

// The BLOB byte[] buffer to be filled by GetBytes.
byte[] outByte = new byte[bufferSize]; 

// The bytes returned from GetBytes.
long retval;                            

// The starting position in the BLOB output.
long startIndex = 0;                  

// The publisher id to use in the file name.
string pubID = "";                     

// Open the connection and read data into the DataReader.
connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);

while (reader.Read())
{
  // Get the publisher id, which must occur before getting the logo.
  pubID = reader.GetString(0);  

  // Create a file to hold the output.
  stream = new FileStream(
    "logo" + pubID + ".bmp", FileMode.OpenOrCreate, FileAccess.Write);
  writer = new BinaryWriter(stream);

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

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

  // Continue while there are bytes beyond the size of the buffer.
  while (retval == bufferSize)
  {
    writer.Write(outByte);
    writer.Flush();

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

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

  // Close the output file.
  writer.Close();
  stream.Close();
}

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

Bir Cevap Yazın

Aşağıya bilgilerinizi girin veya oturum açmak için bir simgeye tıklayın:

WordPress.com Logosu

WordPress.com hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap / Değiştir )

Twitter resmi

Twitter hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap / Değiştir )

Facebook fotoğrafı

Facebook hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap / Değiştir )

Google+ fotoğrafı

Google+ hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap / Değiştir )

Connecting to %s