Storing and retrieving images and files from SQL Server using .NET

Summary:

This project is about storing and retrieving images and files (E.g., pdf, xls and txt) from SQL Database in Microsoft .NET Using VB.NET

Technical Features:

• SQL Server 2000
• Microsoft .NET Version 3.5
• VB.NET (Windows Forms based application)

Functional Features:

• Uploading Images/Files into database
• Retrieval of Images/Files from database

Storing Images/Files:

1) Create a table in SQL Server 2000 database which has at least one field of type Image

Here is the Script I used:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FileStore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[FileStore]

GO

CREATE TABLE [dbo].[FileStore] (

[FileId] [int] IDENTITY (1, 1) NOT NULL ,

[FileName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[ImageData] [image] NOT NULL,

[FileType] [varchar](10) NOT NULL,

[Added On] [DateTime] NOT NULL,

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

The Image data type is used to store the binary content of the images/Files

2) I am using Open File Dialog to locate the file.

Using OpenFileDialog As OpenFileDialog = Me.GetOpenFileDialog()

If (OpenFileDialog.ShowDialog(Me) = DialogResult.OK) Then

txtFileToUpload.Text = OpenFileDialog.FileName

Else 'Cancel

Exit Sub

End If

End Using

3) I have used two methods , one to upload the image and another one to upload the files.

'Call Upload Images Or File

Dim sFileToUpload As String = ""

sFileToUpload = LTrim(RTrim(txtFileToUpload.Text))

Dim Extension As String = System.IO.Path.GetExtension(sFileToUpload)

upLoadImageOrFile(sFileToUpload, "Image")

upLoadImageOrFile(sFileToUpload, Extension)

4) Convert the file content into array of bytes using FileStream

'Initialize byte array with a null value initially.

Dim data As Byte() = Nothing

'Use FileInfo object to get file size.

Dim fInfo As New FileInfo(sPath)

Dim numBytes As Long = fInfo.Length

'Open FileStream to read file

Dim fStream As New FileStream(sPath, FileMode.Open, FileAccess.Read)

'Use BinaryReader to read file stream into byte array.

Dim br As New BinaryReader(fStream)

'When you use BinaryReader, you need to supply number of bytes to read from file.

'In this case we want to read entire file. So supplying total number of bytes.

data = br.ReadBytes(CInt(numBytes))

5) Saving byte array data to database

a) Create command text to insert record.

qry = "insert into FileStore (FileName,ImageData," & _

"FileType,[Added On]) values(@FileName, @ImageData," & _

"@FileType,@AddedOn)"

b) Create and provide value to the Parameters

'Initialize SqlCommand object for insert.

SqlCom = New SqlCommand(qry, connection)

'We are passing File Name and Image byte data as sql parameters.

SqlCom.Parameters.Add(New SqlParameter("@FileName", sFileName))

SqlCom.Parameters.Add(New SqlParameter("@ImageData", DirectCast(imageData, Object)))

SqlCom.Parameters.Add(New SqlParameter("@FileType", sFileType))

SqlCom.Parameters.Add(New SqlParameter("@AddedOn", Now()))

c) Execute the query to save the byte array to database

SqlCom.ExecuteNonQuery()

lblUploadStatus.Text = "File uploaded successfully"

d) Complete Code to save:

Private Sub upLoadImageOrFile(ByVal sFilePath As String, ByVal sFileType As String)

Dim SqlCom As SqlCommand

Dim imageData As Byte()

Dim sFileName As String

Dim qry As String

Try

'Read Image Bytes into a byte array

'Initialize SQL Server Connection

If connection.State = ConnectionState.Closed Then

connection.Open()

End If

imageData = ReadFile(sFilePath)

sFileName = System.IO.Path.GetFileName(sFilePath)

'Set insert query

qry = "insert into FileStore (FileName,ImageData," & _

"FileType,[Added On]) values(@FileName, @ImageData," & _

"@FileType,@AddedOn)"

'Initialize SqlCommand object for insert.

SqlCom = New SqlCommand(qry, connection)

'We are passing File Name and Image byte data as sql parameters.

SqlCom.Parameters.Add(New SqlParameter("@FileName", sFileName))

SqlCom.Parameters.Add(New SqlParameter("@ImageData", DirectCast(imageData, Object)))

SqlCom.Parameters.Add(New SqlParameter("@FileType", sFileType))

SqlCom.Parameters.Add(New SqlParameter("@AddedOn", Now()))

SqlCom.ExecuteNonQuery()

lblUploadStatus.Text = "File uploaded successfully"

Me.txtFileToUpload.Text = ""

Catch ex As Exception

MessageBox.Show(ex.ToString())

lblUploadStatus.Text = "File could not uploaded"

End Try

End Sub

e)

6)

Retrieving Images/Files:

Retrieving images/files from the SQL database is the exact reverse process of saving the images/files to the SQL database. I have used DataGridView control to list the files/images stored with ViewFile button to view the file/image.

1) Populating the Gridview

Creating the query to list all the rows from FileStore database:

Dim strSql As String = "Select FileId,FileName," & _

"FileType,[Added On] from FileStore"

Fill the Adapter:

'Initialize SQL adapter.

Dim ADAP As New SqlDataAdapter(strSql, connection)

'Initialize Dataset.

Dim DS As New DataSet()

'Fill dataset with FileStore table.

ADAP.Fill(DS, "FileStore")

Assign the dataset to DataGridview:

'Fill Grid with dataset.

dbGridView.DataSource = DS.Tables("FileStore")

Add View File Button to the DataGridView:

Dim dgButtonColumn As New DataGridViewButtonColumn

dgButtonColumn.HeaderText = ""

dgButtonColumn.UseColumnTextForButtonValue = True

dgButtonColumn.Text = "View File"

dgButtonColumn.Name = "ViewFile"

dgButtonColumn.ToolTipText = "View File"

dbGridView.Columns.Add(dgButtonColumn)

2) Viewing the Image

When clicking on the View File button of the DataGridView row, it will display the image/file.

The dbGridView_CellContentClick handler does the trick.

If sender.Columns(e.ColumnIndex).Name = "ViewFile" Then

Select Case dbGridView.Rows(e.RowIndex).Cells("FileType").Value

Case "Image"

...

Case ".txt", ".pdf", ".doc"

...

Creating the query to retrieve the image from FileStore database based on FileId:

'For Image

strSql = "Select ImageData from FileStore WHERE FileId=" & dbGridView.Rows(e.RowIndex).Cells("FileId").Value

Convert the Image content into byte array:

Dim imageData As Byte() = DirectCast(sqlCmd.ExecuteScalar(), Byte())

Convert the byte array to Image using Memory Stream

Dim newImage As Image = Nothing

Using ms As New MemoryStream(imageData, 0, imageData.Length)

ms.Write(imageData, 0, imageData.Length)

'Set image variable value using memory stream.

newImage = Image.FromStream(ms, True)

End Using

Display the image in picture box:

pictureBox1.Image = newImage

3) Viewing the File

· Creating the query to retrieve the File from FileStore database based on FileId:

strSql = "Select ImageData from FileStore WHERE FileId=" & iFileId

· Convert the Image content into byte array:

Dim fileData As Byte() = DirectCast(sqlCmd.ExecuteScalar(), Byte())

· Opening the Tempory File with the Stored File Name

Dim sTempFileName As String = Application.StartupPath & "\" & sFileName

· Convert the byte array to File Using File Stream

Using fs As New FileStream(sFileName, FileMode.OpenOrCreate, FileAccess.Write)

fs.Write(fileData, 0, fileData.Length)

fs.Flush()

fs.Close()

End Using

· Opening the File

System.Diagnostics.Process.Start (sFileName)