Writing a Field Mapping Function

This type of function, a mapping rule, can be used to identify which source and target columns to map when defining a replication. The default approach is to map source table columns to target table columns with matching names. If, for example, source table column names and target table column names use a different prefix, you could write a function to compare names after skipping the prefix as in the VB.NET and C# example below.

VB.NET Example

Public Class MappingRule : Inherits IMappingRule
	<MappingRuleAttribute("Match By Prefix", "Match names considering the prefix 'fld_'")>
	Public Function CustomMapping_ByNamePrefixed (
			ByVal sSourceName As String,
			ByVal iSourceOrdinal As Integer,
			ByVal sTargetName As String,
			ByVal iTargetOrdinal As Integer) As Boolean
           If sTargetName.Length >= 4 Then
		Return (String.Compare(sSourceName, sTargetName.SubString(4), True) = 0)
	    Else
		Return False
	    End If
	End Function
      
	<MappingRuleAttribute("Match By Suffix", "Match names considering the suffix '_fld'")>
	Public Function CustomMapping_ByNameSuffixed (
			ByVal sSourceName As String,
			ByVal iSourceOrdinal As Integer,
			ByVal sTargetName As String,
			ByVal iTargetOrdinal As Integer) As Boolean
	   If sSourceName.Length >= 4
		Return (String.Compare(sSourceName.SubString(4), sTargetName, True) = 0)
	   Else
		Return False
	   End If
	End Function    
 
	<MappingRuleAttribute("Match Custom", "Match names considering a specific custom rule")>
	Public Function MyCustomMapping (
			ByVal bIsForth As Boolean,
			ByVal sSourceName As String,
			ByVal iSourceOrdinal As Integer,
			ByVal sSourceType As String,
			ByVal sTargetName As String,
			ByVal iTargetOrdinal As Integer,
			ByVal sTargetType As String,
			ByRef sExpression As System.Text.StringBuilder) As Boolean
			///     ...
	End Function
	<MappingRuleAttribute("Match Custom Extended Params", "Match names considering a specific custom rule")>
	Public Function MyCustomMapping (
			ByVal bIsForth As Boolean,
			ByVal sSourceName As String,
			ByVal iSourceOrdinal As Integer,
			ByVal sSourceType As String,
			ByVal bIsSourcePrimaryKey As Boolean,
			ByVal bIsSourceNullable As Boolean,   
			ByVal iSourceSize As Integer,
			ByVal sSourcePrecision As Short,
			ByVal sSourceScale As Short,
			ByVal sTargetName As String,
			ByVal iTargetOrdinal As Integer,
			ByVal sTargetType As String,
			ByVal bIsTargetPrimaryKey As Boolean,
			ByVal bIsTargetNullable As Boolean,
			ByVal iTargetSize As Integer,
			ByVal sTargetPrecision As Short,
			ByVal sTargetScale As Short,
			ByRef sExpression As System.Text.StringBuilder) As Boolean
			///     ...
	End Function
End Class

C# Example

using System;
using System.Data;
using DBMotoPublic;
using DBMotoScript;
namespace DBRS
{
   public class GlobalScript : IGlobalScript
   {
   }
   public class MappingRule : IMappingRule
   {
     [MappingRuleAttribute("Match By Prefix", "Match names considering the prefix 'fld_'")]
     public bool CustomMapping_ByNamePrefixed (String sSourceName, int iSourceOrdinal, String sTargetName, int iTargetOrdinal)
	{
	   if (sTargetName.Length >= 4)
	      {
		return (String.Compare(sSourceName, sTargetName.Substring(4), true) == 0);
	       }
	   else
	       {
		return false;
	        }
	}
    
	[MappingRuleAttribute("Match By Prefix", "Match names considering the prefix 'fld_'")]
	public bool CustomMapping_ByNameSuffixed (String sSourceName, int iSourceOrdinal, String sTargetName, int iTargetOrdinal)
	{
	   if (sSourceName.Length >= 4)
		{
		   return (String.Compare(sSourceName.Substring(4), sTargetName, true) == 0);
		}
	   else
		{
		   return false;
		}
	  }
	[MappingRuleAttribute("Match Custom", "Match names considering a specific custom rule")]
	public bool MyCustomMapping(bool bIsForth, String sSourceName, int iSourceOrdinal, String sSourceType, 
          String sTargetName, int iTargetOrdinal, String sTargetType, ref System.Text.StringBuilder sExpression)
	{
	  //      ...
	  return true;
	}
   }
   public class GlobalEvents : IGlobalEvents
   {
   }
}

Parameters

The possible parameters for the functions are:

bIsForth - TRUE or FALSE depending on whether mapping is forward or backward direction (for synchronization). This allows you to write a script that handles both directions of mapping. For instance, if bIsForth is True, then apply mapping by name; if bIsForth is False then apply a different rule.

sSourceName - The source>iSourceOrdinal - The source column ordinal position (expressed as an integer)

sSourceType - The source column type (original database type). This can be used to apply custom mapping rules that work on specific datatypes. For instance, you could write a rule that applies a Trim function to the mapping for all VARCHAR columns.

bIsSourcePrimaryKey - Specifies whether the source field is a primary key

bIsSourceNullable - Specifies whether the source field is nullable.

iSourceSize - Source field size expressed as an integer

sSourcePrecision - Source field precision expressed as a short

sSourceScale - Source field scale expressed as a short

sTargetName - The target column name

iTargetOrdinal - The target column ordinal position (expressed as an integer)

sTargetType - The target column type (original database type).

blsTargetPrimaryKey - Specifies whether the target field is a primary key

bIsTargetNullable - Specifies whether the target field is nullable. Allows you to check if the target field is not nullable, and thereby avoid cases where NULL values are passed to the target.

iTargetSize - Target field size expressed as an integer

sTargetPrecision - Target field precision expressed as a short

sTargetScale - Target field scale expressed as a short

sExpression - A reference parameter that can be returned from the function back to Syniti DR. This allows you to apply specific expressions to the mapping. For example:

If sTargetType = "VARCHAR" Then
                sExpression.Append("MyGlobalFunction([" + sSourceName + "])")
             End If

The example above defines an expression that will call the global function MyGlobalFunction for all target columns of type VARCHAR.

Implementing the Mapping Rule

Select the metadata for which you want to create a global script.
From the right mouse button menu, select Global Script.
In the Global Script Editor, insert your mapping rule or rules below the line:
VB.NET: Public Class MappingRule : Inherits IMappingRule
C#: public class MappingRule : IMappingRule
Write a MappingRuleAttribute definition to determine what appears on the Automatic Mapping menu in the Set Mapping Info screen of the Replication Wizard or the Fields Mapping dialog. The first string contains the text that will appear as a submenu of the Custom Mapping menu item. The second string contains text that will serve as a tooltip description of the function.
Write your function using the examples above as a guideline. The parameters of your function must match those in the examples above.
These parameters can then be used in the function.
Compile the global script to see that the function compiles correctly. It will be available in the Automatic Mapping menu only if it compiles correctly.