Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

Upload image files to SQL from a directory

I have a directory of image files.  I need to get them into SQL.  This is the backend of a web app.  I have the code to upload from the web into SQL working with no problems at all.  What I now need to do is write the app that will take the existing files and put them into the same database so the web app only looks to a single location.  Here is what Im trying to do.

       Dim entriesDirectory As ArrayList = New ArrayList

            Dim dir As String = "C:\Images"
            Dim directoryContents As System.IO.DirectoryInfo = _
              New System.IO.DirectoryInfo(dir)
            Dim Mime As String = "image/pjpeg"
            Dim imgtype As String
            Dim nm As String
            Dim uid As String
            Dim memid As Integer

            For Each fileCurrent As System.IO.FileInfo In directoryContents.GetFiles
                nm = CStr(fileCurrent.Name)
                Dim imgSize As Int64 = CInt(fileCurrent.Length)
                Dim img As System.IO.FileInfo = (fileCurrent)
                Me.txtShow.Text += nm & "  ----  "

                If nm.Substring(0, 3) = "cdi" Then
                    Me.txtShow.Text += "cdib" & "  ----  "
                    uid = nm.Substring(nm.IndexOf("_"))
                    uid = Replace(uid, "_", "")
                    Me.txtShow.Text += Replace(uid, ".jpg", "") & vbNewLine
                    imgtype = "cdib"
                    memid = Replace(uid, ".jpg", "")

                ElseIf nm.Substring(0, 3) = "inc" Then
                    Me.txtShow.Text += "incver" & "  ----  "
                    uid = nm.Substring(nm.IndexOf("_"))
                    uid = Replace(uid, "_", "")
                    Me.txtShow.Text += Replace(uid, ".jpg", "") & vbNewLine
                               End If
         Catch ex As Exception
        End Try

This code just access's the files and splits out some information on the file that was stored in the filename that I need to store.  Here is where I am trying to do something to upload the file

            Dim dir As String = "C:\Images"
            Dim directoryContents As System.IO.DirectoryInfo = _
              New System.IO.DirectoryInfo(dir)
            Dim Mime As String = "image/pjpeg"

            For Each fileCurrent As System.IO.FileInfo In directoryContents.GetFiles
                Dim file As System.IO.FileInfo
                Dim Size As Int64
                Dim ImageType As String
                Dim ImageStream As Stream

                Size = (fileCurrent.Length)
                ImageStream = file.OpenRead

                Dim ImageContent(Size) As Byte
                Dim intStatus As Integer
                intStatus = ImageStream.Read(ImageContent, 0, Size)

                Dim myConnection2 As SqlConnection = New SqlConnection("Server=server;Database=Test;Trusted_Connection=True;")

                Dim myCommand2 As New SqlCommand("Insert into image(fields,ima) VALUES (@Values,@ima)", myConnection2)

                 Dim prmPersonImage As New SqlParameter("@ima", SqlDbType.Image)
                prmPersonImage.Value = ImageContent

                    Console.Write("New image successfully added!")
                Catch ex As SqlException
                End Try
        Catch ex As Exception
        End Try

This does not have to be real clean code.  This is a run once app and I just need to get the job done.
Thanks for your help
Fred Goodwin
Fred Goodwin
1 Solution
I am doing the same thing - converting all my asp pages over to SQL. Here are some functions I used, maybe they'll help you?

        Public Shared Function SaveImageToDB(ByVal FilePath As String, ByVal FileName As String, ByVal Article_ID As Integer) As Integer

            ' provide read access to the file
            Dim Fs As FileStream = New FileStream(FilePath, FileMode.Open, FileAccess.Read)

            ' Create a byte array of file stream length
            Dim ImageData As Byte()
            ReDim ImageData(CInt(Fs.Length))

            'Read block of bytes from stream into the byte array
            Fs.Read(ImageData, 0, System.Convert.ToInt32(Fs.Length))

            'Get the width and height
            Dim myImage As System.Drawing.Image = System.Drawing.Image.FromFile(FilePath)
            Dim Width As Integer = myImage.Width
            Dim Height As Integer = myImage.Height

            Return AddImage(FileName, GetMIMEType(FilePath), ImageData, CInt(Article_ID), CInt(Fs.Length), Width, Height)

            'Close the File Stream

        End Function

        Private Shared Function GetMIMEType(ByVal filepath As String) As String
            Dim regPerm As RegistryPermission = New RegistryPermission(RegistryPermissionAccess.Read, "\\HKEY_CLASSES_ROOT")
            Dim classesRoot As RegistryKey = Registry.ClassesRoot
            Dim fi As FileInfo = New FileInfo(filepath)
            Dim dotExt As String = LCase(fi.Extension)
            Dim typeKey As RegistryKey = classesRoot.OpenSubKey("MIME\Database\Content Type")
            Dim keyname As String

            For Each keyname In typeKey.GetSubKeyNames()
                Dim curKey As RegistryKey = classesRoot.OpenSubKey("MIME\Database\Content Type\" & keyname)
                If LCase(CStr(curKey.GetValue("Extension"))) = dotExt Then
                    'Debug.WriteLine("Content type was " & keyname)
                    Return keyname
                End If

            Return "text/plain"
        End Function

     Private Shared Function AddImage(ByVal Image_Name As String, ByVal Image_Type As String, ByVal Image As Byte(), ByVal Article_ID As Integer, ByVal Image_Size As Integer, ByVal Image_Width As Integer, ByVal Image_Height As Integer) As Integer
            Return CType(SqlHelper.ExecuteScalar(connectionString, "AddImage", Image_Name, Image_Type, Image, Article_ID, Image_Size, Image_Width, Image_Height), Integer)
        End Function
And here is the stored procedure for AddImage()

CREATE procedure dbo.AddImage

@Image_Name Varchar(255),
@Image_Type Varchar(255),
@Image Image,
@Article_ID int,
@Image_Size int,
@Image_Width int,
@Image_Height int



      (Image_Name, Image_Type, Image, Article_ID, Image_Size, Image_Width, Image_Height)
      (@Image_Name, @Image_Type, @Image, @Article_ID,
       @Image_Size, @Image_Width, @Image_Height)



Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now