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

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

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

Dim blob As New OracleBlob(conn)

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

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
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


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

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

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

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


' You want to explicitly destroy the LOB obj


If conn.State <> ConnectionState.Closed Then
End If
conn = Nothing
End Try

End Sub