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

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.

From the Blogs

Don’t let bad data sneak up on you when and where you least expect it. Ferret out bad data with Melissa Data’s newest Profiling Component for SSIS. Learn how to take control of your data using knowledge-base-driven metadata. The truth shall set you free!...More

Now that we’ve outlined the process to let servers in a SQL Server AlwaysOn Availability Group "talk to each other" by means of setting up linked servers, it’s possible to set up some additional or improved checks on Availability Group Health....More

In my previous post, I provided a high-level outline of the core logic (and rationale behind that logic) that would be needed to set up regular synchronization checks on SQL Server Agent Jobs for servers where AlwaysOn Availability Groups have been deployed. In this post, I’ll walk through the steps--and the code--needed to setup those checks....More