How to store a image in SQL Server using vb.net in Asp.net

After a long time, I thought of continuing my blog with the new things i learned. Therefore in this post im going give you a brief description about saving images in MSSQL DB and how to preview them.

Database Table

CREATE TABLE Image(
Customer_id int Primary Key NOT NULL,
image image NOT NULL
) 

Simply drag and drop a file uploader from the Toobox , and a button to your aspx page.

AddImage.aspx page


 <asp:FileUpload ID="FileUpload1" runat="server"/>
<br/>
<asp:Button ID="Button7" runat="server" Text="Upload" />

AddImage.aspx.vb 


Declare the following at the top of the page


Public connectionString As String = "Data Source=localhost;Initial Catalog=DatabaseName;Integrated Security=SSPI;User ID=username;Password=password"
Public sqlCmd As SqlCommand
Public adapters As New SqlDataAdapter


I have a seperate method  in the code behind to read the image and return the image as 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

Then i m calling my ReadFile method to another method which will make things much more easy and understandable.

Public Function insertImage(ByVal CLI_ID As Integer, ByVal path As String) As Boolean
    
            Dim imageData As Byte() = ReadFile(path) 'Calling the ReadFile() Method
            


        Dim con As New SqlConnection(connectionString)
        con.Open()
        Dim cmd As New SqlCommand()
        cmd.Connection = con
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "INSERT INTO Image(   Customer_id , image ) VALUES(@Customer_id, @name)"
        cmd.Parameters.Add("@Customer_id", SqlDbType.VarChar).Value = CLI_ID 
        cmd.Parameters.Add("@ image ", SqlDbType.Binary).Value = imageData 
      
        con.Close()
        BindData()
.
        End Using

    End Function

After that im calling the insertImage() method to Button click event.

Protected Sub Button7_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button7.Click

        If FileUpload1.PostedFile IsNot Nothing AndAlso FileUpload1.PostedFile.FileName <> "" Then
            Dim imageSize As Byte() = New Byte(FileUpload1.PostedFile.ContentLength - 1) {}
            Dim uploadedImage__1 As HttpPostedFile = FileUpload1.PostedFile
            uploadedImage__1.InputStream.Read(imageSize, 0, CInt(FileUpload1.PostedFile.ContentLength))


            Dim path As String = FileUpload1.PostedFile.FileName
            Dim clientId As Integer = 000001  'Enter client id
            insertImage(clientId, path)


        End If
    End Sub


So thats all you have to do. Just copy paste the Content in appropriate places then it will work fine.
Hope this will help you guys.
Cheers.....:)