The following VB.NET Code snippets explains how to store the files (Images, pdf, txt etc) into the database and how to retrieve the files from the Sql server.


Storage of Files

I have added a Browse Button, Text Box and Upload Button.

The user can either type the file name directly in the text box or click Browse button to select the file.

Click on Upload Button. The Images will be stored into the database.


Retrieval of Files

While retrieving I am populating the content in a dataGridView and have added a View File button in DataGridView.

When they click on View File Button, the corresponding file will be opened.



Code to Store Images/Files into Database




/*The user can select the file to be uploaded */

Private Sub cmdBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdBrowse.Click

Using OpenFileDialog As OpenFileDialog = Me.GetOpenFileDialog()

'Open the File Dialog to select the file
If (OpenFileDialog.ShowDialog(Me) = DialogResult.OK) Then

txtFileToUpload.Text = OpenFileDialog.FileName

Else 'Cancel

Exit Sub

End If

End Using

End Sub



/*Function to return OpenFileDialog*/

/*The file types can be uploaded are image files, .txt, .doc and .pdf*/


Private Function GetOpenFileDialog() As OpenFileDialog

Dim openFileDialog As New OpenFileDialog

openFileDialog.CheckPathExists = True

openFileDialog.CheckFileExists = True

openFileDialog.Filter = "Image Files (*.bmp;*.jpg;*.jpeg;*.GIF)*.bmp;*.jpg;*.jpeg;*.GIF" + _
"PNG files (*.png)*.pngtext files (*.text)*.txtdoc files (*.doc)*.docpdf files (*.pdf)*.pdf"


openFileDialog.Multiselect = False
openFileDialog.AddExtension = True
openFileDialog.ValidateNames = True
openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)

Return openFileDialog


End Function







/*Upload Button Click Handler */

Private Sub cmdUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpload.Click

Me.lblUploadStatus.Text = ""

If LTrim(RTrim(txtFileToUpload.Text)) = "" Then

lblUploadStatus.Text = "Enter File Name"

txtFileToUpload.Focus()

Exit Sub

End If

'Call Upload Images Or File
Dim sFileToUpload As String = ""

sFileToUpload = LTrim(RTrim(txtFileToUpload.Text))

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

//Call the Upload method based on the type of file

If StrComp(Extension, ".bmp", CompareMethod.Text) = 0 Or _
StrComp(Extension, ".jpg", CompareMethod.Text) = 0 Or _
StrComp(Extension, ".jpeg", CompareMethod.Text) = 0 Or _
StrComp(Extension, ".png", CompareMethod.Text) = 0 Or _
StrComp(Extension, ".gif", CompareMethod.Text) = 0 Then

upLoadImageOrFile(sFileToUpload, "Image")

Else 'Pass the extension

upLoadImageOrFile(sFileToUpload, Extension)

End If

End Sub







'The Actual code to store the Files into the database

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

'Convert File to bytes Array
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()))

'Execute the Query
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






'Open file in to a filestream and read data in a byte array.

Private Function ReadFile(ByVal sPath As String) As Byte()

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

Return data
End Function





Code to retrieve files from database




/*Get table rows from sql server to be displayed in Datagrid. */


Private Sub GetImagesFromDatabase()

Try

'Initialize SQL Server Connection
If connection.State = ConnectionState.Closed Then

connection.Open()

End If

Dim strSql As String = "Select FileId,FileName," & _
"FileType,[Added On] from FileStore"

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

'Fill Grid with dataset.
dbGridView.DataSource = DS.Tables("FileStore")

'Add View Button Starts

Dim dgButtonColumn As New DataGridViewButtonColumn


dgButtonColumn.HeaderText = ""

dgButtonColumn.UseColumnTextForButtonValue = True

dgButtonColumn.Text = "View File"

dgButtonColumn.Name = "ViewFile"

dgButtonColumn.ToolTipText = "View File"

dgButtonColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCellsExceptHeader
dgButtonColumn.FlatStyle = FlatStyle.System

dgButtonColumn.DefaultCellStyle.BackColor = Color.Gray

dgButtonColumn.DefaultCellStyle.ForeColor = Color.White

dbGridView.Columns.Add(dgButtonColumn)


Catch ex As Exception
MessageBox.Show(ex.ToString())
MessageBox.Show("Could not load the File")
End Try
End Sub





'View File Button Handler

Private Sub dbGridView_CellContentClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dbGridView.CellContentClick

Dim strSql As String = ""
Me.lblUploadStatus.Text = ""

Try
Select Case e.ColumnIndex
Case Is > -1

If sender.Columns(e.ColumnIndex).Name = "ViewFile" Then
Select Case dbGridView.Rows(e.RowIndex).Cells("FileType").Value

Case "Image"

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

Dim sqlCmd As New SqlCommand(strSql, connection)

'Get image data from DB
Dim imageData As Byte() = DirectCast(sqlCmd.ExecuteScalar(), Byte())

'Initialize image variable
Dim newImage As Image = Nothing

If Not imageData Is Nothing Then
'Read image data into a memory stream
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 picture in Picture Box

pictureBox1.Image = newImage
End If

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

downLoadFile(dbGridView.Rows(e.RowIndex).Cells("FileId").Value, dbGridView.Rows(e.RowIndex).Cells("FileName").Value, dbGridView.Rows(e.RowIndex).Cells("FileType").Value)

End Select

End If

End Select
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try

End Sub


'Function to download file from the database

Private Sub downLoadFile(ByVal iFileId As Long, ByVal sFileName As String, ByVal sFileExtension As String)

Dim strSql As String

'For Document
Try
'Get image data from gridview column.
strSql = "Select ImageData from FileStore WHERE FileId=" & iFileId

Dim sqlCmd As New SqlCommand(strSql, connection)

'Get image data from DB
Dim fileData As Byte() = DirectCast(sqlCmd.ExecuteScalar(), Byte())

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

If Not fileData Is Nothing Then

'Read image data into a file stream
Using fs As New FileStream(sFileName, FileMode.OpenOrCreate, FileAccess.Write)
fs.Write(fileData, 0, fileData.Length)
'Set image variable value using memory stream.
fs.Flush()
fs.Close()
End Using

'Open File

Process.Start(sFileName)

End If

Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub



0 Responses to Storing and Retrieving Images/Files In Sql Server - VB.NET

Post a Comment