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

Answers

  • 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.


    Saturday, April 11, 2009 5:12 PM