Fred Goodwin
asked on
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(di r)
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.ToStrin g)
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(di r)
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(ImageCont ent, 0, Size)
Dim myConnection2 As SqlConnection = New SqlConnection("Server=serv er;Databas e=Test;Tru sted_Conne ction=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( prmPersonI mage)
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
Dim entriesDirectory As ArrayList = New ArrayList
Try
Dim dir As String = "C:\Images"
Dim directoryContents As System.IO.DirectoryInfo = _
New System.IO.DirectoryInfo(di
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.ToStrin
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(di
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(ImageCont
Dim myConnection2 As SqlConnection = New SqlConnection("Server=serv
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(
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.