VB.NET Tutorials

Welcome to Dream.In.Code
	Become an Expert! Join 300,370 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,458 people online right now. Registration is fast and FREE... Join Now! Chat LIVE With a Expert

Save and retrieve images with SQL Server

Save and retrieve images with SQL Server

Options

PsychoCoder
Group Icon

7 May, 2009 - 02:13 PM

Post #1

I've seen his question asked so many times that I've decided to write a tutorial on it. The question ... How to store and retrieve images in SQL Server. There are some who say this is a bad idea, and some who fully support this. This tutorial is not a debate on the pros and cons of storing images in a SQL database, it is intended to show how to accomplish this. So let's get started.

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:

CODE

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:

CODE

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:

CODE

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:

CODE

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

CODE

''' <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

CODE

''' <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

CODE

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!

Register to Make This Ad Go Away!

Guchie

18 May, 2009 - 12:42 AM

Post #2

Happy Coding is all i can say at the moment. Thank you very much for your valuable time.

This is a very helpful tutorial

Thanks alot!!

sraptor

2 Jun, 2009 - 05:18 AM

Post #3

The store procedure you do in vb.net?
Do you have the 3 files from vb.net you created? I got a little confuse with that

sandli28

16 Jul, 2009 - 11:22 PM

Post #4

Hi,
how thw image save and retrieve from access database.

Thanx,
Regards,
Sandeep

muupeetz

21 Sep, 2009 - 02:40 AM

Post #5

hello psycho coder,

i got errors in :

'Open a FileStream the length of the image being inserted
'Using stream As New FileStream(img.Trim(), FileMode.Open) i comment this coz trims isn't member of system.drawing.image

Using stream As New FileStream(img, FileMode.Open)
error :Error 1 Overload resolution failed because no accessible 'New' can be called with these arguments:
'Public Sub New(handle As Microsoft.Win32.SafeHandles.SafeFileHandle, access As System.IO.FileAccess)': Value of type 'System.Drawing.Image' cannot be converted to 'Microsoft.Win32.SafeHandles.SafeFileHandle'.
'Public Sub New(handle As System.IntPtr, access As System.IO.FileAccess)': Value of type 'System.Drawing.Image' cannot be converted to 'System.IntPtr'.
'Public Sub New(path As String, mode As System.IO.FileMode)': Value of type 'System.Drawing.Image' cannot be converted to 'String'. G:\PROGRAMMMER\MSDN\sqlServerImageSaveRetrieve\sqlServerImageSaveRetrieve\Form1.vb 58 19 sqlServerImageSaveRetrieve

'Create a new byte array the size of the length of the file
Dim imgData() As Byte = New Byte(Convert.ToInt32(len - 1)) {}

thank's for any replies