Getting Started

Add Images to SQL Server

Adding images to and removing them from a SQL Server database is straightforward using simple drag-and-drop procedures.

• By Jason Beres
• 07/01/2004

Technology Toolbox: VB.NET, SQL Server 2000

Drag-and-drop is one of the most productive computer features since the Apple Lisa. You're sure to keep your users happy if you implement drag-and-drop features in your applications. In this article, I'll show you several cool and useful tricks. First, I'll demonstrate how to drag images from the file system over a Windows Forms PictureBox control and trap the file information for the images when you drop them. From there, I'll discuss how to add the images from the file system to a SQL Server database. I've coined the end product the "Cool and Fancy Image Manager" (or just "Image Manager" for short), and I've included a figure that shows you what it looks like (see Figure 1). I won't go into the internals about drag-and-drop or image manipulation, but will teach you practical ways to apply some effective user interface (UI) techniques, as well as some other good stuff along the way.

I created the Image Manager for an internal application at my company so that anyone in the company would be able to add image data to the Web site easily. We had an internal application that managed all the pages for our sites, but the "Add Image" button was a drawback. It didn't provide an effective way for users to associate items with a page. I wanted something better—thus, the Image Manager.

The user interface for Image Manager is simple. It includes a PictureBox (named pb) that facilitates drag-and-drop operations. This control is key to the application. It handles the capture of the Drop event, which fires off the events that save the image to the database. It also includes two or more PictureBox controls that display a full-size image from the database, and a thumbnail image of the full-size image. Last, it includes a ListView control that displays the items stored in the database.

Note that many controls don't display the AllowDrop property in the Properties window at design time in Visual Studio .NET. For this reason, you need to set this property to True, as this code demonstrates for the PictureBox named pb in the Form1_Load event of the MainForm in the Image Manager app:

Private Sub Form1_Load _
   (ByVal sender As System.Object, _
   ByVal e As System.EventArgs) _
   Handles MyBase.Load

   ' Make sure you set the 
   ' AllowDrop property to True,
   ' This is not available
   ' at design time
   pb.AllowDrop = True

End Sub

I've noted other examples that explain how to do a simple drag-and-drop operation from a PictureBox. You can accept multiple files in the Image Manager and save them to SQL Server all at once.

Drag Until You Drop
To implement drag-and-drop, you need to understand the four events—DragEnter, DragOver, DragLeave, and DragDrop—that occur during the process of dragging items over a control and dropping items onto it. The DragEnter, DragOver, and DragLeave events are pretty self-explanatory; they fire when the mouse cursor enters, hovers over, or leaves a control. You can check properties such as the X and Y coordinates for the mouse, the data format of the data, or the type of drag-and-drop events that are allowed. Use this simple code to check what event occurs during a drag operation:

Private Sub pb_DragOver _
   (ByVal sender As Object, _
   ByVal e As DragEventArgs) _
   Handles pb.DragOver
   StatusBar1.Text = "Drag Over Event"
End Sub

Private Sub pb_DragLeave _
   (ByVal sender As Object, _
   ByVal e As System.EventArgs) _
   Handles pb.DragLeave
   StatusBar1.Text = "Drag Leave Event"
End Sub

The DragOver and DragLeave events update the Text property of a StatusBar. You can use DragEnter, DragOver, and DragLeave to determine the state of a drag-and-drop operation, trap the mouse coordinates, or cancel drag-and-drop operations altogether.

The DragDrop event is key to retrieving data that tells which items were dropped onto a control. When this event occurs, you need to check the DataFormats.Format object to make sure you're dealing with the FileDrop format (see Table 1 for a complete list of possible formats). The Format objects represent types that can be contained in the Clipboard, or types allowed during a drag-and-drop operation. Once you're sure the format is the one you want to deal with, you call the GetData method of the DragEventArgs class. At this point, you're getting a list of the file or files that were dropped onto the PictureBox control in a string array and are acting on this data. Take a look at this in action, as well as the code that loops through the list of files dropped onto the control (see Listing 1).

You need to add some data to SQL Server now that you have a handle on where to write your drag-and-drop code and what you're going to do with that code. Adding an image to SQL Server is a little different than simply adding text or numeric data. You need to convert the image to a byte array and insert the byte array data into an Image, Binary, or VarBinary data type in the database. This isn't too hard; you simply create a Stream, then read its contents into an array of bytes. The trick is to remember to initialize the byte array with the size of the data you're trying to insert—in this case, the length in bytes of the image. Using the properties of the file gives you this information quickly. You simply create an instance of the FileInfo class, then pass the name of the image you're working with. Check out the code that shows this in action, as well as how to add the byte array as a parameter to a stored procedure (see Listing 2).

Read Images, Create Thumbnails
Now that the image is in SQL Server, you need an easy way to get the data out and display the image back to a PictureBox control. In some cases, you might even need a thumbnail of the image, or you might want to flip, rotate, or manipulate the image contents even further. You use the FromStream method of the Image class to create an Image data type a PictureBox understands. The methods and properties of the Image class give you complete control over what you can do with any type of image. This code shows you how to create an Image type using the FromStream method, as well as how to set this newly created image as the contents of a PictureBox control (see Listing 3). You can also see what sort of options you have at design time with the Image class (see Figure 2).

You can see that adding to and removing images from a SQL Server database is pretty straightforward, as is doing some simple drag-and-drop procedures. Make sure you're checking the types of files that you want to store when performing binary updates to SQL Server. You can literally store any type of data in SQL Server, and you can save that data back out any way you wish. The next step is to examine the Image Manager code, and see how to implement a better UI in your own applications. Remember, users like to drag and drop, so let them.

• Get Code Download