Introduction:
This article describes an easy approach to saving an image file into an SQL Server 2000 database table as an SQL Server image data type. The sample windows forms application provides a simple interface used to allow the user to select an image file (bitmap, gif, or jpeg) from the file system, title the image, and load the selected into a table in an SQL Server 2000 database. The user may also open an image viewer and examine the images contained in the database; images displayed in the simple viewer display the image, the title of the image, the image type, and the image height and width.
Figure 1: An Image Stored in SQL Server 2000
Getting Started
In order to get started, unzip the included project and open the solution in the Visual Studio 2005 environment. In the solution explorer, you should note the following:
Figure 2: Solution Explorer
Within the solution, the app.config file contains a connection string used with the SQL Server 2000 database used to contain the images. As the configuration file is aimed at my local instance of SQL Server 2000, you will need to modify the connection to point to your database.
<connectionStrings>
<add name="SqlServerImages.My.MySettings.ImageGalleryConnectionString"
connectionString="Data Source=bxswlt;Initial Catalog=ImageGallery;User
ID=sa"
providerName="System.Data.SqlClient" />
</connectionStrings>
In order to support this example, a simple database entitled, "ImageGallery" was added to my SQL Server instance and a table entitled, "ImageCollection" was added to the database. The image collection table (see figure 3) contains the following columns:
- ImageContent
- ImageTitle
- ImageType
- ImageHeight
- ImageWidth
The specification for each of these columns is shown in figure 3. Given the simplicity of the database, I did not include it with the sample application. If you intend to run the sample code, create the database and table using the table properties shown as a guide; also, go ahead and add a data source to the project and point the source to this database.
Figure 3: Table Definition
Code: Main Form (frmMain.vb)
The main form of the sample application (see figure 4) contains the controls necessary to browse for an image file, provide the file with a title, load the image file selected into SQL Server, and to open a viewer to examine the images stored in the table.
Figure 4: Main Form of the Application
The form class includes the following imports:
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
The class also creates two private member variables used to share data between methods exposed in the class; one variable is used to capture the selected image and the other is used to capture the path to the selected image:
Private mImageFile As Image
Private mImageFilePath As String
The find button click event handler calls an Open File Dialog which is used to navigate to and select an image file. The dialog supports searches for bitmaps, jpegs, and gif files. The code used if the click event handler is as follows:
Private Sub btnFind_Click_1(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnFind.Click
OpenFileDialog1.Title = "Set Image File"
OpenFileDialog1.Filter = "Bitmap Files|*.bmp" & _
"|Gif Files|*.gif|JPEG Files|*.jpg"
OpenFileDialog1.DefaultExt = "bmp"
OpenFileDialog1.FilterIndex = 1
OpenFileDialog1.FileName = ""
OpenFileDialog1.ShowDialog()
If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.Cancel Then
Exit Sub
End If
Dim sFilePath As String
sFilePath = OpenFileDialog1.FileName
If sFilePath = "" Then Exit Sub
If System.IO.File.Exists(sFilePath) = False Then
Exit Sub
Else
txtImageFile.Text = sFilePath
mImageFilePath = sFilePath
End If
End Sub
The code opens the Open File Dialog box with the image types used as a filter and the default extension type set to bitmap. The file selected by the user is validated to make sure that it exists, and if it does, the text box used to display the selected file path is updated to display the path to and name of the file selected by the user. Also, the member variable used to display the path to the image file is also updated.
The load button click event handler is used to load the image and image related data into SQL Server. That code is as follows:
Private Sub btnLoad_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
Try
If (Me.txtImageFile.Text = String.Empty Or Me.txtTitle.Text =
String.Empty) Then
MessageBox.Show("Complete both form fields prior to submitting",
"Missing Values", _
MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Exit Sub
End If
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), "File Test Error")
End Try
Dim fs As FileStream = New FileStream(mImageFilePath.ToString(),
FileMode.Open)
Dim img As Byte() = New Byte(fs.Length) {}
fs.Read(img, 0, fs.Length)
fs.Close()
mImageFile = Image.FromFile(mImageFilePath.ToString())
Dim imgHeight As Integer = mImageFile.Height
Dim imgWidth As Integer = mImageFile.Width
Dim imgLength As Integer = mImageFile.PropertyItems.Length
Dim imgType As String = Path.GetExtension(mImageFilePath)
mImageFile = Nothing
Dim strConnect As String
strConnect = "Data Source=bxswlt;Initial Catalog=ImageGallery;User ID=sa"
Dim conn As SqlConnection = New SqlConnection(strConnect)
Dim sSQL As String = "INSERT INTO ImageCollection (ImageContent, " & _
"ImageTitle, ImageType, ImageHeight, ImageWidth) VALUES(" & _
"@pic, @title, @itype, @iheight, @iwidth)"
Dim cmd As SqlCommand = New SqlCommand(sSQL, conn)
' image content
Dim pic As SqlParameter = New SqlParameter("@pic", SqlDbType.Image)
pic.Value = img
cmd.Parameters.Add(pic)
' title
Dim title As SqlParameter = New SqlParameter("@title",
System.Data.SqlDbType.VarChar, 50)
title.Value = txtTitle.Text.ToString()
cmd.Parameters.Add(title)
' type
Dim itype As SqlParameter = New SqlParameter("@itype",
System.Data.SqlDbType.Char, 4)
itype.Value = imgType.ToString()
cmd.Parameters.Add(itype)
' height
Dim iheight As SqlParameter = New SqlParameter("@iheight",
System.Data.SqlDbType.Int)
iheight.Value = imgHeight
cmd.Parameters.Add(iheight)
' width
Dim iwidth As SqlParameter = New SqlParameter("@iwidth",
System.Data.SqlDbType.Int)
iwidth.Value = imgWidth
cmd.Parameters.Add(iwidth)
Try
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
MessageBox.Show("Query executed.", "Image Load")
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), "Data Error")
Exit Sub
End Try
End Sub
Within this block of code, the first thing that takes place is a validation that the user has supplied a path to the image file and has provided a title for the file:
Try
If (Me.txtImageFile.Text = String.Empty Or Me.txtTitle.Text =
String.Empty) Then
MessageBox.Show("Complete both form fields prior to submitting",
"Missing Values", _
MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Exit Sub
End If
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), "File Test Error")
End Try
After confirming the user input, the image file is converted to a byte array. This byte array will subsequently be passed to the database as the image.
Dim fs As FileStream = New FileStream(mImageFilePath.ToString(),
FileMode.Open)
Dim img As Byte() = New Byte(fs.Length) {}
fs.Read(img, 0, fs.Length)
fs.Close()
After the file stream object is closed, the image is loaded into an image from the source file and the loaded image is used to capture the characteristics of the image and to set local variables to contain the image information (e.g., height, width, and type of image). By storing this information in the table, it would be easier to filter for image types in the database and to dynamically create picture box controls sized to accommodate the stored image.
mImageFile = Image.FromFile(mImageFilePath.ToString())
Dim imgHeight As Integer = mImageFile.Height
Dim imgWidth As Integer = mImageFile.Width
Dim imgLength As Integer = mImageFile.PropertyItems.Length
Dim imgType As String = Path.GetExtension(mImageFilePath)
mImageFile = Nothing
Once the local variables are set and the image disposed of, the insert query used to submit the data to the database is configured: (update the connection string to match your settings; and no, you would not really put your connection string into the application in this manner, instead, use the one in the app.config file)
Dim strConnect As String
strConnect = "Data Source=bxswlt;Initial Catalog=ImageGallery;User ID=sa"
Dim conn As SqlConnection = New SqlConnection(strConnect)
Dim sSQL As String = "INSERT INTO ImageCollection (ImageContent, " & _
"ImageTitle, ImageType, ImageHeight, ImageWidth) VALUES(" & _
"@pic, @title, @itype, @iheight, @iwidth)"
Dim cmd As SqlCommand = New SqlCommand(sSQL, conn)
After the connection is created and the command text set, the parameters are defined, and each is added to the command's parameter collection:
' image content
Dim pic As SqlParameter = New SqlParameter("@pic", SqlDbType.Image)
pic.Value = img
cmd.Parameters.Add(pic)
' title
Dim title As SqlParameter = New SqlParameter("@title",
System.Data.SqlDbType.VarChar, 50)
title.Value = txtTitle.Text.ToString()
cmd.Parameters.Add(title)
' type
Dim itype As SqlParameter = New SqlParameter("@itype",
System.Data.SqlDbType.Char, 4)
itype.Value = imgType.ToString()
cmd.Parameters.Add(itype)
' height
Dim iheight As SqlParameter = New SqlParameter("@iheight",
System.Data.SqlDbType.Int)
iheight.Value = imgHeight
cmd.Parameters.Add(iheight)
' width
Dim iwidth As SqlParameter = New SqlParameter("@iwidth",
System.Data.SqlDbType.Int)
iwidth.Value = imgWidth
cmd.Parameters.Add(iwidth)
After everything is set, the only thing remaining is to post the data to the database:
Try
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
MessageBox.Show("Query executed.", "Image Load")
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), "Data Error")
Exit Sub
End Try
Assuming that everything posted properly, the user will be notified that the query has completed; if the insert fails, the user will be provided with a description of the error.
The only other code in the main form is used to handle the viewer button click; this handler creates a new instance of the viewer and displays it to the user:
Private Sub btnViewer_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnViewer.Click
Dim f As New frmViewer()
f.Show()
End Sub
Code: Viewer (frmViewer.vb)
This is going to be a short section as the viewer does not contain any hand written code. The viewer contains a binding navigator and a few text fields dragged onto the form from the project data source. The text fields are bound to the binding source and are updated through the binding navigator. The picture box control used to display the image has its data bindings set to point to the image data contained in the database. Whenever the binding navigator is sequenced up or down, the image and bound text are updated to the display the values associated with the current record.
Figure 5: Picture Box Control Data Bindings
Summary
Whether or not it is a terrific idea to store images in a database is debatable, some would advocate the storage of path strings in the database to reduce the overhead in lieu of actually storing the images which is certainly a valid consideration. In systems involving large volumes of imagery data, reliance upon stored paths could very well be a requirement. In other instances where only a limited amount of imagery data is stored, there is less of an argument to be made against it.
In the simple viewer provided, the display of the images was managed entirely by binding it to the image source from the image content database column; it is possible to reconstruct the image manually from the byte array but I did not include a description of that process as it seems likely that binding the control to the record would be the sufficient for most situations.