As with any .Net work you need to make sure you've imported all the right Namespaces. The Namespaces we need for this tutorial will be:
First we will cover the SQL items needed for this tutorial. First we need a table to hold the images in. In this tutorial we will be using SQL 2005. When storing an image you need a data type that can handle storing a large amount of binary data, for this we will be using the Image data type, which is a BLOB (Binary Large Objects) type. For this example we will storing only the image itself and the images name. To create this table you can use this script:
CREATE TABLE dbo.Images ( image_id INT IDENTITY(1,1) NOT NULL, image_data IMAGE NOT NULL, image_name VARCHAR(75) NOT NULL )
Notice in our table all columns are required (the IDENTITY column takes care of itself). In this tutorial we are going to create a User Defined Function (UDF) for trimming strings that are passed to our table. Trimming strings will come handy when querying the table by the image's name. Here is how our UDF will look:
CREATE FUNCTION dbo.udfTrimString (@value VARCHAR(8000)) RETURNS VARCHAR(8000) AS BEGIN DECLARE @Trimmed_Value VARCHAR(8000) SET @Trimmed_Value = RTrim(LTrim(@value)) RETURN @Trimmed_Value END
Now we need a stored procedure for inserting an image into our table. As stated earlier for this tutorial we will only be working with the image itself and it's name. This is how the stored procedure will look:
CREATE PROCEDURE dbo.uspInsertImage @image_data IMAGE, @image_name VARCHAR(75) AS INSERT INTO Images(image_data, image_name) VALUES (@image_data, dbo.udfTrimString(@image_name))
Now that we have our stored procedure for inserting an image, we need one for retrieving the image back from the table for displaying to the user. Here is what that stored procedure will look like:
CREATE PROCEDURE dbo.uspGetImage @image_name VARCHAR(75) AS BEGIN SELECT image_id AS 'ID', image_data AS 'Image', image_name AS 'ImageName' FROM dbo.Images WHERE image_name = dbo.udfTrimString(@image_name) END
Ok, all the SQL work is completed, now we can move on to the VB.NET code for inserting and retrieving the images we have stored. For inserting the image we will create a function that returns True if the insert works and False if it fails. We will have to convert the image into a byte array, then once that is complete we have the image in a form SQL will understand, since the Image data type is for binary data.
The function will expect 2 parameters, the name of the image, and the name of the stored procedure. I chose to have the name of the stored procedure a parameter so anyone who uses this can name their stored procedure whatever they want and not have to change any of the functions used in this tutorial. So here is how we will insert the image into our Images table
''' <summary>
''' Function to save an image to a SQL database
''' </summary>
''' <param name="imgName">Name of the image (Need path as well)
''' ex: C:\MyImage.gif
''' </param>
''' <param name="storedProc">Stored procedure to execute.
''' NOTE: I decided to make this a parameter so when using it
''' a stored proc doesnt have to be renamed to use this snippet
''' </param>
''' <returns>True if successful, False is failed</returns>
''' <remarks></remarks>
Public Function InsertImage(ByRef imgName As String, ByRef storedProc As String) As Boolean
Try
'First we need to make sure an image name was provided. If
'none is provided we need to show a message to the user
'and exit the function
If IsNullOrEmpty(img) Then
MessageBox.Show("Please provide an image to save.")
Exit Function
End If
'FileInfo instance so we can get all the
'information we need regarding the image
Dim fInfo As New FileInfo(imgName)
'Get the length of the image for the byte array
'we create later in the function
Dim len As Long = fInfo.Length
'This is the connections tring connecting to your database
'** NOTE: This needs to be changed to YOUR connection string **
Dim connString As String = "YourConnectionString"
'Open a FileStream the length of the image being inserted
Using stream As New FileStream(img.Trim(), FileMode.Open)
'Create a new byte array the size of the length of the file
Dim imgData() As Byte = New Byte(Convert.ToInt32(len - 1)) {}
'Read the byte array into the buffer
stream.Read(imgData, 0, len)
'Create our Sql connection
Using con As New SqlConnection(connString)
'Create the command object that will do
'the database work
Using cmd As New SqlCommand()
con.Open()
'Set the properties of ouor connection object
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = storedProc
'Add the parameters for the stored procedure
cmd.Parameters.AddWithValue("@image_data", imgData)
cmd.Parameters.AddWithValue("@image_name", imgName)
'Execute the Stored Procedure
cmd.ExecuteNonQuery()
Return True
End Using
End Using
End Using
Catch ex As SqlException
MessageBox.Show(ex.ToString)
Return False
Catch ex As Exception
MessageBox.Show(ex.ToString)
Return False
End Try
End Function
Ok, now that we have our image inserted into our table, how can we get it back out, as an image (remember we converted the image into a byte array) and display it to the user. For this we will execute our stored procedure, the use a SqlDataReader to read the returned data. From there we would convert the image data into a byte array, then read that array into a MemoryStream.
Finally we will save the image (as a byte array) in the DataTable (the column that will hold the data is of type Byte())Here's the function
''' <summary>
''' Function for retrieving images from a SQL Server based on the image name
''' </summary>
''' <param name="imgName">Name of the image we want to retrieve</param>
''' <param name="storedProc">Name of the stored procedure to execute</param>
''' <returns>A DataTable with the image information</returns>
''' <remarks></remarks>
Public Function GetImageFromDB(ByRef imgName As String, ByRef storedProc As String) As DataTable
'This is the connections tring connecting to your database
'** NOTE: This needs to be changed to YOUR connection string **
Dim connString As String = "YourConnectionString"
'MemoryStream that will hold the byte array
'before converting to a Bitmap
Dim stream As MemoryStream = Nothing
Dim img As Image = Nothing
Dim imgBytes As Byte() = Nothing
'Table that will hold the data returned
Dim table As New DataTable("Images")
'We need to create the columns for the table
table.Columns.Add("ImageID", GetType(Integer))
table.Columns.Add("Image", GetType(Byte()))
table.Columns.Add("ImageName", GetType(String))
'Need a reader to read the returned data
Dim reader As SqlDataReader = Nothing
Try
Using con As New SqlConnection(connString)
Using cmd As New SqlCommand()
'Set the properties of our connection object
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = storedProc
cmd.Parameters.AddWithValue("@image_name", imgName)
'Open our connection
cmd.Connection.Open()
'Read the data
reader = cmd.ExecuteReader()
'Create a new row for the table
Dim row As DataRow
'Now loop through the data
While reader.Read()
row = table.NewRow
'Add the id of the image
row(0) = reader.GetValue(0)
'Here we need to get the binary data and convert
'it to an image before we can add it to the table
imgBytes = CType(reader.GetValue(1), Byte())
'Read the byte array into a MemoryStream
stream = New MemoryStream(imgBytes, 0, imgBytes.Length)
'Create the new Image from the stream
img = Image.FromStream(stream)
img.Save(stream, System.Drawing.Imaging.ImageFormat.Gif)
'Now add the image to the table
row(1) = stream.ToArray()
'Add th image name to the table
row(2) = reader.GetValue(2)
table.Rows.Add(row)
End While
Return table
End Using
End Using
Catch ex As SqlException
MessageBox.Show(ex.ToString())
Return Nothing
Catch ex As Exception
MessageBox.Show(ex.ToString())
Return Nothing
End Try
End Function
NOTE: In both functions you will see the line
Dim connString As String = "YourConnectionString"
Change YourConnectionString to the connection string you use for connecting to your database.
There you go, that's how you insert image to a SQL table and retrieve it to display in your application (or website). Hope you have found this tutorial useful and informative.
Happy Coding!
MultiQuote
| 