email article printer friendly get the code more resources

Manage BLOB Data Fields
ADO.NET and SQL Server let you retrieve random images for display in your WinForms apps.
by Fabio Claudio Ferracchiati and Juval Löwy

August 2003 Issue

Technology Toolbox: VB.NET, C#, SQL Server 2000, ADO.NET

Q: Manage BLOB Data Fields
I need to develop an application that displays random images stored in a Microsoft SQL Server database in a picture box. Is there an easy way to accomplish this functionality?

A:
Yes. ADO.NET and SQL Server give you all the tools for retrieving binary large objects (BLOBs) easily from a database. You can use a simple application I created as a model for implementing dynamic image changing in a WinForms form. Start by examining the database structure:

CREATE TABLE [dbo].[T_Image] (
   [ImageID] [int] IDENTITY (1, 1) 
   NOT NULL , [ImageBinary] [image] NOT 
   NULL ) ON [PRIMARY] TEXTIMAGE_ON 
   [PRIMARY]

The table has two columns—an image identifier as primary key, and the image's binary code. As you can see, SQL Server provides an image data type that's useful for managing images within a database. This stored procedure retrieves the binary image data and provides an image identifier:

CREATE PROCEDURE dbo.RetrieveImage
   @id int
AS
SELECT ImageBinary FROM T_Image
WHERE ImageId = @id

This stored procedure inserts a new image into the database:

CREATE PROCEDURE dbo.InsertImage
   @i image
AS
INSERT INTO T_Image (ImageBinary)
VALUES (@i)

The code to execute a stored procedure is simple, thanks to the ADO.NET classes. You call the InsertImage stored procedure, which provides binary code from an image file (see Listing 1).

Calling a stored procedure from the code takes six steps. First, create a SqlConnection object that specifies the connection string of the database to connect. Second, create a SqlCommand object; use the new object to specify the StoredProcedure command type and the CommandText with the stored procedure name. Third, add a new Parameters collection item for each stored procedure parameter. Fourth, open the connection to the database. Fifth, use either the ExecuteNonQuery method to execute a stored procedure that doesn't return a value, or the ExecuteReader method to execute a stored procedure that returns a reference to the DataReader object. Finally, close the connection.