Creating a Merge Replication Custom Conflict Resolver Using VB.NET

(Article by Doug Arterburn)

Introduction

Microsoft SQL Server 2000 includes a DLL called the Microsoft SQL Replication Conflict Resolver Library, replrec.dll.  This library of methods enables your application to respond as changes are applied during synchronization. A very nice article written by Andrea Fox from Microsoft titled Creating Merge Replication Custom Conflict Resolvers Using Visual Basic, documents these methods as well as the Merge Replication Custom Conflict Resolver VB6 sample, SampleResolver, included on the SQL Server 2000 Resource Kit.  Andrea's MSDN article can be found here.

This current article attempts to go one step further and provide a complete code example written in VB.NET that readers can adapt to their own business needs.  I will be covering just the steps required to create a VB.NET custom conflict resolver DLL.  I will not be covering the steps necessary to set up merge replication nor how to register the custom DLL to SQL Server. The latter is partially covered in Andrea's article and you may also find it useful to read up on the .NET utilities, tlbimp, ildasm, and ilasm; and the .NET Marshal class.

The Challenge

The .NET type library importer incorrectly defines the buffer parameter of methods GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to be the address of an Object.  For COM interfaces, which is what replrec.dll appears to be, that implies a COM-VARIANT parameter passing mechanism, however this is not what replrec.dll passes back.  Replrec.dll is simply passing back the address of a buffer containing the column value.  Furthermore, I was unable to find any documentation on the format of the data being passed back.  The simple data types are straight forward, but more complex data types required some detective work.  DATETIME, for example, is being passed back as an 8 element array of 16 bit integers: year, month, day, hour, minute, second, ?, ? (I'm still not sure what the last two integers represent - tenths of seconds?, hundredths of seconds?, timezone?). Herein lies the challenge.

The Solution - Summary

The solution is to create a type library for .NET that will force a parameter passing protocol that we can live with for those routines which pass column values via a buffer.  I chose the simplest parameter passing mechanism, 32-bit integer by value, so I can be assured that I know how .NET will pass the parameter.  (I could have done something much more interesting, perhaps created a custom COM wrapper, but I always say the most effective programmers are the laziest :))  I suspect that some of you are already thinking to yourself, "He must mean by reference, not by value!"  but I will be using the .NET Marshal class to allocate a buffer.  The Marshal class will give me the address of a buffer which I will store in a 32-bit integer variable (side note for VB6 programmers:  .NET integer type is 32 bits, long type is 64 bits).  So, if I pass this integer variable that contains an address by value, I am, in effect, passing the buffer by reference.  Makes me dizzy just thinking about it!  The final step is to create procedures to parse each SQL data type that I am interested in.  For this example, I have created a procedure to parse string values and a procedure to parse date values (see Appendix A for complete source listing).

The Solution - Nitty-Gritty Details

• Open a Visual Studio .NET 2003 Command Prompt
•  tlbimp "c:\Program Files\Microsoft SQL Server\80\COM\replrec.dll" /OUT:SQLResolver_import.dll
• ildasm "SQLResolver_import.dll" /OUT=SQLResolver.il
• Change line "[out] object&  marshal( struct) pvBuffer" for the methods GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to "[out] int32 pvBuffer"
• ilasm SQLResolver.il /OUT=SQLResolver.dll /dll
• Create a new .NET Windows Control Library project
• Remove the wizard generated control
• Use "Add New Item..." to add a new COM class
• Add reference to SQLResolver.dll created in step #5
   

About the Sample

This sample is relatively simple.  At Precision Development, Inc., we routinely maintain ModifyUser and ModifyDate columns to track who made the last change to the record and when the last change was made.  For every column conflict, the columns from the row with the latest ModifyDate wins.  The conflict is also logged with a message to indicate whose changes were lost, the ModifyUser of the losing row.

About the Author

Doug Arterburn has been programming for 29 years and married for 29 years; and has yet to determine whether these two milestones are in any way related.  His most challenging project is nearing completion after 26 years, that of advancing a daughter and son from birth to college graduation.  He still writes programs furiously under the guise of Senior Consultant at Precision Development, Inc., www.precisdev.com,  a company specializing in creating technology systems customized to meet the needs of small and medium-sized businesses across the central United States.
 

Appendix A - The Source Code

Imports System.Text
Imports SQLResolver
Imports System.Runtime.InteropServices
Imports System.Runtime.InteropServices.MarshalAsAttribute
Imports SQLResolver.REPOLE_CHANGE_TYPE
Imports SQLResolver.REPOLE_COLSTATUS_TYPE

<ComClass(ComClass1.ClassId, ComClass1.InterfaceId, ComClass1.EventsId)> _
Public Class ComClass1
Implements SQLResolver.IVBCustomResolver
Private Const MAX_BUFFER_SIZE As Integer = 1048576
Private Const MAX_NAME_LENGTH As Integer = 128

#Region "COM GUIDs"
' These GUIDs provide the COM identity for this class
' and its COM interfaces. If you change them, existing
' clients will no longer be able to access the class.
Public Const ClassId As String = "825818F7-3531-4524-8B07-72343EFDC8AB"
Public Const InterfaceId As String = "CECFBB8F-584F-4733-9373-B69AFA6F117F"
Public Const EventsId As String = "5D55BA40-A438-4FD4-BA8B-05095DC89948"
#End Region

' A creatable COM class must have a Public Sub New()
' with no parameters, otherwise, the class will not be
' registered in the COM registry and cannot be created
' via CreateObject.
Public Sub New()
   MyBase.New()
End Sub


Public Sub GetHandledStates(ByRef ResolverBm As Integer) Implements IVBCustomResolver.GetHandledStates
   ResolverBm = REPOLEUpdateConflicts
End Sub

Public Sub Reconcile(ByVal pRowChange As IReplRowChange, ByVal dwFlags As Integer, ByVal pvReserved As IReplRowChange) Implements IVBCustomResolver.Reconcile

Dim cntColumns As Integer
Dim intColumn As Integer
Dim strColumnName As String
Dim strLogMessage As String

Dim WinningData As Object
Dim blnSourceIsWinner As Boolean
Dim ColStatus As SQLResolver.REPOLE_COLSTATUS_TYPE
Dim intBufferLenActual As Integer
Dim intBufferLen As Integer
Dim strDestinationDateTime As String
Dim strSourceDateTime As String
Dim strDestinationUser As String
Dim strSourceUser As String
Dim myBuffer As IntPtr = Marshal.AllocHGlobal(MAX_BUFFER_SIZE)
Dim strMsg As String

Call pRowChange.GetNumColumns(cntColumns)