Working with binary large objects (BLOBs)

Introduction

 
You can define a BLOB as a large photo, document, audio, etc. saved in binary formats that you want to save in a database.
 
Saving and retrieving BLOBs in a database is more complex than querying string or numeric data.
 
The BLOB may be very large and if you try to move it in one piece will consume a lot of system memory and that for sure will affect your application performance.
 
To reduce the amount of system memory you have to break up the BLOB into smaller pieces.
 
There are a lot of classes that are designed for moving large amounts of binary data like BinaryRader, BinaryWriter which exists in the System.IO namespace. In the next paragraphs, you will see how to use all of this.
 

Saving a BLOB value to the database

 
To save a BLOB value to the database we use FileStream and BinaryReader classes.
 
The next example will show you the process of saving a BLOB to a database.
  1. string filePath = @ "D:\\My Movie.wmv";  
  2.   
  3. //A stream of bytes that represents the binary file  
  4. FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);  
  5.   
  6. //The reader reads the binary data from the file stream  
  7. BinaryReader reader = new BinaryReader(fs);  
  8.   
  9. //Bytes from the binary reader stored in BlobValue array  
  10. byte[] BlobValue = reader.ReadBytes((int) fs.Length);  
  11.   
  12. fs.Close();  
  13. reader.Close();  
  14.   
  15. SqlConnection BlobsDatabaseConn = new SqlConnection("Data Source = .; Initial Catalog = BlobsDatabase; Integrated Security = SSPI");  
  16. SqlCommand SaveBlobeCommand = new SqlCommand();  
  17. SaveBlobeCommand.Connection = BlobsDatabaseConn;  
  18. SaveBlobeCommand.CommandType = CommandType.Text;  
  19. SaveBlobeCommand.CommandText = "INSERT INTO BlobsTable(BlobFileName, BlobFile)" + "VALUES (@BlobFileName, @BlobFile)";  
  20.   
  21. SqlParameter BlobFileNameParam = new SqlParameter("@BlobFileName", SqlDbType.NChar);  
  22. SqlParameter BlobFileParam = new SqlParameter("@BlobFile", SqlDbType.Binary);  
  23. SaveBlobeCommand.Parameters.Add(BlobFileNameParam);  
  24. SaveBlobeCommand.Parameters.Add(BlobFileParam);  
  25. BlobFileNameParam.Value = filePath.Substring(filePath.LastIndexOf("\\") + 1);  
  26. BlobFileParam.Value = BlobValue;  
  27. try {  
  28.     SaveBlobeCommand.Connection.Open();  
  29.     SaveBlobeCommand.ExecuteNonQuery();  
  30.     MessageBox.Show(BlobFileNameParam.Value.ToString() + " saved to database.""BLOB Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  31. catch (Exception ex) {  
  32.     MessageBox.Show(ex.Message, "Save Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);  
  33. finally {  
  34.     SaveBlobeCommand.Connection.Close();  

Retrieving a BLOB from the database

 
To retrieve a BLOB value from the database we use FileStream and BinaryWriter classes.
 
The next example will show you the process of retrieving a BLOB to a database.
 
NOTE: you will see that we set the CommandBehavior to SquentialAccess when we call ExecuteReader() method, this allows us to use the GetBytes() method of the SqlDataRader, so we can read the BLOB from the database in smaller, user-definable amounts.
  1. string SavePath = @ "D:\\My BLOBs";  
  2. SqlConnection SaveConn = new SqlConnection("Data Source = .; Initial Catalog = BlobsDatabase; Integrated Security = SSPI");  
  3. SqlCommand SaveCommand = new SqlCommand();  
  4. SaveCommand.CommandText = "Select BlobFileName, BlobFile from BlobsTable where BlobFileName = @BlobFileName";  
  5. SaveCommand.Connection = SaveConn;  
  6. SaveCommand.Parameters.Add("@BlobFileName", SqlDbType.NVarChar).Value = "My Movie.wmv";  
  7.   
  8. //the index number to write bytes to  
  9. long CurrentIndex = 0;  
  10.   
  11. //the number of bytes to store in the array  
  12. int BufferSize = 100;  
  13.   
  14. //The Number of bytes returned from GetBytes() method  
  15. long BytesReturned;  
  16.   
  17. //A byte array to hold the buffer  
  18. byte[] Blob = new byte[BufferSize];  
  19.   
  20. SaveCommand.Connection.Open();  
  21.   
  22. //We set the CommandBehavior to SequentialAccess  
  23. //so we can use the SqlDataReader.GerBytes() method.  
  24. SqlDataReader reader = SaveCommand.ExecuteReader(CommandBehavior.SequentialAccess);  
  25.   
  26. while (reader.Read()) {  
  27.     FileStream fs = new FileStream(SavePath + "\\" + reader["BlobFileName"].ToString(), FileMode.OpenOrCreate, FileAccess.Write);  
  28.     BinaryWriter writer = new BinaryWriter(fs);  
  29.   
  30.     //reset the index to the beginning of the file  
  31.     CurrentIndex = 0;  
  32.   
  33.     BytesReturned = reader.GetBytes(1,  
  34.         //the BlobsTable column indexCurrentIndex, // the current index of the field from which to begin the read operationBlob, // Array name to write tha buffer to0, // the start index of the array to start the write operationBufferSize // the maximum length to copy into the buffer);   
  35.         while (BytesReturned == BufferSize) {  
  36.             writer.Write(Blob);  
  37.             writer.Flush();  
  38.             CurrentIndex += BufferSize;  
  39.             BytesReturned = reader.GetBytes(1, CurrentIndex, Blob, 0, BufferSize);  
  40.         }  
  41.         writer.Write(Blob, 0, (int) BytesReturned); writer.Flush(); writer.Close(); fs.Close();  
  42.     }  
  43.   
  44.     reader.Close();  
  45.     SaveCommand.Connection.Close(); 
To fully understand the concept you need to try to write this code yourself.
 
Note The database and the full source code in the source code area with this article.