The following C# 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 object GetQueryValue(IDbConnection objConnection, String strQuery)
{
// If a parameter is null, return a null value
if (objConnection == null || strQuery == "")
return System.DBNull.Value;
// Declare variables that might be used
IDbCommand cmd = null;
IDataReader reader = null;
object objValue = System.DBNull.Value;
// Use the Try block to 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.
lock (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 the information
if (reader.Read()){
objValue = reader.GetValue(0);
}
// If there are no exceptions so far, return the value
reader.Close();
reader = null;
cmd.Dispose();
return objValue;
}
catch (Exception ex)
{
// Output exceptions to the log
IGlobalScript.AddLog("Exception in GetQueryValue: " + ex.ToString(), 0);
}
finally
{
if (!(reader == null))
reader.Close();
if (!(cmd == null))
cmd.Dispose();
//Return System.DBNull.Value because an exception has been generated
//if this point is reached.
}
return System.DBNull.Value;
}Calling the Script from a Mapping Expression
If you are using the function in an existing replication, either stop the Replication Agent or disable the replication.
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.
Right-click on the target field that will be using the generated value and choose Map to Expression.
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.Type an expression similar to the following in the Expression Editor. Modify the SELECT statement to retrieve the information you need.
GlobalScript.GetQueryValue(DBRS.SourceConnection, "SELECT Capital FROM Capitals WHERE abbrv='" + [ABBRV] + "'")ClickOKto close the Expression Generator window.
Click OK to close the Mapping window.
Click OK to close the Replication Properties window.
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 DateTime DateConvert(int intDate)
{
int cyy;
int yyyy;
int mm;
int dd;
cyy = (int)(intDate / 10000);
mm = (int)((intDate - cyy * 10000) / 100);
dd = intDate % 100;
if (cyy < 100)
yyyy = 1900 + cyy;
else
yyyy = 2000 + (cyy % 100);
DateTime dc = new DateTime(yyyy, mm, dd);
return dc;
}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 object JulianToDate(object obj)
{
if (obj == null)
return null;
int intObj = Convert.ToInt32(obj);
int intYears;
intYears = intObj / 1000;
int intDays;
intDays = (intObj - intYears * 1000) - 1;
DateTime datDate = new DateTime();
datDate = Convert.ToDateTime("1/1/" + (1900 + intYears)).AddDays(intDays);
return datDate;
}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 object CombineDateTime(String objDate, System.DateTime objTime)
{
// If the date object is nothing return a null value
if (objDate == null)
return System.DBNull.Value;
// To return a time of midnight for null times, remove this line
if (objTime == null)
return System.DBNull.Value;
// If not a legal date, return a null value
if (IsDate(objDate))
return System.DBNull.Value;
int intHours;
intHours = objTime.Hour;
int intMinutes;
intMinutes = objTime.Minute;
int intSeconds;
intSeconds = objTime.Second;
DateTime datOutput;
datOutput = DateTime.Now.AddDays(intHours);
datOutput = DateTime.Now.AddDays(intMinutes);
datOutput = DateTime.Now.AddDays(intSeconds);
return datOutput;
}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 object SafeTimeValue(System.DateTime objTime)
{
int intHours = 0;
int intMinutes = 0;
int intSeconds = 0;
// Return a time of midnight for null or illegal times
if (objTime != null)
{
if (IsDate(objTime.ToString()))
{
intHours = objTime.Hour;
intMinutes = objTime.Minute;
intSeconds = objTime.Second;
}
}
System.DateTime datOutput = default(System.DateTime);
datOutput = DateTime.Now.AddDays(intHours);
datOutput = DateTime.Now.AddDays(intMinutes);
datOutput = DateTime.Now.AddDays(intSeconds);
return datOutput;
}