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

        Try
            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)
                entriesDirectory.Add(nm)
                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
            Next
         Catch ex As Exception
            MessageBox.Show(ex.ToString)
        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

Try
            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
                myCommand2.Parameters.Add(prmPersonImage)

               Try
                    myConnection2.Open()
                    myCommand2.ExecuteNonQuery()
                    myConnection2.Close()
                    Console.Write("New image successfully added!")
                Catch ex As SqlException
                    Console.Write(ex)
                End Try
            Next
        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
LVL 7
Fred GoodwinVP of Software DevelopmentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

toddhdCommented:
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
            Fs.Close()

        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
            Next

            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

As

Begin

INSERT INTO Images
      (Image_Name, Image_Type, Image, Article_ID, Image_Size, Image_Width, Image_Height)
VALUES
      (@Image_Name, @Image_Type, @Image, @Article_ID,
       @Image_Size, @Image_Width, @Image_Height)

select SCOPE_IDENTITY()

End
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.