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
No comments:
Post a Comment