FAQ: How do I store BLOB data (e.g. image, document) into the database?

Question

How do I store BLOB data (e.g. image, document) into the database?

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 1

1)    The intuitive approach is to store BLOB data (e.g. image, document) into database in format of Byte Array.

Thus, convert file into Byte Array before inserting it into the database, and convert it back to a file after retrieving it from the database.

 

T-SQL Data Type

Member name	Description
Binary	Array of type Byte.    A fixed-length stream of binary data ranging between 1 and 8,000 bytes.
Image	Array of type Byte.     A variable-length stream of binary data ranging from 0 to 2 <sup>31</sup> -1 (or 2,147,483,647) bytes.

 

Code sample:

http://social.msdn.microsoft.com/forums/en-US/vbgeneral/thread/0fb5fd05-3eb5-4568-9e2a-fd9ba5ed5a3e

 

2)     However, storing binary objects (e.g. image, office document etc.) into the database is a little cumbersome when it comes to retrieval. Hence the most commonly accepted way is to only store the file path in the database, and to store the documents on a file share.

 

             This article talks about this topic: Store and retrieve objects as BLOBs in SQL Server

             http://www.codeproject.com/KB/database/Store_and_manipulat_BLOBs.aspx

 

3)  In addition, in SQL Server 2008, you can use the new feature FileStream data type, which allows storage and efficient access to BLOB data using a combination of SQL Server 2008 and the NTFS file system. FileStream uses the NT system cache for caching file data.

 

FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FileStream data. Win32 file system interfaces provide streaming access to the data.

 

Tutorials/Details:

http://msdn.microsoft.com/en-us/library/bb933993.aspx

http://msdn.microsoft.com/en-us/library/cc949109(SQL.100).aspx

 

For more FAQ about Visual Basic .NET General, please see Visual Basic .NET General FAQ

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.