Friday, November 21, 2008

Using Predicates with VB.Net Array and List Find methods

Some things are just harder in VB.Net than C#.  Using the Find and FindAll methods when you want to pass a parameter to the search delegate is definitely one such instance.  With C# you can use anonymous methods, but those are not found in VB.Net.  Fortunately a couple of guys cooked up an extended Predicate that allow you to pass in a search object.  It all starts with this class and delegate that you define in a common location (warning, trying to read this code may make you dizzy):

Public Delegate Function PredicateWrapperDelegate(Of T, A) _

    (ByVal item As T, ByVal argument As A) As Boolean

 

Public Class PredicateWrapper(Of T, A)

    Private _argument As A

    Private _wrapperDelegate As PredicateWrapperDelegate(Of T, A)

 

    Public Sub New(ByVal argument As A, _

        ByVal wrapperDelegate As PredicateWrapperDelegate(Of T, A))

 

        _argument = argument

        _wrapperDelegate = wrapperDelegate

    End Sub

 

    Private Function InnerPredicate(ByVal item As T) As Boolean

        Return _wrapperDelegate(item, _argument)

    End Function

 

    Public Shared Widening Operator CType( _

        ByVal wrapper As PredicateWrapper(Of T, A)) _

        As Predicate(Of T)

 

        Return New Predicate(Of T)(AddressOf wrapper.InnerPredicate)

    End Operator

 

End Class

Wherever you want to use Array(Of T).FindAll you call it like this and pass a “finder method” that you define nearby:

    Public Shared Function GetAgencies(ByVal secretariatCode As String) As Agency()

        Dim agList As Agency() = WSHelper.GetAgencies()

        Dim agSearchObj As New Agency

        agSearchObj.SecretariatCode = secretariatCode

       

        Return Array.FindAll(Of Agency)(agList, New PredicateWrapper(Of Agency, _
         Agency)(agSearchObj, AddressOf AgencyMatch))

 

    End Function

 

    Private Shared Function AgencyMatch(item As Agency, argument As Agency) As Boolean

        Return item.SecretariatCode = argument.SecretariatCode

    End Function

Note: specifying the array type (e.g. Array.FindAll (Of Agency)  ) is critical, otherwise you get an error that says: Type argument inference failed for type parameter 'T' of 'Public Shared Function FindAll(Of T)(array() As T, match As System.Predicate(Of T)) As T()'. Type argument inferred from the argument passed to parameter 'match' conflicts with the type argument inferred from the argument passed to parameter 'array'.

I found it a little easier to use List(Of T).FindAll since it does not require that you pass the array (or specify the type).  An example of calling FindAll:

    WSHelper.GetAgencyList.FindAll(New PredicateWrapper(Of AjaxAgency, _
      AjaxAgency)(ajaxAgencyToFind, AddressOf AgencyMatch2))

 

    Private Function AgencyMatch2(ByVal item As AjaxAgency, ByVal argument As AjaxAgency) As Boolean

        Return item.secretariatCode = argument.secretariatCode

    End Function

A special thanks to Paul Stovell for his original creation of the PredicateWrapper.

 

Wednesday, November 5, 2008

Upload Oracle BLOB in Stored Procedure using OracleClient OracleLob

I ran into the 32k limit on OracleClient parameters of stored procedures when trying to upload documents to Oracle using a byte array:


comm = New OracleCommand("ADD_DOCUMENT_PROC", conn)
comm.CommandType = CommandType.StoredProcedure
comm.Parameters.Add("IN_PARENT_ID", OracleType.Number).Value = parentId
comm.Parameters.Add("IN_DOC_TYPE", OracleType.VarChar).Value = docType
comm.Parameters.Add("IN_DOC_BLOB", OracleType.Blob).Value = o.DocumentBlob
comm.ExecuteNonQuery()

Then I found a “fast way” using the Oracle.DataAccess client.


Dim startOffset As Integer = 0
Dim writeBytes As Integer = 0

conn.Open()
Dim blob As New OracleBlob(conn)

blob.BeginChunkWrite()
Do
writeBytes = IIf(startOffset + BUFFER_SIZE > o.DocumentBlob.Length, o.DocumentBlob.Length - startOffset, BUFFER_SIZE)
blob.Write(o.DocumentBlob, startOffset, writeBytes)
startOffset += writeBytes
Loop While startOffset < o.DocumentBlob.Length
blob.EndChunkWrite()

comm = New OracleCommand("ADD_DOCUMENT_PROC", conn)
comm.CommandType = CommandType.StoredProcedure
comm.Parameters.Add("IN_PARENT_ID", OracleDbType.Int32).Value = parentId
comm.Parameters.Add("IN_DOC_TYPE", OracleDbType.Varchar2).Value = docType
comm.Parameters.Add("IN_DOC_BLOB", OracleDbType.Blob).Value = blob
comm.ExecuteNonQuery()
It worked locally, but the client is not installed on our servers, so going back to the ADO.Net OracleClient, I found an example in the MSDN docs that is working quite well. It seems the key is to use DBMS_LOB.CREATETEMPORARY:

Private Sub AddDocument(ByVal parentId As Integer, ByVal docType As String, ByVal o As Document)
Dim conn As OracleConnection
Dim comm As OracleCommand

Try

conn = OpenConnection()

Dim tx As OracleTransaction = conn.BeginTransaction()

comm = conn.CreateCommand()
comm.Transaction = tx

' This will create a temp blob object in the DB
comm.CommandText = "declare xx blob; " & _
"begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;"

comm.Parameters.Add(New OracleParameter("tempblob", _
OracleType.Blob)).Direction = ParameterDirection.Output
comm.ExecuteNonQuery()

Dim tempLob As OracleLob
tempLob = comm.Parameters(0).Value
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite)

' You may be able to chunk this to improve performance?
tempLob.Write(o.DocumentBlob, 0, o.DocumentBlob.Length)
tempLob.EndBatch()

comm.Parameters.Clear()
comm.CommandText = "ADD_DOCUMENT_PROC"
comm.CommandType = CommandType.StoredProcedure

' Add the BLOB object parameter
comm.Parameters.Add("IN_DOC_BLOB", OracleType.Blob).Value = tempLob

comm.Parameters.Add("IN_PARENT_ID", OracleType.Number).Value = parentId
comm.Parameters.Add("IN_DOC_TYPE", OracleType.VarChar).Value = docType

comm.ExecuteNonQuery()

' You want to explicitly destroy the LOB obj
tempLob.Dispose()

tx.Commit()
comm.Dispose()

Finally
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
conn = Nothing
End Try

End Sub