Insert BLOB to Oracle and SQL server using ODP.net, system.data.oracleclient and system.data.sqlclient

Recently I had to write 3 test applications to insert a file (blob) into oracle and Sql server using ODP.net (Oracle.DataAccess.Client), Microsoft’s Oracle Client (System.Data.OracleClient) and Microsoft’s Sql Client (System.Data.SqlClient) the object was to do some performance testing. Below are the 3 classes I wrote

 Using ODP.Net

Imports Oracle.DataAccess.Client

Imports System.IO

Imports System.Configuration

 Public Class InsertBlobUsingOdpDotNet

     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim StartTime As DateTime = Now

        InsertBlob()

        Dim endTime As DateTime = Now

        StatusLabel.Text = “Start: ” & StartTime & ” End: ” & endTime

    End Sub

     Public Sub InsertBlob()

        Dim conn As New OracleConnection(“Data Source=” & ServerTextBox.Text & “;User ID=” & UidTestBox.Text & “;Password=” & PwdTextBox.Text)  

    Dim filePath As String

        Dim bigData As Byte() = Nothing

        Dim t As Date

         t = Now

         filePath = TerminatedApplicationFileLocation.Text

        If Not File.Exists(filePath) Then

            Throw New Exception(“file does not exist”)

        End If

        Dim fs As Stream = _

                    File.OpenRead(filePath)

        Dim tempBuff(fs.Length) As Byte

        fs.Read(tempBuff, 0, fs.Length)

        fs.Close()

        conn.Open()

        Dim tx As OracleTransaction

        tx = conn.BeginTransaction()

        Dim p1 As OracleParameter = New OracleParameter(“p_application_no_in”, OracleDbType.Int32, ApplicationNoTextBox.Text, ParameterDirection.Input)

        Dim p2 As OracleParameter = New OracleParameter(“p_FILE_BLOB_in”, OracleDbType.Blob, tempBuff.Length, tempBuff, ParameterDirection.Input)

        Dim cmd As New OracleCommand()

        cmd = conn.CreateCommand()

        cmd.Parameters.Clear()

        cmd.CommandText = “OOD.OOD_PKG.UPDATE_APPLICATION”

        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add(p1)

        cmd.Parameters.Add(p2)

        Try

            cmd.ExecuteNonQuery()

            tx.Commit()

        Catch myex As Exception

            tx.Rollback()

            MsgBox(myex.Message)

        End Try

    End Sub

End Class

Using Microsoft.Net OracleClient

Imports System.Data.OracleClient

Imports System.IO

Imports System.Configuration

 Public Class InsertUsingMicrosoftOracleClient

     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim StartTime As DateTime = Now

        InsertBlob()

        Dim endTime As DateTime = Now

        StatusLabel.Text = “Start: ” & StartTime & ” End: ” & endTime

    End Sub

     Public Sub InsertBlob()

        Dim conn As New OracleConnection(“server=” & ServerTextBox.Text & “;Uid=” & UidTestBox.Text & “;pwd=” & PwdTextBox.Text)

        Dim filePath As String

        Dim bigData As Byte() = Nothing

        Dim t As Date

         t = Now

         filePath = TerminatedApplicationFileLocation.Text

        If Not File.Exists(filePath) Then

            Throw New Exception(“file does not exist”)

        End If

         Dim fs As Stream = _

                    File.OpenRead(filePath)

        Dim tempBuff(fs.Length) As Byte

         fs.Read(tempBuff, 0, fs.Length)

        fs.Close()

        conn.Open()

         Dim tx As OracleTransaction

        tx = conn.BeginTransaction()

         Dim cmd As New OracleCommand()

        cmd = conn.CreateCommand()

         cmd.Transaction = tx

         cmd.CommandText = “declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;”

        cmd.Parameters.Add(New OracleParameter(“tempblob”, OracleType.Blob)).Direction = ParameterDirection.Output

        cmd.ExecuteNonQuery()

         Dim tempLob As OracleLob

        tempLob = cmd.Parameters(0).Value

        tempLob.BeginBatch(OracleLobOpenMode.ReadWrite)

        tempLob.Write(tempBuff, 0, tempBuff.Length)

        tempLob.EndBatch()

         cmd.Parameters.Clear()

        cmd.CommandText = “OOD.OOD_PKG.UPDATE_APPLICATION”

        cmd.CommandType = CommandType.StoredProcedure

        Dim parmAppNo = New OracleParameter(“p_application_no_in”, OracleType.Int32)

        parmAppNo.Direction = ParameterDirection.Input

        parmAppNo.Value = ApplicationNoTextBox.Text

        cmd.Parameters.Add(parmAppNo)

        Dim pBlob = New OracleParameter(“p_FILE_BLOB_in”, OracleType.Blob)

        pBlob.Value = tempLob

        cmd.Parameters.Add(pBlob)

        Try

            cmd.ExecuteNonQuery()

        Catch myex As Exception

            MsgBox(myex.Message)

        End Try

        tx.Commit()

     End Sub

End Class

Using SqlClient

Imports System.Data.SqlClient

Imports System.IO

 Public Class InsertBlobUsingSqlClient

     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim StartTime As DateTime = Now

        InsertBlob()

        Dim endTime As DateTime = Now

        StatusLabel.Text = “Start: ” & StartTime & ” End: ” & endTime

    End Sub

    Public Sub InsertBlob()

Dim conn As New SqlConnection(“Data Source=” & ServerTextBox.Text & “;Initial Catalog=” & DbTextBox.Text & “;Persist Security Info=True;User Id=” & UidTestBox.Text & “;Password=” & PwdTextBox.Text)

        Dim filePath As String

         Dim t As Date

         t = Now

         filePath = TerminatedApplicationFileLocation.Text

        If Not File.Exists(filePath) Then

            Throw New Exception(“file does not exist”)

        End If

         Dim fs As Stream = _

                    File.OpenRead(filePath)

        Dim tempBuff(fs.Length) As Byte

        fs.Read(tempBuff, 0, fs.Length)

        fs.Close()

        conn.Open()

         Dim tx As SqlTransaction

        tx = conn.BeginTransaction()

         Dim cmd As New SqlCommand()

        cmd = conn.CreateCommand()

         cmd.Transaction = tx

         cmd.Parameters.Clear()

        cmd.CommandText = “UPDATE_APPLICATION”

        cmd.CommandType = CommandType.StoredProcedure

        Dim parmAppNo = New SqlParameter(“@p_application_no_in”, SqlDbType.BigInt)

        parmAppNo.Direction = ParameterDirection.Input

        parmAppNo.Value = ApplicationNoTextBox.Text

        cmd.Parameters.Add(parmAppNo)

        Dim pBlob = New SqlParameter(“@p_FILE_BLOB_in”, SqlDbType.VarBinary)

        pBlob.Value = tempBuff

        cmd.Parameters.Add(pBlob)

        Try

            cmd.ExecuteNonQuery()

        Catch myex As Exception

            MsgBox(myex.Message)

        End Try

        tx.Commit()

     End Sub

End Class

 I found Blob insert to Oracle was really slow, So I had to write a program to speed up the process, I found by chunking up the buffer It really speeded up the process (Using ODP.Net)

 Public Sub ChunkInsertBlob()

     Dim conn As New OracleConnection(“Data Source=” & ServerTextBox.Text & “;User ID=” & UidTestBox.Text & “;Password=” & PwdTextBox.Text)  

      Dim filePath As String

        Dim bigData As Byte() = Nothing

        Dim t As Date

        Dim chunkSize As Integer = BufferTextBox.Text

         t = Now

         filePath = TerminatedApplicationFileLocation.Text

         If Not File.Exists(filePath) Then

            Throw New Exception(“file does not exist”)

        End If

         Dim fs As Stream = _

                    File.OpenRead(filePath)

        Dim tempBuff(fs.Length) As Byte

         fs.Read(tempBuff, 0, fs.Length)

        fs.Close()

        conn.Open()

         Dim tx As OracleTransaction

        tx = conn.BeginTransaction()

         Dim p1 As OracleParameter = New OracleParameter(“p_application_no_in”, OracleDbType.Int32, ApplicationNoTextBox.Text, ParameterDirection.Input)

        Dim p2 As OracleParameter = New OracleParameter(“p_FILE_BLOB_in”, OracleDbType.Blob, ParameterDirection.Input)

         Dim myBlob As New OracleBlob(conn)

         Dim startOffset As Integer = 0

        Dim writeBytes As Integer = 0

         ‘ loading the buffer in small chunks to Oracle

        myBlob.BeginChunkWrite()

        Do

            writeBytes = If(startOffset + chunkSize > tempBuff.Length, tempBuff.Length – startOffset, chunkSize)

            myBlob.Write(tempBuff, startOffset, writeBytes)

            startOffset += writeBytes

        Loop While startOffset < tempBuff.Length

        myBlob.EndChunkWrite()

        p2.Value = myBlob

        Dim cmd As New OracleCommand()

        cmd = conn.CreateCommand()

        cmd.Parameters.Clear()

        cmd.CommandText = ” OOD.OOD_PKG.UPDATE_APPLICATION ”

        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add(p1)

        cmd.Parameters.Add(p2)

        Try

            cmd.ExecuteNonQuery()

            tx.Commit()

        Catch myex As Exception

            tx.Rollback()

            MsgBox(myex.Message)

        End Try

    End Sub

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s