Wednesday, October 31, 2012

Save Image into SQL Server via VB.net


OpenFileDialog for browse the Image and showing in picture box.
        OpenFileDialog1.Filter = "Image File (*.jpg;*.bmp;*.gif)|*.jpg;*.bmp;*.gif"
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
            sfile = OpenFileDialog1.FileName   ' Store the file name in variable "sfile"
            pbox.Image = System.Drawing.Bitmap.FromFile(sfile) 
            pbox.SizeMode = PictureBoxSizeMode.StretchImage
        End If

Convert image into Byte using Filestream

        Try
            Dim fs As FileStream
            fs = New FileStream(sfile, FileMode.Open, FileAccess.Read) 'Pass the sfile value
            Dim picByte As Byte() = New Byte(fs.Length - 1) {}
            fs.Read(picByte, 0, System.Convert.ToInt32(fs.Length))
            fs.Close()

Insert statement for SQL query
            Dim sqltxt As String
            sqltxt = "insert into nstudent values('" & txt_sID.Text & "','" & txt_fname.Text & "','" &   txt_lname.Text & "','" & cmb_gender.Text & "','" & txt_dob.Text & "','" & txt_addr.Text & "',@photo)"

Store image as Binary value using SQLParameter
            Dim imgP As New SqlParameter
            imgP.SqlDbType = SqlDbType.Binary
            imgP.ParameterName = "photo"
            imgP.Value = picByte
            sqlcmd = New SqlCommand(sqltxt, con)
            sqlcmd.Parameters.Add(imgP)
            sqlcmd.ExecuteNonQuery()
            MsgBox("Data Saved Successfully")
            sqlcmd.Dispose()
            con.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

Retrieve Image from database and set in Picture box
        Sub rimage(ByVal imgno As Integer)    'User created Function "rimage"

               If pbox.Image IsNot Nothing Then
                       pbox.Image.Dispose()
               End If
       
        Dim arr() As Byte
             arr = dr.Item(imgno)   'Pass the varaible imgno from funciton "rimage"
              pbox.Image = Image.FromStream(New IO.MemoryStream(arr))
              pbox.SizeMode = PictureBoxSizeMode.StretchImage
    End Sub

Using select command for retrieving data from database
Dim s as String
        s = "Select * from nstudent where sID = '" & valu & "'"  'Getting input using InputBox & store the value in string varaible
 Dim cmd As New SqlCommand(s, con)
        dr = cmd.ExecuteReader
 Dim n As Byte
        n = dr.Read()
        If n > 0 Then
            txt_sID.Text = dr.Item(0)
            txt_fname.Text = dr.Item(1)
            txt_lname.Text = dr.Item(2)
            cmb_gender.Text = dr.Item(3)
            txt_dob.Text = dr.Item(4)
            txt_addr.Text = dr.Item(5)
            Call rimage(6)                          'Call the Function
        Else
            MsgBox("NotFound")
        End If
        If Not dr.IsClosed Then dr.Close()
    End Sub





No comments:

Post a Comment