none
Insert Multiple Images Into SQL RRS feed

  • Question

  • First of all, I'd like to point out that I'm very new to programming and still learning.

    I'm trying to insert multiple images into my table with a click of a button. I have my images stored in a folder and the file names of these images match with ID column in my SQL Table.

    My table has 30 Columns and 5000 rows. I'm able to connect my table but I just don't know how to insert all images in this folder to my table with matching ID. (i.e If ID=123456 I want to insert an image file named 123456.jpg to the column named Photo)

    Below is my code and any help would be appreciated. By the way, Column Photo is varbinary(max)

    Dim con As SqlConnection
        Dim cmd As SqlCommand
    
        Dim ds As New DataSet
    
        con = New SqlConnection("server=MY-PC; Initial Catalog=Northwind;Integrated Security=SSPI")
        cmd = New SqlCommand()
        con.Open()
        cmd.Connection = con
    
        BindingSource1.DataSource = 
    
            cmd.CommandText = "SELECT * FROM Northwind Where ID like '" + TextBox1.Text + "'"
            ' dr = cmd.ExecuteReader
            Dim adapter As New SqlDataAdapter(cmd)
            Dim table As New DataTable
            adapter.Fill(table)
            BindingSource1.DataSource = table
            DataGridView1.DataSource = BindingSource1
    Monday, April 29, 2019 4:55 PM

Answers

  • Hello,

    The following does one image at a time, it's possible to use the same code to iterate a folder and do many at once, it will work but there are ways to optimize if speed is an issue.

    Example data class

    Imports System.Data.SqlClient
    
    Public Class DataOperations
        Private ConnectionString As String = "Your connection string"
        Public Sub InsertImage(pIdentifier As Integer, pImageBytes As Byte())
            Dim updateStatement = "UPDATE dbo.Categories SET [Picture] = @Picture WHERE CategoryID = @ID"
    
            Using cn = New SqlConnection(ConnectionString)
                Using cmd = New SqlCommand() With {.Connection = cn, .CommandText = updateStatement}
                    cmd.Parameters.AddWithValue("@Picture", pIdentifier)
                    cmd.Parameters.AddWithValue("@ID", pImageBytes)
    
                    Try
                        cn.Open()
                        cmd.ExecuteNonQuery()
                    Catch ex As Exception
                        '
                        ' Decide how to handle but don't leave it blank
                        ' If nothing else use
                        Console.WriteLine(ex.Message)
                        '
                    End Try
                End Using
            End Using
        End Sub
    End Class
    

    Simple usage (yes I'm sure this does not fit the need of iterating a folder but this is teaching not fully giving a solution) Select image via OpenDialog. 

    Imports System.IO
    
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops = New DataOperations
            If OpenFileDialog1.ShowDialog = DialogResult.OK Then
                Dim Identifier As Integer = 12
                Dim fileBytes As Byte() = File.ReadAllBytes(OpenFileDialog1.FileName)
                ops.InsertImage(Identifier, fileBytes)
            End If
        End Sub
    End Class
    
    I'd highly advise not using a string field (which it appears you are for an id since you are using a WHERE like and that is unwise) but instead an auto-incrementing key of type Integer.

    All of the above is based off a full code sample I wrote for Microsoft which lives here.

    https://code.msdn.microsoft.com/INSERT-Image-into-SQL-29dfc8ee?redir=0


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by tcoder11 Tuesday, April 30, 2019 7:54 AM
    Monday, April 29, 2019 6:41 PM
    Moderator

All replies

  • anyone?
    Monday, April 29, 2019 6:07 PM
  • Hello,

    The following does one image at a time, it's possible to use the same code to iterate a folder and do many at once, it will work but there are ways to optimize if speed is an issue.

    Example data class

    Imports System.Data.SqlClient
    
    Public Class DataOperations
        Private ConnectionString As String = "Your connection string"
        Public Sub InsertImage(pIdentifier As Integer, pImageBytes As Byte())
            Dim updateStatement = "UPDATE dbo.Categories SET [Picture] = @Picture WHERE CategoryID = @ID"
    
            Using cn = New SqlConnection(ConnectionString)
                Using cmd = New SqlCommand() With {.Connection = cn, .CommandText = updateStatement}
                    cmd.Parameters.AddWithValue("@Picture", pIdentifier)
                    cmd.Parameters.AddWithValue("@ID", pImageBytes)
    
                    Try
                        cn.Open()
                        cmd.ExecuteNonQuery()
                    Catch ex As Exception
                        '
                        ' Decide how to handle but don't leave it blank
                        ' If nothing else use
                        Console.WriteLine(ex.Message)
                        '
                    End Try
                End Using
            End Using
        End Sub
    End Class
    

    Simple usage (yes I'm sure this does not fit the need of iterating a folder but this is teaching not fully giving a solution) Select image via OpenDialog. 

    Imports System.IO
    
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops = New DataOperations
            If OpenFileDialog1.ShowDialog = DialogResult.OK Then
                Dim Identifier As Integer = 12
                Dim fileBytes As Byte() = File.ReadAllBytes(OpenFileDialog1.FileName)
                ops.InsertImage(Identifier, fileBytes)
            End If
        End Sub
    End Class
    
    I'd highly advise not using a string field (which it appears you are for an id since you are using a WHERE like and that is unwise) but instead an auto-incrementing key of type Integer.

    All of the above is based off a full code sample I wrote for Microsoft which lives here.

    https://code.msdn.microsoft.com/INSERT-Image-into-SQL-29dfc8ee?redir=0


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by tcoder11 Tuesday, April 30, 2019 7:54 AM
    Monday, April 29, 2019 6:41 PM
    Moderator
  • I understand that you are a beginner, please begin by asking if there is anything wrong with your code, or what you do not understand about your code. It is very obvious you are oblivious to how any of the given classes should work.  We cannot provide you with an answer in which exists on step 5 if you're not in understanding on whats on step 1-4. 

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Monday, April 29, 2019 6:44 PM