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).
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.
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.
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:
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.