Code the Global Change - Part 1

Previous: Retrieve the TimeZoneEntry

In this section, I will provide a brief explanation of the CLR code that implements the bulk of the business logic along with the USR_USP_ADDRESSCOORDINATETIMEZONE_GCPROCESS_UPDATE stored procedure which will be covered in a following section.

Tip: The best way to walk through this code is to debug the global change within Visual Studio. For details on debugging a global change, see Debugging.

The code will accomplish the following:

Obtain the parameter values for the global change instance.
Use parameter values to retrieve a set of address coordinate values to process.
For each address coordinate, call to the Google Time Zone API and retrieve the name of time zone.
Use the name of the time zone to obtain the Time Zone GUID from the TIMEZONEENTRY table.
Build an XML message representing the rows of time zones to add/update.
Pass the XML message to the USR_USP_ADDRESSCOORDINATETIMEZONE_GCPROCESS_UPDATE stored procedure to add/edit the time zones to the USR_ADDRESSCOORDINATETIMEZONE.
Return the result including numbers of rows inserted and updated.

Tip: The source code includes a Visual Studio 2010 solution and server side assembly project containing the XML specs/catalog items and VB class file. The source code is included with this example within a file named AddressCoordinateTimeZone.GlobalChange.vb. Grab the source from the repo in GitHub.

Obtain the Parameter Values for the Global Change Instance

Step 1 - Open the CLR code file and add the code below.

The IDSetRegisterID, OnlyPrimary, OnlyChanged variables are declared as PUBLIC. In this way the system can pass the parameter values into the code at runtime.

Note how we inheriting AppGlobalChangeProcess and overriding ProcessGlobalChange() function. The system will call this function when the instance is started. The ProcessGlobalChange() function must return an object of type AppGlobalChangeResult, which will contain the number of rows inserted, updated, and deleted.

Imports Blackbaud.AppFx.Server
Imports System.Xml
Imports System.Data.SqlClient
Imports Blackbaud.AppFx.XmlTypes
Public NotInheritable Class AddressCoordinateTimeZoneGlobalChange
    Inherits AppCatalog.AppGlobalChangeProcess

    Public IDSetRegisterID As Guid
    Public OnlyPrimary As Boolean?
    Public OnlyChanged As Boolean?
    Private AsOf As DateTime

    Private _expectedExceptions As ExpectedDBExceptions

    Public Overrides Function ProcessGlobalChange() As AppCatalog.AppGlobalChangeResult
        Dim changeAgentID As Guid = RequestContext.GetChangeAgentID()
        Dim numberUpdated As Integer = 0
        Dim numberInserted As Integer = 0
        Dim numberExceptions As Integer = 0

				AsOf = MyBase.RequestArgs.LastRunOn

Use Parameter Values to Retrieve a Set of Address Coordinate Values to Process

Grab a connection to the Infinity database within the connection context of the business process.

Closing Connections

Sometimes your code requires an unmanaged resource, such as a SQL connection. If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. You may also consider a Using block which guarantees the disposal of one or more such resources when your code is finished with them. For more information see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.close.aspx and http://msdn.microsoft.com/en-us/library/htd05whh.aspx.

For example, the following code would require the explicit closing of the connection:

Dim conn As SqlConnection = Me.RequestContext.OpenAppDBConnection
		‘code to perform work with the sql connection
conn.Close

Whereas utilizing a Using block guarantees the disposal of the SQLConnection:

using conn As SqlConnection = Me.RequestContext.OpenAppDBConnection
        ‘code to perform work with the sql connection
end using

Build a dynamical T-SQL SELECT statement that returns rows from the AddressCoordinates table. The code below utilizes the parameter values to determine the portions of the SELECT statement that restricts the amount of rows returned. You can review the entire, completed code sample at the bottom of this content within the section named Completed CLR Class.

        
		Dim baseTableName As String = "dbo.CONSTITUENT"
        Dim withClause As String = BuildRecordSecurityWithClause("CONSTITUENT", "Constituent", "ID")
        Dim FilterOutUnmodified As Boolean = OnlyChanged.HasValue AndAlso OnlyChanged.Value
        If Len(withClause) > 0 Then
            baseTableName = "CONSTITUENT_RACS"
        End If

        Dim commandText As String = withClause & _
                                   "SELECT " & _
                                   "   ADDRESSCOORDINATES.ID " & _
                                   "   , ADDRESSCOORDINATES.LATITUDE " & _
                                   "   , ADDRESSCOORDINATES.LONGITUDE  " & _
                                   "FROM " & baseTableName & " as C " & _
                                   "INNER JOIN dbo.ADDRESS on ADDRESS.CONSTITUENTID = C.ID " & _
                                   "INNER JOIN dbo.ADDRESSCOORDINATES on ADDRESSCOORDINATES.ADDRESSID = ADDRESS.ID "

        If IDSetRegisterID <> Guid.Empty Then
            Dim idSetReader As New Blackbaud.AppFx.Server.IDSetReader(IDSetRegisterID, RequestContext)
            commandText &= String.Format("inner join {0} SELECTION on SELECTION.{1} = C.ID ", idSetReader.GetResultsTableOrFunctionName(), idSetReader.IDColumnName)
        End If

        If FilterOutUnmodified Then
            commandText &= "LEFT JOIN dbo.USR_ADDRESSCOORDINATETIMEZONE on USR_ADDRESSCOORDINATETIMEZONE.ID = ADDRESSCOORDINATES.ID "
        End If

        If FilterOutUnmodified Then
            commandText &= "where (USR_ADDRESSCOORDINATETIMEZONE.ID is null or USR_ADDRESSCOORDINATETIMEZONE.DATECHANGED < ADDRESSCOORDINATES.DATECHANGED) "
        End If

        If OnlyPrimary Then
            If FilterOutUnmodified Then
                commandText &= "and ADDRESS.ISPRIMARY = 1 "
            Else
                commandText &= "where ADDRESS.ISPRIMARY = 1 "
            End If
        End If

        commandText &= "order by ADDRESSCOORDINATES.DATEADDED"

        command = theConn.CreateCommand()
        command.CommandText = commandText
        command.CommandTimeout = 20
        command.CommandType = CommandType.Text

For Each Address Coordinate, Call to the Google Time Zone API and Retrieve the Name of Time Zone

Loop through the rows and begin building an XML string. Pass the latitude and logiture to GetTimeZone() to retrieve time zone name via the API. Within GetTimeZone(), call GetTimeZoneEntryID() to grab the GUID for the time zone from the database. Use the TimeZoneResponse class to returned the time zone information. Report any issues via an ENUM named "Status." Build an XML message representing the rows of time zones to add/update. Call the SP named USR_USP_ADDRESSCOORDINATETIMEZONE_GCPROCESS_UPDATE to insert and/or update the rows in the database. Return the result this database call including numbers of rows inserted and updated.

See the section named Completed CLR Class at the bottom of this topic for details on GetTimeZone(), GetTimeZoneEntryID(), and the TimeZoneResponse class.

	
		Dim mergeXMLMsg As String
        mergeXMLMsg = "<ROOT>"
        Try
            Dim reader As SqlDataReader = command.ExecuteReader()
            While reader.Read()
                Dim ADDRESSCOORDINATESID As Guid = reader.GetGuid(0)
                Dim ADDRESSCOORDINATESLATITUDE As Decimal = reader.GetDecimal(1)
                Dim ADDRESSCOORDINATESLONGITUDE As Decimal = reader.GetDecimal(2)

                ' Make call to GetTimeZone function which uses an experimental Google maps time zone api
                ' The use of this service within this global change code is for educational purposes only and 
                ' is not meant for production.  
                ' This service has usage limits. 
                'https://developers.google.com/maps/documentation/timezone/
                Dim TimeZoneSvcResponse As TimeZoneResponse = GetTimeZone(ADDRESSCOORDINATESLATITUDE.ToString, ADDRESSCOORDINATESLONGITUDE.ToString)


                'Using result of calls to service and the lat and longitude, build up an xml message and pass
                'to stored procedude which will update the custom USR_ADDRESSCOORDINATETIMEZONE table
                ' Example xml message passed to stored procedure.
                '<ROOT>
                '<TimeZoneResponse ADDRESSCOORDINATESID="AEC02AB8-430B-48B8-A32C-348F98C04C2B" StatusCode="1" StatusDesc="The request was successful." TimeZoneEntryID="B6CE5E5E-236F-437E-9A42-DFDB945B5286"/>
                '<TimeZoneResponse ADDRESSCOORDINATESID="E457774D-97E4-4F0C-A35B-5020347C2EAD" StatusCode="1" StatusDesc="The request was successful." TimeZoneEntryID="B6CE5E5E-236F-437E-9A42-DFDB945B5286"/>
                '<TimeZoneResponse ADDRESSCOORDINATESID="41BDE2D6-C9F7-4AB6-86DC-129A01F98920" StatusCode="1" StatusDesc="The request was successful." TimeZoneEntryID="B6CE5E5E-236F-437E-9A42-DFDB945B5286"/>
                '<TimeZoneResponse ADDRESSCOORDINATESID="B9E1C919-811E-4B88-A39E-D8203FF08BAD" StatusCode="1" StatusDesc="The request was successful." TimeZoneEntryID="B6CE5E5E-236F-437E-9A42-DFDB945B5286"/>
                '</ROOT>

                mergeXMLMsg &= "<TimeZoneResponse ADDRESSCOORDINATESID=""" & ADDRESSCOORDINATESID.ToString & """ StatusCode=""" & TimeZoneSvcResponse.ResponseStatus & """ StatusDesc=""" & TimeZoneSvcResponse.ResponseStatusDesc.ToString & """ TimeZoneEntryID=""" & TimeZoneSvcResponse.TimeZoneEntryID.ToString & """/>"
            End While

            mergeXMLMsg &= "</ROOT>"

            reader.Close()
            

            Dim DBCommand As SqlCommand = New SqlCommand("USR_USP_ADDRESSCOORDINATETIMEZONE_GCPROCESS_UPDATE", theConn)
            DBCommand.CommandType = CommandType.StoredProcedure

            Dim xmlDocParm As SqlParameter = DBCommand.Parameters.Add("@doc", SqlDbType.NVarChar, -1)
            xmlDocParm.Value = mergeXMLMsg

            Dim changeAgentParm As SqlParameter = DBCommand.Parameters.Add("@CHANGEAGENTID", SqlDbType.UniqueIdentifier)
            changeAgentParm.Value = changeAgentID

            Dim myReader As SqlDataReader = DBCommand.ExecuteReader()
            Dim changeType As String

            Do While (myReader.Read())
                changeType = myReader.GetString(0)
                If changeType = "UPDATE" Then
                    numberUpdated = myReader.GetInt32(1)
                ElseIf changeType = "INSERT" Then
                    numberInserted = myReader.GetInt32(1)
                End If
            Loop

            myReader.Close()
            theConn.Close()

        Catch sqlEx As Exception
            'Build the collection of expected exceptions
            
            BuildExceptions("SQL Exception", sqlEx.Message)

            MyBase.HandleSQLException(sqlEx, _expectedExceptions)
        End Try

        Dim result As New AppCatalog.AppGlobalChangeResult(numberInserted, numberUpdated, 0)

        Return result
    End Function

Step 2 - Review the CLR class

Review the completed class below. We will deploy the class at the end of this exercise.

Completed CLR Class


Imports Blackbaud.AppFx.Server
Imports System.Xml
Imports System.Data.SqlClient
Imports Blackbaud.AppFx.XmlTypes
Public NotInheritable Class AddressCoordinateTimeZoneGlobalChange
    Inherits AppCatalog.AppGlobalChangeProcess

    Public IDSetRegisterID As Guid
    Public OnlyPrimary As Boolean?
    Public OnlyChanged As Boolean?
    Private AsOf As DateTime

    Private _expectedExceptions As ExpectedDBExceptions

    Public Overrides Function ProcessGlobalChange() As AppCatalog.AppGlobalChangeResult
        Dim changeAgentID As Guid = RequestContext.GetChangeAgentID()
        Dim numberUpdated As Integer = 0
        Dim numberInserted As Integer = 0
        Dim numberExceptions As Integer = 0

        If Not MyBase.RequestArgs.LastRunOn Is Nothing Then
            AsOf = MyBase.RequestArgs.LastRunOn
        End If

        _expectedExceptions = New ExpectedDBExceptions

        Dim theConn As SqlConnection = RequestContext.OpenAppDBConnection(RequestContext.ConnectionContext.BusinessProcess)
        Dim command As SqlCommand

        Dim baseTableName As String = "dbo.CONSTITUENT"
        Dim withClause As String = BuildRecordSecurityWithClause("CONSTITUENT", "Constituent", "ID")
        Dim FilterOutUnmodified As Boolean = OnlyChanged.HasValue AndAlso OnlyChanged.Value
        If Len(withClause) > 0 Then
            baseTableName = "CONSTITUENT_RACS"
        End If

        Dim commandText As String = withClause & _
                                   "SELECT " & _
                                   "   ADDRESSCOORDINATES.ID " & _
                                   "   , ADDRESSCOORDINATES.LATITUDE " & _
                                   "   , ADDRESSCOORDINATES.LONGITUDE  " & _
                                   "FROM " & baseTableName & " as C " & _
                                   "INNER JOIN dbo.ADDRESS on ADDRESS.CONSTITUENTID = C.ID " & _
                                   "INNER JOIN dbo.ADDRESSCOORDINATES on ADDRESSCOORDINATES.ADDRESSID = ADDRESS.ID "

        If IDSetRegisterID <> Guid.Empty Then
            Dim idSetReader As New Blackbaud.AppFx.Server.IDSetReader(IDSetRegisterID, RequestContext)
            commandText &= String.Format("inner join {0} SELECTION on SELECTION.{1} = C.ID ", idSetReader.GetResultsTableOrFunctionName(), idSetReader.IDColumnName)
        End If

        If FilterOutUnmodified Then
            commandText &= "LEFT JOIN dbo.USR_ADDRESSCOORDINATETIMEZONE on USR_ADDRESSCOORDINATETIMEZONE.ID = ADDRESSCOORDINATES.ID "
        End If

        If FilterOutUnmodified Then
            commandText &= "where (USR_ADDRESSCOORDINATETIMEZONE.ID is null or USR_ADDRESSCOORDINATETIMEZONE.DATECHANGED < ADDRESSCOORDINATES.DATECHANGED) "
        End If

        If OnlyPrimary Then
            If FilterOutUnmodified Then
                commandText &= "and ADDRESS.ISPRIMARY = 1 "
            Else
                commandText &= "where ADDRESS.ISPRIMARY = 1 "
            End If
        End If

        commandText &= "order by ADDRESSCOORDINATES.DATEADDED"

        command = theConn.CreateCommand()
        command.CommandText = commandText
        command.CommandTimeout = 20
        command.CommandType = CommandType.Text

        Dim mergeXMLMsg As String
        mergeXMLMsg = "<ROOT>"
        Try
            Dim reader As SqlDataReader = command.ExecuteReader()
            While reader.Read()
                Dim ADDRESSCOORDINATESID As Guid = reader.GetGuid(0)
                Dim ADDRESSCOORDINATESLATITUDE As Decimal = reader.GetDecimal(1)
                Dim ADDRESSCOORDINATESLONGITUDE As Decimal = reader.GetDecimal(2)

                ' Make call to GetTimeZone function which uses an experimental Google maps time zone api
                ' The use of this service within this global change code is for educational purposes only and 
                ' is not meant for production.  
                ' This service has usage limits. 
                'https://developers.google.com/maps/documentation/timezone/
                Dim TimeZoneSvcResponse As TimeZoneResponse = GetTimeZone(ADDRESSCOORDINATESLATITUDE.ToString, ADDRESSCOORDINATESLONGITUDE.ToString)


                'Using result of calls to service and the lat and longitude, build up an xml message and pass
                'to stored procedude which will update the custom USR_ADDRESSCOORDINATETIMEZONE table
                ' Example xml message passed to stored procedure.
                '<ROOT>
                '<TimeZoneResponse ADDRESSCOORDINATESID="AEC02AB8-430B-48B8-A32C-348F98C04C2B" StatusCode="1" StatusDesc="The request was successful." TimeZoneEntryID="B6CE5E5E-236F-437E-9A42-DFDB945B5286"/>
                '<TimeZoneResponse ADDRESSCOORDINATESID="E457774D-97E4-4F0C-A35B-5020347C2EAD" StatusCode="1" StatusDesc="The request was successful." TimeZoneEntryID="B6CE5E5E-236F-437E-9A42-DFDB945B5286"/>
                '<TimeZoneResponse ADDRESSCOORDINATESID="41BDE2D6-C9F7-4AB6-86DC-129A01F98920" StatusCode="1" StatusDesc="The request was successful." TimeZoneEntryID="B6CE5E5E-236F-437E-9A42-DFDB945B5286"/>
                '<TimeZoneResponse ADDRESSCOORDINATESID="B9E1C919-811E-4B88-A39E-D8203FF08BAD" StatusCode="1" StatusDesc="The request was successful." TimeZoneEntryID="B6CE5E5E-236F-437E-9A42-DFDB945B5286"/>
                '</ROOT>

                mergeXMLMsg &= "<TimeZoneResponse ADDRESSCOORDINATESID=""" & ADDRESSCOORDINATESID.ToString & """ StatusCode=""" & TimeZoneSvcResponse.ResponseStatus & """ StatusDesc=""" & TimeZoneSvcResponse.ResponseStatusDesc.ToString & """ TimeZoneEntryID=""" & TimeZoneSvcResponse.TimeZoneEntryID.ToString & """/>"
            End While

            mergeXMLMsg &= "</ROOT>"

            reader.Close()
            command.Dispose()

            Dim DBCommand As SqlCommand = New SqlCommand("USR_USP_ADDRESSCOORDINATETIMEZONE_GCPROCESS_UPDATE", theConn)
            DBCommand.CommandType = CommandType.StoredProcedure

            Dim xmlDocParm As SqlParameter = DBCommand.Parameters.Add("@doc", SqlDbType.NVarChar, -1)
            xmlDocParm.Value = mergeXMLMsg

            Dim changeAgentParm As SqlParameter = DBCommand.Parameters.Add("@CHANGEAGENTID", SqlDbType.UniqueIdentifier)
            changeAgentParm.Value = changeAgentID

            Dim myReader As SqlDataReader = DBCommand.ExecuteReader()
            Dim changeType As String

            Do While (myReader.Read())
                changeType = myReader.GetString(0)
                If changeType = "UPDATE" Then
                    numberUpdated = myReader.GetInt32(1)
                ElseIf changeType = "INSERT" Then
                    numberInserted = myReader.GetInt32(1)
                End If
            Loop

            myReader.Close()
            DBCommand.Dispose()
            theConn.Close()

        Catch sqlEx As Exception
            'Build the collection of expected exceptions

            BuildExceptions("SQL Exception", sqlEx.Message)

            MyBase.HandleSQLException(sqlEx, _expectedExceptions)
        End Try

        Dim result As New AppCatalog.AppGlobalChangeResult(numberInserted, numberUpdated, 0)

        Return result
    End Function

   

    Private Function GetTimeZone(Latitude As String, Longitude As String) As TimeZoneResponse

        Dim doc As XmlDocument
        Dim mainnode As XmlNode
        Dim Location As String = Latitude.Trim & "," & Longitude.Trim
        Dim RequestStatus As String

        ' Create a new XmlDocument  
        doc = New XmlDocument()

        'Make call to  experimental Google maps time zone api
        ' The use of this service within this global change code is for educational purposes only and 
        ' is not meant for production.  
        ' This service has usage limits. 
        'See https://developers.google.com/maps/documentation/timezone/
        ' Load data  
        doc.Load("https://maps.googleapis.com/maps/api/timezone/xml?location=" & Location & "&timestamp=0&sensor=false")

        mainnode = doc.SelectSingleNode("TimeZoneResponse")
        RequestStatus = mainnode.SelectSingleNode("status").InnerText

        If mainnode Is Nothing Then
            Return Nothing

        ElseIf RequestStatus = "OK" Then
            Dim timezonename As String = mainnode.SelectSingleNode("time_zone_name").InnerText
            Dim TimeZoneGuid As System.Guid = GetTimeZoneEntryID(timezonename)

            If TimeZoneGuid = System.Guid.Empty Then
                Return New TimeZoneResponse(TimeZoneResponse.Status.TIMEZONEENTRY_FK_NOTFOUND, "indicates no row found in TIMEZONEENTRY table in DB for '" & timezonename & "'.", "", System.Guid.Empty)
            Else
                Return New TimeZoneResponse(TimeZoneResponse.Status.OK, "The request was successful.", timezonename, TimeZoneGuid)
            End If

        ElseIf RequestStatus = "INVALID_REQUEST" Then
            Return New TimeZoneResponse(TimeZoneResponse.Status.INVALID_REQUEST, "The API request was malformed.", "", System.Guid.Empty)
        ElseIf RequestStatus = "OVER_QUERY_LIMIT" Then
            Return New TimeZoneResponse(TimeZoneResponse.Status.OVER_QUERY_LIMIT, "The requestor has exceeded API quota.", "", System.Guid.Empty)
        ElseIf RequestStatus = "REQUEST_DENIED" Then
            Return New TimeZoneResponse(TimeZoneResponse.Status.REQUEST_DENIED, "The API did not complete the request. Confirm that the request was sent over http instead of https.", "", System.Guid.Empty)
        ElseIf RequestStatus = "UNKNOWN_ERROR" Then
            Return New TimeZoneResponse(TimeZoneResponse.Status.UNKNOWN_ERROR, "indicates an unknown API error.", "", System.Guid.Empty)
        ElseIf RequestStatus = "ZERO_RESULTS" Then
            Return New TimeZoneResponse(TimeZoneResponse.Status.ZERO_RESULTS, "indicates that no time zone API data could be found for the specified position or time. Confirm that the request is for a location on land, and not over water.", "", System.Guid.Empty)
        Else
            Return New TimeZoneResponse(TimeZoneResponse.Status.GENERAL_ERROR, "indicates a general error.", "", System.Guid.Empty)
        End If
       
    End Function

    Private Function GetTimeZoneEntryID(TimeZoneEntry As String) As System.Guid

        Dim DBConn As SqlConnection = RequestContext.OpenAppDBConnection(RequestContext.ConnectionContext.BusinessProcess)
        Dim DBCommand As SqlCommand = New SqlCommand("USR_USP_TIMEZONEENTRY_GETBYNAME", DBConn)

        DBCommand.CommandType = CommandType.StoredProcedure

        Dim myParm As SqlParameter = DBCommand.Parameters.Add("@TimeZoneName", SqlDbType.NVarChar, 400)

        myParm.Value = TimeZoneEntry

        Dim myReader As SqlDataReader
        Dim retval As System.Guid
        Try
            myReader = DBCommand.ExecuteReader()

            'EXEC(USR_USP_TIMEZONEENTRY_GETBYNAME) 'Eastern Standard Time'

            Do While (myReader.Read())

                If myReader.IsDBNull(0) Then
                    retval = System.Guid.Empty
                Else
                    Console.WriteLine("{0}", myReader.GetGuid(0))
                    retval = myReader.GetGuid(0)
                End If

            Loop
            myReader.Close()
        Catch ex As Exception
            Throw ex
        Finally
            DBCommand.Dispose()
            DBConn.Close()
        End Try

        Return retval

    End Function

    Protected Class TimeZoneResponse
        Public Enum Status As Integer
            OK = 1
            INVALID_REQUEST = 2
            OVER_QUERY_LIMIT = 3
            REQUEST_DENIED = 4
            UNKNOWN_ERROR = 5
            ZERO_RESULTS = 6
            GENERAL_ERROR = 7
            TIMEZONEENTRY_FK_NOTFOUND = 8
        End Enum

        Private _responseStatus As Status
        Private _responseStatusDesc As String
        Private _timeZone As String
        Private _timeZoneEntryID As Guid

        Sub New(ByVal responseStatus As Status, ByVal responseStatusDesc As String, ByVal timeZone As String, timeZoneEntryID As Guid)
            _responseStatus = responseStatus
            _responseStatusDesc = responseStatusDesc
            _timeZone = timeZone
            _timeZoneEntryID = timeZoneEntryID
        End Sub

        Public ReadOnly Property ResponseStatus As Status
            Get
                Return _responseStatus
            End Get
        End Property

        Public ReadOnly Property ResponseStatusDesc As String
            Get
                Return _responseStatusDesc
            End Get
        End Property

        Public ReadOnly Property TimeZone As String
            Get
                Return _timeZone
            End Get
        End Property

        Public ReadOnly Property TimeZoneEntryID As Guid
            Get
                Return _timeZoneEntryID
            End Get
        End Property

    End Class

    Private Sub BuildExceptions(ByVal SearchText As String, ByVal CustomErrorMsg As String)
        _expectedExceptions = New ExpectedDBExceptions
        ReDim _expectedExceptions.CustomExceptions(0)

        Dim invalidSelection As New CustomExceptionDescriptor
        invalidSelection.SearchText = SearchText
        invalidSelection.CustomErrorMsg = CustomErrorMsg

        _expectedExceptions.CustomExceptions(0) = invalidSelection
    End Sub
End Class

Next: Code the Processing Stored Procedure