upload file to Oracle DB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

has anyone uploaded a document such as word to an Oracle table? if so where
can i find an example of this being done in VB.NET
 
This is some skeleton code to do it. I have this in production and it works
like a charm. You should be able to get the general idea, I had to strip a
quite a bit of stuff out as to not give certain things away.

*Note* if your files are larger than 4MB you will need to edit the
httpResponse size in web.config else you will recieve and "action canelled"
or "page not found" error

Private Function InsertBlob(ByVal lngFormSeq As Integer) As Boolean
'DESCRIPTION:
' Inserts a blob (binary file) into the database.
'

Dim strSQL As String
Try
strSQL = "SELECT Form_Seq, Word_Document_Blob FROM oracleBlobTable " & _
"WHERE Form_Seq = " & lngFormSeq
Dim daBlob As New OracleDataAdapter(strSQL, connOra)
Dim cb As OracleCommandBuilder = New OracleCommandBuilder(daBlob)
Dim ds As New DataSet

Dim fs As New FileStream("C:\FileName.doc", FileMode.OpenOrCreate,
FileAccess.Read)
Dim bBlobStorage(fs.Length) As Byte
fs.Read(bBlobStorage, 0, fs.Length)
fs.Close()
daBlob.Fill(ds, "oracleBlobTable")
Dim dr As DataRow = ds.Tables("oracleBlobTable").NewRow
dr("Form_Seq") = lngFormSeq
dr("Word_Document_Blob") = bBlobStorage
ds.Tables("oracleBlobTable").Rows.Add(dr)
daBlob.Update(ds, "oracleBlobTable")

ds.Dispose()
daBlob.Dispose()
InsertBlob = True
End Function

Private Function RetrieveBlob(ByVal lngFormSeq As Integer) As Boolean
'DESCRIPTION:
' Retrieves a binary file from the database.

Dim comGetBlob As New OracleCommand
Dim bufferSize As Integer = 8192
Dim outbyte(bufferSize - 1) As Byte 'Byte array for Blob data
Dim strSQL As String
Dim startIndex As Long = 0
Dim retval As Integer

Dim fs As FileStream
Dim bw As BinaryWriter

strSQL = "SELECT Word_Document_Blob FROM oracleBlobTable " & _
"WHERE Form_Seq = " & lngFormSeq

comGetBlob.CommandType = CommandType.Text
comGetBlob.CommandText = strSQL
comGetBlob.Connection = connOra

Dim rdrGetBlob As OracleDataReader =
comGetBlob.ExecuteReader(CommandBehavior.SequentialAccess)

If Not rdrGetBlob.HasRows Then
Script_Literal.Text = HandleException(ErrFAFileNotFound)
Exit Function
End If

Do While rdrGetBlob.Read()
fs = New FileStream(conDefaultSaveDir & strDestFileName,
FileMode.OpenOrCreate, FileAccess.Write)
bw = New BinaryWriter(fs)

startIndex = 0
retval = rdrGetBlob.GetBytes(0, startIndex, outbyte, 0, bufferSize)
Do Until Not retval = bufferSize
bw.Write(outbyte) ' Write to file
bw.Flush() ' Clear Writer
outbyte.Clear(outbyte, 0, bufferSize)
startIndex += bufferSize
retval = rdrGetBlob.GetBytes(0, startIndex, outbyte, 0, bufferSize)
Loop
bw.Write(outbyte, 0, retval - 1) ' Write to file
bw.Flush()
Loop
rdrGetBlob.Close()
rdrGetBlob = Nothing
comGetBlob.Dispose()
RetrieveBlob = True
End Function
 
Back
Top