Rewriting the OOB duplicate checking for constituents

Okay so to start with this was an extremely difficult task, but not an impossible one. There are many parts to this customization, but I will only cover the basics. Future posts will show what features I added. This customization contains 4 specs, all of which may not necessarily be needed, but we are going to assume they are.

  • SearchList – SP (used to return the possible duplicates)
  • VB/C# Module file (where the duplicate matching takes place, ie: string comparison, other string algorithms)
  • StoredProcedure (called by the class file above to select the initial dataset of constituents)
  • StoredProcedure (used to call the class file)

The reason for all of these files is simple. The SearchList cannot be CLR because of some of the limitations of the OOB system, therefore create a SP SearchList and have it call an assembly that is housed on your SQL Server.
Now the first thing step in this process is to actually write your duplicate checking module that will be compiled and housed on your SQL Server.

Start with skeleton of the module:

#Region "Imports"
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
#End Region

Public Module DuplicateRecordChecker
    <SqlProcedure()> _
    Public Sub GetMatchedConstituents(ByVal ID As SqlGuid, ByVal KeyName As SqlString, ByVal FirstName As SqlString)

    End Sub
End Module

Now inside of the GetMatchedConstituents Sub you will want to put your code to select possible matches through query and then implement your duplicate checking algorithm. For this example we are just going to check for constituents that have a first and last name in common. Now all of this could be done without the implementation of this VB module, however for added extensibility and to follow how the OOB duplicate check works we will use it. Please refer to the comments in the code for explanations of what is being done.

#Region "Imports"
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
#End Region

Public Module DuplicateRecordChecker
        'steps for checking for duplicates
    '1.  i format all fields needed to pass into my stored procedure
    '2.  i get all of the constituents that have at least one attribute 
    '    in common using usr_usp_gettrimmedconstituents 
    '3.  simple enough i then loop through the returned datatable, fill
    '    variables, and check all of the possible constituents against
    '    the definitive and candidate matching logic to find certain matches
    '3a. if the constituent isnt a match then i put it in a list
    '4.  i remove all constituents that arent certain matches from the 
    '    datatable and then push everything into sqldatarecords
    '5.  the sqldatarecords are then sent over the pipe back to the 
    '    calling stored procedure which i must say is pretty sexy
    <SqlProcedure()> _
    Public Sub GetMatchedConstituents(ByVal ID As SqlGuid, ByVal KeyName As SqlString, ByVal FirstName As SqlString)

        'important to use using on any ado.net classes that implement idisposable
        'because once the using statement has been left the object is disposed of        
        Using conn As New SqlConnection("context connection=true")
            Using cmd As New SqlCommand("USR_USP_GETTRIMMEDCONSTITUENTS", conn)

                'format incoming parameters
                Dim sKeyName As String = FormatString(KeyName)
                Dim sFirstName As String = FormatString(FirstName)

                'add all of parameters needed for the stored procedure called
                cmd.Parameters.AddWithValue("@FIRSTNAME", sFirstName)
                cmd.Parameters.AddWithValue("@KEYNAME", sKeyName)

                'important because the default for commandtype is text
                'ie: select * from constituent
                cmd.CommandType = CommandType.StoredProcedure

                Using dta As New SqlDataAdapter(cmd)
                    Dim constitsDataTable As New DataTable
                    Dim nonMatches As New List(Of DataRow)

                    cmd.Connection.Open()

                    'fill the constits datatable with the trimmed down constituents
                    dta.Fill(constitsDataTable)

                    'iterating through each constituent that may be a possible match
                    'and then checking it fully against all of the matching logic
                    For Each dr As DataRow In constitsDataTable.Rows
                        Try
                            'get values from datarow, check to see if they are null
                            'and format strings as needed
                            Dim temp_keyName As String = If(Not IsDBNull(dr("KEYNAME")), FormatString(dr("KEYNAME").ToString()), Nothing)
                            Dim temp_firstName As String = If(Not IsDBNull(dr("FIRSTNAME")), FormatString(dr("FIRSTNAME").ToString()), Nothing)

                            'time to check all of the values against the duplicate logic matrix
                            If ((Not String.IsNullOrEmpty(temp_keyName)) And sKeyName.Equals(temp_keyName)) And _
                                ((Not String.IsNullOrEmpty(temp_firstName)) And sFirstName.Equals(temp_firstName)) Then
                                nonMatches.Add(dr)
                                Continue For
                            End If
                        Catch ex As Exception
                            'we do nothing with errors here.
                        End Try
                    Next

                    'here i am removing the constituents from the final datatable that arent 
                    'needed before they are sent back to the stored procedure 
                    For Each dr As DataRow In nonMatches
                        constitsDataTable.Rows.Remove(dr)
                    Next

                    'to actually send the results in the datatable back to the client, tsql sproc,
                    'i had to either use xml, ugh, or the sqlcontext.pipe class
                    'url: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlpipe.aspx
                    'outputcolumns just holds all of the column data in the datatable
                    Dim outputColumns As New List(Of SqlMetaData)

                    'dynamically get the type and name of the columns in the datatable
                    'and put that data in the above list as sqlmetadata
                    For Each col As DataColumn In constitsDataTable.Columns
                        'this stupid stupid stupid type is needed, yeah i know you think that 
                        'nvarchar or varchar would work, but it doesnt and i know that msdn says
                        'that it should but it doesnt. so you have to have the text type
                        Dim sqltype As SqlDbType = SqlDbType.Text

                        If col.ColumnName = "ID" Then
                            sqltype = SqlDbType.UniqueIdentifier
                        End If

                        outputColumns.Add(New SqlMetaData(col.ColumnName, sqltype))
                    Next

                    'create the sqldatarecord with the list of metadata
                    Dim record As New SqlDataRecord(outputColumns.ToArray())

                    'start sending the results, this is used if a datareader is not an option
                    'and the record passed in MUST contain the metadata
                    SqlContext.Pipe.SendResultsStart(record)

                    'iterate through every row in the datatable and then each column in that row
                    'and set the value of the records column to the actual value in the correct
                    'row/column in the datatable, seems pretty bulky but it's all we got
                    For Each row As DataRow In constitsDataTable.Rows
                        For col = 0 To constitsDataTable.Columns.Count - 1
                            record.SetValue(col, (row.ItemArray(col)))
                        Next

                        'send that record back to client one at a time, i know i know
                        SqlContext.Pipe.SendResultsRow(record)
                    Next

                    'stop sending results, just marks the end of the sending 
                    'and puts the pipe back to the initial state
                    SqlContext.Pipe.SendResultsEnd()
                End Using
            End Using
        End Using
    End Sub

    'checks to see if the incoming string is null or empty
    'if it is then we just return an empty string, lol
    'if it isnt then we remove whitespace and lower 
    'the string before returning it
    Private Function FormatString(ByVal s As SqlString) As String
        'honestly if you need comments here then you shouldnt have visual studio open
        Try
            If s.IsNull Then
                Throw New Exception
            Else
                Return s.ToString().Trim().ToLower().Replace(" ", "")
            End If
        Catch ex As Exception
            Return String.Empty
        End Try
    End Function
End Module

Now once you have your code completed and compiled, (make sure that the target framework is set to 3.5. project properties –> compile –> advanced compiler settings).

image_thumb_567

You will want to add this assembly to SQL Server by running the following script:

create assembly [Blackbaud.CustomFx.Sample.SqlClr] 
from 'C:\Users\chriswh\Documents\Visual Studio 2010\Projects\Blackbaud.CustomFx.Sample\SqlClr\bin\Debug\Blackbaud.CustomFx.Sample.SqlClr.dll'
with permission_set = safe
go

Verify that this has been added to your assemblies in your database.

image_thumb_1_1

The hard part is over. Yay!

Now to create the specs. Let’s start with the stored procedure that get’s called by the assembly and then the stored procedure that is going to call the assembly.

<SQLStoredProcedureSpec
    xmlns="bb_appfx_sqlstoredprocedure"
    xmlns:common="bb_appfx_commontypes" 
    ID="38143663-9b85-4f3a-b209-fab460dee5b6"
    Name="USR_USP_GETTRIMMEDCONSTITUENTS"
    Description="USR_USP_GETTRIMMEDCONSTITUENTS"
    Author="Blackbaud Professional Services"
    SPName="USR_USP_GETTRIMMEDCONSTITUENTS"
    >

    <CreateProcedureSQL>
        <![CDATA[
            create procedure dbo.USR_USP_GETTRIMMEDCONSTITUENTS
	    (
		@FIRSTNAME nvarchar(100),
		@KEYNAME nvarchar(100)
  	    )
            as
            begin
                select ID, FIRSTNAME, KEYNAME from CONSTITUENT
	       where FIRSTNAME like '%' + @FIRSTNAME + '%' and KEYNAME like '%' + @KEYNAME + '%'
            end
        ]]>
    </CreateProcedureSQL>
</SQLStoredProcedureSpec>

 

<SQLStoredProcedureSpec
    xmlns="bb_appfx_sqlstoredprocedure"
    xmlns:common="bb_appfx_commontypes" 
    ID="20b3411b-3bbf-4f64-80ad-17687e0c790a"
    Name="USR_USP_GETDUPLICATECONSTITUENTS"
    Description="USR_USP_GETDUPLICATECONSTITUENTS"
    Author="Blackbaud Professional Services"
    SPName="USR_USP_GETDUPLICATECONSTITUENTS"
    >

    <CreateProcedureSQL>
        <![CDATA[
            create procedure dbo.USR_USP_GETDUPLICATECONSTITUENTS
            (
                @ID uniqueidentifier,
                @KEYNAME nvarchar(100),
                @FIRSTNAME nvarchar(100)
            )
            with execute as owner
            as
                external name [Blackbaud.CustomFx.Sample.SqlClr].[Blackbaud.CustomFx.Sample.SqlClr.DuplicateRecordChecker].[GetMatchedConstituents]
        ]]>
    </CreateProcedureSQL>
</SQLStoredProcedureSpec>

Let’s test this so far. Go to SSMS and do what is below.

image_thumb_2_1

Once you have verified that your stored procedure is now successfully calling your assembly we move on and finish this up.

All that is left to do is to create the SP SearchList. The code is below and is pretty straight forward.

<SearchListSpec
    xmlns="bb_appfx_searchlist"
    xmlns:common="bb_appfx_commontypes"
    ID="ebfe0ae0-0666-4a5f-8328-3b760fdadac0"
    Name="Duplicate Constituent Search"
    Description="Searches for constituents for duplicate checking."
    Author="Blackbaud Professional Services"
    RecordType="Constituent"
    TranslationFunctionID="E34647DE-BB87-43CA-A178-E0D66132E268"
    >

    <SPSearchList SPName="USR_USP_SEARCHLIST_GETDUPLICATES">
        <common:CreateProcedureSQL>
            <![CDATA[
                create procedure dbo.USR_USP_SEARCHLIST_GETDUPLICATES
                (
                    @ID uniqueidentifier = null,
                    @KEYNAME nvarchar(100) = null,
                    @FIRSTNAME nvarchar(50) = null,
                    @MAXROWS smallint = 500
                )
                as
                set nocount on;

                begin
                    declare @t table
                    (
                        ID uniqueidentifier, 
                        KEYNAME nvarchar(100),
                        FIRSTNAME nvarchar(100)
                    )

                    insert into
                        @t
                        exec dbo.USR_USP_GETDUPLICATECONSTITUENTS
                            @ID, 
                            @KEYNAME, 
                            @FIRSTNAME

                    select 
                        ID, 
                        FIRSTNAME,
                        KEYNAME
                    from 
                        @t
                end
            ]]>
        </common:CreateProcedureSQL>
    </SPSearchList>
    <FormMetaData xmlns="bb_appfx_commontypes">
        <FormFields>
            <FormField FieldID="ID" Caption="Constituent" DataType="Guid" />
            <FormField FieldID="FIRSTNAME" Caption="First name" DataType="String" />
            <FormField FieldID="KEYNAME" Caption="Last name" DataType="String" />
        </FormFields>
    </FormMetaData>

    <Output>
        <OutputFields>
            <OutputField FieldID="ID" Caption="Constituent ID" DataType="Guid" IsHidden="true" />
            <OutputField FieldID="FIRSTNAME" Caption="First name" DataType="String" />
            <OutputField FieldID="KEYNAME" Caption="Last name" DataType="String" />
        </OutputFields>
    </Output>
</SearchListSpec>

After completing and load the searchlist, give it a try in BBEC. If all is successful you should have something similar to:

image_thumb_3_1

All that’s left to implement this into your batch is to put the correct elements in the batch spec, that is extremely simple and is a little beyond the scope of this article.

However, feel free to contact me with any questions/comments/concerns.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s