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

/*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)|*.png|text files (*.text)|*.txt|doc files (*.doc)|*.doc|pdf 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

    /*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