Imports System
Imports System.Data
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.IO
Imports System.Text
Module modSendImagesToDB
Sub Main()
Dim strConn As String = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=latetothegame)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=late)));User Id=space;Password=space;"
Dim myOConn As OracleConnection
Dim storefile As Directory
Dim files As String()
Dim File As String
Dim iCounter As Int32
Dim SourceLoc As String
Try
myOConn = New OracleConnection(strConn)
myOConn.Open()
If myOConn.State = ConnectionState.Open Then
Console.WriteLine("Connected to database!")
files = storefile.GetFiles("D:\user\", "*.jpg", SearchOption.TopDirectoryOnly)
For Each File In files
iCounter = iCounter + 1
SourceLoc = File
Console.WriteLine(File)
' provide read access to the file
Dim Fs As FileStream = New FileStream(SourceLoc,
FileMode.Open, FileAccess.Read)
' Create a byte array of file stream length
Dim ImageData As Byte()
ReDim ImageData(Fs.Length)
'Read block of bytes from stream into the byte array
Fs.Read(ImageData, 0, System.Convert.ToInt32(Fs.Length))
'Close the File Stream
Fs.Close()
' Step 3
' Create Anonymous PL/SQL block string
Dim block As String =
" BEGIN " & _
" INSERT INTO Photos (photoid, photoblob, phototitle) VALUES (:1, :2, :3) ;" & _
" end ;"
' Set command to create Anonymous PL/SQL Block
Dim cmd As OracleCommand = New OracleCommand()
cmd.CommandText = block
cmd.Connection = myOConn
' Since executing an anonymous PL/SQL block, setting the command type
' as Text instead of StoredProcedure
cmd.CommandType = CommandType.Text
' Step 4
' Setting Oracle parameters
' Bind the parameter as OracleDbType.Blob
' to command for inserting image
Dim param1 As OracleParameter = cmd.Parameters.Add("id", OracleDbType.Int32)
param1.Size = 3
param1.Direction = ParameterDirection.Input
param1.Value = iCounter
Dim param2 As OracleParameter = cmd.Parameters.Add("blobtodb", OracleDbType.Blob)
param2.Direction = ParameterDirection.Input
' Assign Byte Array to Oracle Parameter
param2.Value = ImageData
Dim param3 As OracleParameter = cmd.Parameters.Add("filename", OracleDbType.Varchar2)
param3.Size = 50
param3.Direction = ParameterDirection.Input
' Assign Byte Array to Oracle Parameter
param3.Value = File
' Step 5
' Execute the Anonymous PL/SQL Block
' The anonymous PL/SQL block inserts the image to the database and then retrieves
' the images as an output parameter
cmd.ExecuteNonQuery()
cmd.Dispose()
Console.WriteLine("Image file inserted to database from " + SourceLoc)
Next
End If
Catch ex As Exception
Console.WriteLine(ex.ToString)
End Try
End Sub
End Module