Page 1 of 1

Save and retrieve images with SQL Server Rate Topic: -----

#1 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Posted 07 May 2009 - 03:13 PM

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:

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! :)

Is This A Good Question/Topic? 0
  • +

Replies To: Save and retrieve images with SQL Server

#2 Guchie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 15-May 09

Posted 18 May 2009 - 01:42 AM

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!!
Was This Post Helpful? 0
  • +
  • -

#3 sraptor  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 26
  • Joined: 22-April 09

Posted 02 June 2009 - 06:18 AM

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
Was This Post Helpful? 0
  • +
  • -

#4 sandli28  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 31-October 08

Posted 17 July 2009 - 12:22 AM

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

Thanx,
Regards,
Sandeep
Was This Post Helpful? 0
  • +
  • -

#5 muupeetz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 17-August 09

Posted 21 September 2009 - 03:40 AM

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 :wub:
Was This Post Helpful? 0
  • +
  • -

#6 mariamfiaz  Icon User is offline

  • New D.I.C Head

Reputation: -3
  • View blog
  • Posts: 8
  • Joined: 03-March 11

Posted 07 March 2011 - 04:11 AM

hello..
Can u explain the same thing in Java???? i have the same problem in java...im not getting how to retrieve images? how to match them??can u help in java?
Was This Post Helpful? 0
  • +
  • -

#7 dotNETnewbie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 29-May 11

Posted 29 May 2011 - 12:52 AM

Hi psycho coder

I have been working with your code and did understand the stored procedures and sql function parts. When it came to the vb.net part, I am not sure how to use the xml code for the parameters, in short how do I use this code with a windows form and picture controls.

Thanks in advance
Was This Post Helpful? 0
  • +
  • -

#8 dotNETnewbie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 29-May 11

Posted 29 May 2011 - 02:05 AM

View PostdotNETnewbie, on 29 May 2011 - 12:52 AM, said:

Hi psycho coder

I have been working with your code and did understand the stored procedures and sql function parts. When it came to the vb.net part, I am not sure how to use the xml code for the parameters, in short how do I use this code with a windows form and picture controls.

Thanks in advance

Hi Again

I have been able to save the picture to the database table successfully. However I am getting an error when I try to Get the image from the same Table. Any ideas on the vb.net code I can use to achieve this?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1