Solved

Upload image files to SQL from a directory

Posted on 2004-10-26
166 Views
Last Modified: 2010-04-23
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
0
Question by:imsolost
    1 Comment
     
    LVL 8

    Accepted Solution

    by:
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Learn The Basics of Ethical Hacking & Pen Testing

    Computer and network security is one of the fastest growing and most essential industries in technology, meaning companies will pay big bucks for ethical hackers. This is the perfect course to leap into this lucrative career, learning how to use ethical hacking to reveal ...

    This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
    Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    This video discusses moving either the default database or any database to a new volume.

    933 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now