ASP.NET/VB.NET Code to insert images in MS SQL Server Database.

Introduction:

I ran a quick Google Search for “insert image into sql server” came up with some results.There is a Codeproject.com article on it,in fact I think there are two articles about.The first one dealt with inserting the image into an a Microsoft Access Database (ole image).

  1. You can go through the article at Codeproject at:       http://www.codeproject.com/aspnet/fileupload.asp
  2. The second article at Codeproject you can find here: http://www.codeproject.com/aspnet/simpleuploadimage.asp
  3. UPDATE (2/27/2005) : http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21250100.html

Both the articles are written in C#.

Below is the first “hack” of some of the code in ASP.NET/VB.NET for developers:

Note This code is for MS SQL Database Server and the “Image” DataType is used.

Table Structure:

CREATE TABLE [T_EMP_IMAGES] (
[F_EMP_IMG_SR] [int] IDENTITY (1, 1) NOT NULL ,
[F_EMP_REF_SRC] [int] NOT NULL ,
[F_EMP_FILENAME] [varchar] (35) COLLATE ,
[F_EMP_IMAGE] [image] NULL ,
[F_EMP_FILETYPE] [varchar] (25)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

You can change the structure as you like but the main things you need are; an id,and the image field.

[1].Code to insert image to database: Put something like this in your Upload Button Event

    Try
Dim connection As New OleDbConnection(cmain.sCnn)
Dim command As New OleDbCommand(“INSERT INTO T_EMP_IMAGES (F_EMP_REF_SRC,F_EMP_FILENAME,F_EMP_IMAGE,F_EMP_FILETYPE) VALUES (?,?,?,?)”, connection)
Dim ParaidEmp As New OleDbParameter(“F_EMP_REF_SRC”, OleDbType.Integer, 4)
ParaidEmp.Value = idEmp
command.Parameters.Add(ParaidEmp)

            ‘Dim paramTitle As New OleDbParameter(“@MyFileName”, OleDbType.VarChar, 35)
Dim paramTitle As New OleDbParameter(“F_EMP_FILENAME”, OleDbType.VarChar, 35)
paramTitle.Value = fileTitle
command.Parameters.Add(paramTitle)
‘Dim paramData As New OleDbParameter(“@MyFile”, OleDbType.Binary)
Dim paramData As New OleDbParameter(“F_EMP_IMAGE”, OleDbType.Binary)
paramData.Value = fileData
command.Parameters.Add(paramData)
‘Dim paramType As New OleDbParameter(“@FileType”, OleDbType.VarChar, 25)
Dim paramType As New OleDbParameter(“F_EMP_FILETYPE”, OleDbType.VarChar, 25)
paramType.Value = fileType
command.Parameters.Add(paramType)

            connection.Open()
command.ExecuteNonQuery()
connection.Close()
Catch ex As Exception

       End Try

[2].How to display the image on a webform:

Steps:

[1]. Create a aspx file and add a Image Control

     i.e:  ProtectedWithEvents Image1 As System.Web.UI.WebControls.Image

[2]. Now you need to feed this control with the data you inserted into the Database Table above,so

Add this in the form load

      Image1.ImageUrl = “EmpGetImg.aspx?ID=77777”

Now you wonder thats another file your calling and passing a parameter id to it.So what does this file do?,Well  it queries the database and gets the image data.The code behind of the file is listed below:

CODE BEHIND: EmpGetImg.aspx

PrivateSub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.Load
Dim iFid As
String
iFid = Request.QueryString(“ID”)
ShowTheFile(iFid)
End
Sub

‘This function does the work and gets the data to our Image1 control
‘Some of the lines are commented in the code cause it
‘Depends on the situation where you use the code,say if you used it in a grid you can have a link which sends you to
‘the image,so the commented code is needed for the browser to understand that this is image data

Private
Function ShowTheFile(ByVal FileID AsString)
Dim SQL As
String
SQL = “SELECT * FROM T_EMP_IMAGES WHERE F_EMP_REF_SRC =” & FileID.ToString()
Dim connection AsNew
SqlConnection(“server=SERVER; uid=sa; pwd=password; Database=TEST”)
Dim command AsNew
SqlCommand(SQL, connection)
connection.Open()
Dim dr As
SqlDataReader
dr = command.ExecuteReader
dr.Read()
‘Response.Clear()
‘Response.ContentType = dr(“F_EMP_FILETYPE”)
Response.BinaryWrite(dr(“F_EMP_IMAGE”))
‘ dr.Close()
Response.End()
EndFunction

So we have a page with an image control and when you run that page you call the second page to get the data and fill it into the image control.

Refer the Codeproject articles to get a better idea on the different methods.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: