[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Upload image files to SQL from a directory

Posted on 2004-10-26
3
Medium Priority
?
170 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
Comment
Question by:Fred Goodwin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 8

Accepted Solution

by:
toddhd earned 2000 total points
ID: 12414786
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

649 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