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();

Extending Web Application for extranet HTTPS enabled.

1. Go to Central Administration
   
2. Go to Application Management
   
3. Under Create or Extend Web Application
   
4. Select http://mossdev/ as web application
   
5. Change Description: Sharepoint – Extranet
   
   1. Port: 443 (HTTPS Port)
      
   2. Host Header: extranet
      
   3. User SSL: YES
      
   4. Zone: Extranet
      
6. Also don't forget to add http://extranet to DNS Server.
   
7. Install IIS 6.0 Resource Kit Tools from Microsoft`s web site.
   
   1. Install just SelfSSL if wanted or do complete installation.
      
   2. After installing RKT, from start menu strat SelfSSL console app.
      
   3. On the console write:
      
   4. Selfssl /T /N:CN=extranet (press enter)
      
   5. Write exit to exit from console.
      
   6. Go to IIS go to Default Web Site go to Properties
      
      1. Select Directory Security
         
      2. See if View Certificate button is enabled if it is certificate is created.
         
      3. Go to Sharepoint – Extranet we site on IIS
         
      4. From properties go to Directory Security
         
      5. Go to Server Certificate from wizard select Assign an Existing Certificate
         
      6. Select the certificate that was create
         
      7. Leave port number as 443
         
      8. After adding the certificate to the extranet web site
         
      9. Go to Edit enable Require secure channel (SSL)
         
      10. Enable Require 128-bit encryption.
          
   7. Go to command line to reset IIS, write iisreset /noforce
      
   8. Open Internet explorer to try the sure write https://extranet/
      

Turning On Self Service Site Management

1. Go to Application Management from Central Administration
   
2. Under the Application Security
   
3. Go to Self Service site management
   
4. Select Require secondary contact
   
5. Select On from the radio box
   
6. Click OK
   
7. Go to MOSS 2007 Home
   
   1. You will see new announcement
      
   2. Go to Site Actions -> Site Settings
      
   3. Under Site Collection Administration, select Site Directory Settings
      
   4. Select "Create new site collections from Site Directory"
      
   5. Click OK, this will enable to users can add new sites from the site directory.