SQLServer typically stores binary large object (BLOB) data, such as graphics and sounds, as native text, ntext, or image data types. Microsoft provides several tools for working with BLOBs, and you can find extensive information about these tools in the Microsoft Knowledge Base. Here are seven of the most important access technologies for working with BLOBs in SQL Server.
7. TextCopy
The TextCopy utility is a character-based command-line tool that uses the DB-Library to import and export binary files into a SQL Server database. TextCopy is in the expandable .zip file in the SQL Server installation CD's \Program Files\Microsoft SQLServer\80\Tools\Devtools\Samples\
DBLib\TextCopy folder.
6. Bulk Image Import
Provided as part of the SQL Server 2000 Resource Kit, the Bulk Image Import (BII) utility is an extension of the character-based bulk copy program (bcp). Bcp lets you import and export large objects into SQL Server's text, ntext, and image columns. Unlike bcp, the BII utility uses a filename and path argument that points to a file containing the binary data you want to import into SQL Server.
5. READTEXT, WRITETEXT, and UPDATETEXT
T-SQL's built-in READTEXT, WRITETEXT, and UPDATETEXT statements let you work with BLOB data in your T-SQL scripts. Although you can't use these functions to directly import or export BLOB data, you can use them to move, copy, delete, and extract BLOB data that's already in the database.
4. ADO's GetChunk and AppendChunk
Available since ADO 1.0, the GetChunk method retrieves BLOB data from a text, ntext, or image column. You can use AppendChunk to write BLOB data. These methods help applications import and export BLOB data by breaking the data into chunks. For more information about GetChunk and AppendChunk, see "Who's Afraid of the Big, Bad BLOB?" April 1999, InstantDoc ID 5107.
3. ADO's Stream Object
Introduced in ADO 2.5, the ADO Stream object provides methods for importing and exporting entire files using one method call. For details about using the Stream object, see "Return of the BLOB," June 2001, InstantDoc ID 20460.
2. ADO.NET's SqlDataReader
You can use the ADO.NET SqlDataReader's ExecuteReader method to retrieve BLOB data from a SQL Server database. The Microsoft article "Read and Write a File to and from a BLOB Column by Using ADO.NET and Visual Basic .NET" (316887) explains this technology.
1. ADO.NET's DataSet
One of the best ways to import BLOB data through ADO.NET is by using the DataSet object. You can find information about using DataSet to work with BLOB data in the Microsoft article "Read and Write BLOB Data by Using ADO.NET with Visual Basic .NET" (308042).
2 comments
Hide comments
