How To Retrieve and Insert Image in SqlServer and bind to binding navigator and Databinding in vb.net

Question

Hello All,

I am a Beginner and mostly access data as normal datatypes like int,varchar,date etc from sql into my program. Now I need your help to know about that 'How to access data where datatype is varbinary and store data image.

My Code working fine but image not retrieve as normal. Can any one look my code and guide me?

Regards-

Amit

My Code are:

Imports System.Data
Imports UnknownDeveloper.SqlConnectionClass
Imports System.Data.SqlClient
Imports System.IO
Imports System.Drawing.Imaging

Public Class Users
    Dim cmd As New SqlCommand
    Dim Da As New SqlDataAdapter(cmd)
    Dim Dt As New DataTable
    Dim Bs As New BindingSource
    Dim Querymode As Boolean = False
    Dim EditMode As Boolean = False
    Dim AddMode As Boolean = False
    Private Sub FillData(Userid As String, UserName As String, UserRole As String)
        Try
            For Each Ctrl As Control In Me.Controls
                If TypeOf (Ctrl) Is TextBox Then
                    Ctrl.Enabled = False
                    Ctrl.DataBindings.Clear()
                    UserImage.DataBindings.Clear()
                    Ctrl.BackColor = SystemColors.InactiveBorder
                End If
            Next
            cmd.CommandText = "Select top 10* From Users Where Userid like '%" & Userid & "%' and UserName Like '%" & UserName & "%' and UserRole like '%" & UserRole & "%'"
            cmd.Connection = Con
            Da.Fill(Dt)
            Bs.DataSource = Dt
            BN.BindingSource = Bs
            TxtUserid.DataBindings.Add(New Binding("Text", Bs, "Userid"))
            TxtUserName.DataBindings.Add(New Binding("Text", Bs, "Username"))
            TxtPassword.DataBindings.Add(New Binding("Text", Bs, "UserPassword"))
            UserImage.DataBindings.Add(New Binding("Image", Bs, "UserImage"))
            TxtRoleId.DataBindings.Add(New Binding("Text", Bs, "UserRole"))
            TxtAcessDate.DataBindings.Add(New Binding("Text", Bs, "UserAcessDate"))
            TxtAcessMachine.DataBindings.Add(New Binding("Text", Bs, "UserLastAcessMachine"))
            TxtAcessUserIP.DataBindings.Add(New Binding("Text", Bs, "UserLastAcessIP"))
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            Con.Close()
        End Try
    End Sub

    Private Sub Users_Shown(sender As Object, e As System.EventArgs) Handles Me.Shown
        FillData("", "", "")
    End Sub

    Private Sub BtnAdd_Click(sender As System.Object, e As System.EventArgs) Handles BtnAdd.Click

    End Sub

    Private Sub BtnSave_Click(sender As System.Object, e As System.EventArgs) Handles BtnSave.Click

    End Sub

    Private Sub BtnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles BtnUpdate.Click

    End Sub

    Private Sub BtnFind_Click(sender As System.Object, e As System.EventArgs) Handles BtnFind.Click

    End Sub

    Private Sub BtnCancel_Click(sender As System.Object, e As System.EventArgs) Handles BtnCancel.Click

    End Sub

    Private Sub BtnClose_Click(sender As System.Object, e As System.EventArgs) Handles BtnClose.Click

    End Sub

    Private Sub BtnEdit_Click(sender As System.Object, e As System.EventArgs) Handles BtnEdit.Click

    End Sub

    Private Sub BtnQuery_Click(sender As System.Object, e As System.EventArgs) Handles BtnQuery.Click

    End Sub

    Private Sub BtnDelete_Click(sender As System.Object, e As System.EventArgs) Handles BtnDelete.Click

    End Sub

    Private Sub UserImage_Click(sender As System.Object, e As System.EventArgs) Handles UserImage.Click
        Try
            Using ofd As New OpenFileDialog() With {.Filter = ""}
                Dim codecs As ImageCodecInfo() = ImageCodecInfo.GetImageEncoders()
                Dim sep As String = String.Empty
                For Each c As ImageCodecInfo In codecs
                    Dim codecName As String = c.CodecName.Substring(8).Replace("Codec", "Files").Trim()
                    ofd.Filter = String.Format("{0}{1}{2} ({3})|{3}", ofd.Filter, sep, codecName, c.FilenameExtension)
                    sep = "|"
                Next c
                ofd.Filter = String.Format("{0}{1}{2} ({3})|{3}", ofd.Filter, sep, "All Files", "*.*")
                ofd.Title = "Select Image to Open !"
                If ofd.ShowDialog = DialogResult.OK Then
                    UserImage.ImageLocation = ofd.FileName
                End If
            End Using
        Catch ex As Exception

        End Try
    End Sub
End Class

Answer 1

normal datatypes like int,varchar,date

The data ytpe varbinary(max) to store binaries like images is also a "normal" data type in SQL Server,

See binary and varbinary (Transact-SQL)

Answer 2

Check out the following code sample which shows how to add an image, display data in a DataGridView. I kept it simple so it would be easy to learn.

• The code sample has a script to create the SQL-Server database.
• Connection string is in appsettings.json

Module PhotoOperations

    Public Function InsertImage(imageBytes() As Byte, description As String) As Integer
        Dim sql = "    
        INSERT INTO [dbo].[Pictures1]
        (
            [Photo],
            Description
        )
        VALUES
        (
            @ByteArray,
            @Description
        )"

        Using cn = New SqlConnection(ConnectionString())
            Using cmd = New SqlCommand(sql, cn)

                cmd.Parameters.Add("@ByteArray", SqlDbType.VarBinary).Value = imageBytes
                cmd.Parameters.Add("@Description", SqlDbType.NVarChar).Value = description

                cn.Open()

                Return cmd.ExecuteNonQuery()

            End Using
        End Using
    End Function
    Public Function ReadAllImages() As DataTable
        Dim sql = "SELECT Id,Photo,Description FROM dbo.Pictures1;"
        Dim dt As New DataTable
        Using cn = New SqlConnection(ConnectionString())
            Using cmd = New SqlCommand(Sql, cn)
                cn.Open()
                dt.Load(cmd.ExecuteReader())
                Return dt
            End Using
        End Using
    End Function

End Module