Creating a custom resolver with VB.NET

Someone posted the question "Can anyone point me towards a source code
listing for a working replication custom resolver written in .Net?" Here is
an example that I have written.
Custom resolvers are created by adding a reference to the Microsoft SQL
Replication Conflict Resolver Library, replrec.dll. Unfortunately, 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 is
supposed to be, that implies a COM-VARIANT parameter passing mechanism, but
this is not what Replrec passes back. Replrec is simply passing back the
address of a buffer containing the column value which you have to decode.
So the solution involves correcting the method definitions so that a buffer
address can be passed and using the .NET Marshal routines to create the
buffer and move bytes out of the buffer.
Here are the steps involved, followed by a code example that resolves
conflicts by using the column values from the row with the latest date in
user defined column ModifyDate.
1. Open a Visual Studio .NET 2003 Command Prompt
2. tlbimp "c:\Program Files\Microsoft SQL Server\80\COM\replrec.dll"
/OUT:SQLResolver_import.dll
3. ildasm "SQLResolver_import.dll" /OUT=SQLResolver.il
4. Change line "[out] object& marshal( struct) pvBuffer" for the methods
GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to "[out]
int32 pvBuffer"
5. ilasm SQLResolver.il /OUT=SQLResolver.dll /dll
6. Create a new .NET Windows Control Library project
7. Remove the wizard generated control
8. Use "Add New Item..." to add a new COM class
9. Add reference to SQLResolver.dll created in step #5
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
'If Not Debugger.IsAttached Then
' Debugger.Launch()
'Else
' Debugger.Break()
'End If
Call pRowChange.GetNumColumns(cntColumns)
For intColumn = 1 To cntColumns
strColumnName = " ".PadRight(MAX_NAME_LENGTH)
' strColumnName.PadRight(OSQL_SYSNAME_SET, Chr(vbNull))
Call pRowChange.GetColumnName(intColumn, strColumnName, MAX_NAME_LENGTH)
' strColumnName.TrimEnd(Chr(vbNull))
strColumnName = strColumnName.TrimEnd()
If (String.Compare(strColumnName, "ModifyDate", True) = 0) Then
pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strDestinationDateTime = ConvertBufferToDateString(myBuffer)
pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strSourceDateTime = ConvertBufferToDateString(myBuffer)
If strSourceDateTime > strDestinationDateTime Then
blnSourceIsWinner = True
Else
blnSourceIsWinner = False
End If
End If
If (String.Compare(strColumnName, "ModifyUser", True) = 0) Then
pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strDestinationUser = ConvertBufferToString(myBuffer, intBufferLenActual)
pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strSourceUser = ConvertBufferToString(myBuffer, intBufferLenActual)
End If
Next intColumn
For intColumn = 1 To cntColumns
'Get the column status of each column
pRowChange.GetColumnStatus(intColumn, ColStatus)
' If the column has been updated at both the Publisher and Subscriber
If (ColStatus = REPOLEColumn_UpdatedWithConflict) Then
If blnSourceIsWinner Then
pRowChange.CopyColumnFromSource(intColumn)
End If
' For columns that have been updated without any changes, copy column
values from source
ElseIf (ColStatus = REPOLEColumn_UpdatedNoConflict) Then
pRowChange.CopyColumnFromSource(intColumn)
' For columns that have not been updated - do nothing.
ElseIf (ColStatus = REPOLEColumn_NotUpdated) Then
End If
Next intColumn
' Log conflict and call the UpdateRow method to commit all the column value
changes.
'
If strDestinationDateTime.Length > 0 And strDestinationUser.Length > 0 Then
If blnSourceIsWinner Then
strMsg = "Losing update(s) made by " & strDestinationUser
Else
strMsg = "Losing update(s) made by " & strSourceUser
End If
End If
pRowChange.LogConflict(REPOLE_BOOL.REPOLEBool_TRUE ,
REPOLE_CONFLICT_TYPE.REPOLEConflict_ColumnUpdateCo nflict,
REPOLE_BOOL.REPOLEBool_FALSE, strMsg, REPOLE_BOOL.REPOLEBool_FALSE)
Call pRowChange.UpdateRow()
Marshal.FreeHGlobal(myBuffer)
End Sub
Private Function ConvertBufferToDateString(ByVal p As IntPtr) As String
Dim s As String = String.Empty
Dim i(7) As Short
Dim j As Integer
Marshal.Copy(p, i, 0, i.Length)
s = i(0).ToString '4 digit Year
For j = 1 To i.GetUpperBound(0)
s &= i(j).ToString.PadLeft(2, "0"c)
Next
ConvertBufferToDateString = s
End Function
Private Function ConvertBufferToString(ByVal p As IntPtr, ByVal
intBufferLenActual As Integer) As String
Dim i As Integer
Dim s As String = String.Empty
For i = 0 To intBufferLenActual - 1
s &= Chr(Marshal.ReadByte(p, i))
Next
ConvertBufferToString = s
End Function
Private Sub AppendToLog(ByVal pRowChange As IReplRowChange, ByRef s As
String, ByVal inMsg As String)
s = s & ";" & inMsg
If Len(s) < 50 Then Call pRowChange.LogError(REPOLEAllChanges, s)
End Sub
End Class
u rock man!
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Douglas Arterburn" <darterburn@.precisdev.com> wrote in message
news:uzhwyd7iFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Someone posted the question "Can anyone point me towards a source code
> listing for a working replication custom resolver written in .Net?" Here
> is an example that I have written.
> Custom resolvers are created by adding a reference to the Microsoft SQL
> Replication Conflict Resolver Library, replrec.dll. Unfortunately, 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 is supposed to be, that implies a COM-VARIANT parameter
> passing mechanism, but this is not what Replrec passes back. Replrec is
> simply passing back the address of a buffer containing the column value
> which you have to decode.
> So the solution involves correcting the method definitions so that a
> buffer address can be passed and using the .NET Marshal routines to create
> the buffer and move bytes out of the buffer.
> Here are the steps involved, followed by a code example that resolves
> conflicts by using the column values from the row with the latest date in
> user defined column ModifyDate.
> 1. Open a Visual Studio .NET 2003 Command Prompt
> 2. tlbimp "c:\Program Files\Microsoft SQL Server\80\COM\replrec.dll"
> /OUT:SQLResolver_import.dll
> 3. ildasm "SQLResolver_import.dll" /OUT=SQLResolver.il
> 4. Change line "[out] object& marshal( struct) pvBuffer" for the methods
> GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to "[out]
> int32 pvBuffer"
> 5. ilasm SQLResolver.il /OUT=SQLResolver.dll /dll
> 6. Create a new .NET Windows Control Library project
> 7. Remove the wizard generated control
> 8. Use "Add New Item..." to add a new COM class
> 9. Add reference to SQLResolver.dll created in step #5
> 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
> 'If Not Debugger.IsAttached Then
> ' Debugger.Launch()
> 'Else
> ' Debugger.Break()
> 'End If
> Call pRowChange.GetNumColumns(cntColumns)
> For intColumn = 1 To cntColumns
> strColumnName = " ".PadRight(MAX_NAME_LENGTH)
> ' strColumnName.PadRight(OSQL_SYSNAME_SET, Chr(vbNull))
> Call pRowChange.GetColumnName(intColumn, strColumnName,
> MAX_NAME_LENGTH)
> ' strColumnName.TrimEnd(Chr(vbNull))
> strColumnName = strColumnName.TrimEnd()
> If (String.Compare(strColumnName, "ModifyDate", True) = 0) Then
> pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strDestinationDateTime = ConvertBufferToDateString(myBuffer)
> pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strSourceDateTime = ConvertBufferToDateString(myBuffer)
> If strSourceDateTime > strDestinationDateTime Then
> blnSourceIsWinner = True
> Else
> blnSourceIsWinner = False
> End If
> End If
> If (String.Compare(strColumnName, "ModifyUser", True) = 0) Then
> pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strDestinationUser = ConvertBufferToString(myBuffer, intBufferLenActual)
> pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strSourceUser = ConvertBufferToString(myBuffer, intBufferLenActual)
> End If
> Next intColumn
> For intColumn = 1 To cntColumns
> 'Get the column status of each column
> pRowChange.GetColumnStatus(intColumn, ColStatus)
> ' If the column has been updated at both the Publisher and Subscriber
> If (ColStatus = REPOLEColumn_UpdatedWithConflict) Then
> If blnSourceIsWinner Then
> pRowChange.CopyColumnFromSource(intColumn)
> End If
> ' For columns that have been updated without any changes, copy column
> values from source
> ElseIf (ColStatus = REPOLEColumn_UpdatedNoConflict) Then
> pRowChange.CopyColumnFromSource(intColumn)
> ' For columns that have not been updated - do nothing.
> ElseIf (ColStatus = REPOLEColumn_NotUpdated) Then
> End If
> Next intColumn
> ' Log conflict and call the UpdateRow method to commit all the column
> value changes.
> '
> If strDestinationDateTime.Length > 0 And strDestinationUser.Length > 0
> Then
> If blnSourceIsWinner Then
> strMsg = "Losing update(s) made by " & strDestinationUser
> Else
> strMsg = "Losing update(s) made by " & strSourceUser
> End If
> End If
> pRowChange.LogConflict(REPOLE_BOOL.REPOLEBool_TRUE ,
> REPOLE_CONFLICT_TYPE.REPOLEConflict_ColumnUpdateCo nflict,
> REPOLE_BOOL.REPOLEBool_FALSE, strMsg, REPOLE_BOOL.REPOLEBool_FALSE)
> Call pRowChange.UpdateRow()
> Marshal.FreeHGlobal(myBuffer)
>
> End Sub
> Private Function ConvertBufferToDateString(ByVal p As IntPtr) As String
> Dim s As String = String.Empty
> Dim i(7) As Short
> Dim j As Integer
> Marshal.Copy(p, i, 0, i.Length)
> s = i(0).ToString '4 digit Year
> For j = 1 To i.GetUpperBound(0)
> s &= i(j).ToString.PadLeft(2, "0"c)
> Next
> ConvertBufferToDateString = s
> End Function
> Private Function ConvertBufferToString(ByVal p As IntPtr, ByVal
> intBufferLenActual As Integer) As String
> Dim i As Integer
> Dim s As String = String.Empty
> For i = 0 To intBufferLenActual - 1
> s &= Chr(Marshal.ReadByte(p, i))
> Next
> ConvertBufferToString = s
> End Function
> Private Sub AppendToLog(ByVal pRowChange As IReplRowChange, ByRef s As
> String, ByVal inMsg As String)
> s = s & ";" & inMsg
> If Len(s) < 50 Then Call pRowChange.LogError(REPOLEAllChanges, s)
> End Sub
> End Class
>