Introduction
There will be many occassion, in which we will be urged to store images in the Database. In some applications we may have some sensitive information which cannot be stored in a file system, since if anything is in the file system, then it may be very easy for the users to hack the pictures/images.
In this article, we will discuss about, how we can insert images to a SqlServer 2000.
We will be learning the following aspects in this article.
- Prerequistes for inserting an image file
- Working with the Stream Object
- Finding the Size and Type of the image that is going to be uploaded
- How to use the InputStream method?
Prerequistes for inserting an image file
Two primary things that we need before the upload begins are
# The property enctype of the Form tag should be set to enctype="multipart/form-data"
# We should have a which allows the user to select the necessary image file (which will be inserted into the database)
# Also we need to Import the Namespace, System.IO to deal with the Stream object.
The above three points applies to an ASPX page. Also we need to have the following prerequistes in the SqlServer.
# We should have a Table with atleast one of the field of type Image.
# It will be better, if we have another field of type Varchar to hold the image type.
So, we have a Sql Table with the field type of Image and we have a (HTMLFile control). We also need a Submit button, where user can click after selecting the image. In the OnClick event of the button, we need to read the content of the image file and finally we insert the image to the table. Let us take a look at the OnClick event of the button, which reads the image and inserts into the sql table.
Code in the OnClick event of the Submit button.
|
Dim intImageSize As Int64
Dim strImageType As String
Dim ImageStream As Stream
' Gets the Size of the Image
intImageSize = PersonImage.PostedFile.ContentLength
' Gets the Image Type
strImageType = PersonImage.PostedFile.ContentType
' Reads the Image
ImageStream = PersonImage.PostedFile.InputStream
Dim ImageContent(intImageSize) As Byte
Dim intStatus As Integer
intStatus = ImageStream.Read(ImageContent, 0, intImageSize)
' Create Instance of Connection and Command Object
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As New SqlCommand("sp_person_isp", myConnection)
' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC
Dim prmPersonImage As New SqlParameter("@PersonImage", SqlDbType.Image)
prmPersonImage.Value = ImageContent
myCommand.Parameters.Add(prmPersonImage)
Dim prmPersonImageType As New SqlParameter("@PersonImageType", SqlDbType.VarChar, 255)
prmPersonImageType.Value = strImageType
myCommand.Parameters.Add(prmPersonImageType)
Try
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Response.Write("New person successfully added!")
Catch SQLexc As SqlException
Response.Write("Insert Failed. Error Details are: " & SQLexc.ToString())
End Try |
How it works?
The Object, PersonImage is the name of the HTMLInputFile control. First we need to get the size of the image that is going to be inserted and that is done by
intImageSize = PersonImage.PostedFile.ContentLength
. Then we retrieve the image type using the property ContenType. Then the most important thing is, we need to get the Image Stream and that is done by
ImageStream = PersonImage.PostedFile.InputStream
. We have an array of Bytes, ImageContent, which is ready to hold the image content. The entire image is read using the method Read of the Stream Object. The method read takes three arguments, viz;
# Target Location that the Image Content to be copied
# Starting position for the purpose of read
# Number of bytes that needs to be read
. And the Read statement is
intStatus = ImageStream.Read(ImageContent, 0, intImageSize)
. Now, we have read the entire image content. Next we need to insert this into a sql table. We are going to use a stored procedure which inserts the image type and the image to a sql table. If you go through the above code listing, then you can see that we use the datatype as SqlDbType.Image.
That is it. We have successfully inserted an image to SqlServer.