Using the SqlCommand Object to Import BLOBs

SQL Server Magazine technical editor Bob Pfeiff of Microsoft suggested an alternative method for using the DataSet object to import BLOBs—you can use the ADO.NET Command object with a stored procedure. This method doesn't require that you use a DataSet on the client, but it does require a preexisting stored procedure on the SQL Server system. When you're using stored procedure parameters to access BLOBs, remember that the BLOB parameter must always be the last parameter in the call to the stored procedure.

The following code shows a simple stored procedure named p_insertimage that accepts one image data type parameter. The code inserts the parameter into the BLOBTable table. (You can find the schema for this table in the main article.)

CREATE PROCEDURE dbo.p_insertimage(   @image image)AS   SET NOCOUNT ON   INSERT INTO BLOBTable (blob_object)    VALUES(@image)   RETURNGO

Listing A shows the C# code that calls this stored procedure. The code uses an OleDbConnection object to open a connection to SQL Server. Then, the code creates a new FileStream object named fs that opens the file homer.jpg and reads the contents of that file into the byte array named imagefile. Next, the code creates an OleDbCommand object named cmd that will execute the p_insertimage stored procedure, passing the BLOB data to p_insertimage as a parameter.

BLOB access isn't limited to just the System.Data.SqlClient namespace. You can also access BLOBs by using the System.Data.OleDb namespace, as I show here, as well as the System.Data.OracleClient and the System.Data.Odbc namespaces.