VB.NET Global Script Function Examples

The following VB.NET example functions can be defined in a global script, then used in replication scripts and expressions to modify target field values during replication. For detailed steps on how to define a Global Script function, check Writing a Function to Use in Scripts and Expressions.

Accessing Values Using a Different Database Connection

If your replication needs record values which are accessible only by opening a separate database connection and running a query there, you can write the following script in the Global Script Editor, then call the script as indicated below.

The GetQueryValue function retrieves the first returned value from the passed in connection and query. If the parameters are illegal, the query returns an error or the query does not return a value, the function returns System.DBNull.Value.

Public Shared Function GetQueryValue(objConnection as Object, strQuery as String) as Object   
 'If a parameter is null, return a null value
  If objConnection Is Nothing OR strQuery="" Then Return System.DBNull.Value
  'Declare variables that might be used
   Dim cmd As IDbCommand = Nothing
   Dim reader As IDataReader = Nothing
   Dim objValue as Object = System.DBNull.Value
   'Use the Try block to make sure we capture any exceptions that might be generated
    Try
       'Use a SyncLock to make sure this function is the only user of the connection since the
       'source or target connection might be in use.
	SyncLock(objConnection)
	  'Create and execute the select to get the specified value
	  cmd = objConnection.CreateCommand
	  cmd.CommandText = strQuery            
	   reader = cmd.ExecuteReader             
	   'If we can read from the reader, the record is already there. Get info.
	  If reader.Read Then objValue = reader.GetValue(0)
	End SyncLock
	'If there are no exceptions so far, return the value
	 reader.Close
	reader = Nothing
	cmd.Dispose
        Return objValue
	 'Output exceptions to the log
	Catch ex As Exception
	   AddLog("Exception in GetQueryValue: " & ex.ToString,0)
	Finally
	   if (not reader is Nothing) Then
		reader.Close
	   End If
	    if (not cmd is Nothing) Then
		cmd.Dispose
	   End If
       End Try
	'Return System.DBNull.Value because an exception has been generated if this point is reached.
       Return System.DBNull.Value
End Function 'GetQueryValue

Calling the Script from a Mapping Expression

  1. If you are using the function in an existing replication, either stop the Replication Agent or disable the replication.

  2. For an existing replication, right-click on the replication and choose Replication Properties, then click Mapping to display the Mapping Editor.  For new replications, perform the following steps when you get to the mapping portion of replication creation.

  3. Right-click on the target field that will be using the generated value and choose Map to Expression.

  4. In the Expression Generator window, expand the User Functions folder and click on (all) to see if your function is listed.
    If the function is not displayed, check to make sure that you have used the proper Public Shared qualifier for the function. See Writing a Function to Use in Scripts and Expressions for more information.

  5. Type an expression similar to the following in the Expression Editor, modifying the SELECT statement to retrieve the information you need.

  GetQueryValue(DBRS.SourceConnection, "SELECT Capital FROM Capitals WHERE abbrv='" & [ABBRV] & "'")

  1. Click OK to close the Expression Generator window.

  2. Click OK to close the Mapping window.

  3. Click OK to close the Replication Properties window.

  4. Either start the Replication Agent or enable the replication to test that your function is working.

Date Conversion

Define this function in the Global Script Editor, then call it by mapping the source field to an expression on the target field. The expression would be DateConvert([DT]), where DT is the name of the source field. The DT field should have the Use Unmapped option checked.

  Public Shared Function DateConvert(intDate)
    Dim cyy
    Dim yyyy
    Dim mm
    Dim dd
    cyy = CInt(intDate/10000)
    mm = CInt((intDate - cyy * 10000) / 100)
    dd = intDate Mod 100
    if (cyy < 100)
      yyyy = 1900 + cyy
    else
      yyyy = 2000 + (cyy Mod 100)
    End if
    DateConvert = DateSerial(yyyy, mm, dd)
  End Function

Julian Date Conversion

Define this function in the Global Script Editor, then call it by mapping the source field to an expression on the target field. The expression would be JulianToDate(obj), where obj is the name of the source field. The source field should have the Use Unmapped option checked. This function takes a numeric Julian date in the format YYYDDD where YYY is the number of years since 1900 and DDD is the day of the year and returns a VB.Net date value or Null if the original value was null.

Public Shared Function JulianToDate(obj as Object) as Object
    If obj Is Nothing Then Return Nothing
    If Not IsNumeric(obj) Then Return Nothing  
    Dim intYears as Integer
    intYears = Int(obj/1000)       
    Dim intDays as Integer
    intDays = (obj - intYears * 1000) - 1    
    Dim datDate as Date
    datDate = CDate("1/1/" & Str(1900 + intYears)).AddDays(intDays)
    Return datDate   
End Function

Combine Date and Time Fields

Define this function in the Global Script Editor, then call it by mapping the source field to an expression on the target field. The expression would be CombineDateTime(objDate, objTime), where objDate is the name of the date source field and objTime is the name of the time source field. The source fields should have the Use Unmapped option checked.

 Public Shared Function CombineDateTime(objDate as Object, objTime as Object) as Object
    'If the date object is nothing return a null value
    If objDate is Nothing Then Return System.DBNull.Value    
    'If you want to return a time of midnight for null times, remove this line
    If objTime is Nothing Then Return System.DBNull.Value     
    'If we don't have a legal date return a null value
    If Not IsDate(objDate) Then Return System.DBNull.Value
     Dim intHours as Integer
     intHours = Hour(objTime)
     Dim intMinutes as Integer
     intMinutes = Minute(objTime)
     Dim intSeconds as Integer
     intSeconds = Second(objTime)
     Dim datOutput as Date
     datOutput = DateAdd("h", intHours, objDate)
     datOutput = DateAdd("n", intMinutes, datOutput)
     datOutput = DateAdd("s", intSeconds, datOutput)
     Return datOutput
   End Function

Time Function that Handles Null Values

Define this function in the Global Script Editor, then call it by mapping the source field to an expression on the target field. The expression would be SafeTimeValue(objTime), where objTime is the name of the time source field. The source field should have the Use Unmapped option checked. This function can be used to replace the built in TimeValue function because it checks for null values.  When a record is deleted on SQL Server, only the primary key is replicated so when an attempt is made to modify the  time value, the TimeValue routine fails because it has been passed a null value.

Public Shared Function SafeTimeValue(objTime as Object) as Object
  Dim intHours as Integer
  Dim intMinutes as Integer
  Dim intSeconds as Integer 
  'Return a time of midnight for null or illegal times
  intHours = 0
  intMinutes = 0
  intSeconds = 0
  If Not objTime is Nothing Then  
      If IsDate(objTime) Then
         intHours = Hour(objTime)
         intMinutes = Minute(objTime)
         intSeconds = Second(objTime)
      End If
  End If
  Dim datOutput as Date
  datOutput = DateAdd("h", intHours, datOutput)
  datOutput = DateAdd("n", intMinutes, datOutput)
  datOutput = DateAdd("s", intSeconds, datOutput)
  Return datOutput
End Function