Using C# code in Data Connector data source pre-processing
Article ID: 181197
Updated On:
Products
Issue/Introduction
A Data Connector data source can pre-process data using C# code before it goes to the import rule. (This cannot use SQL or other programming languages, only C#.) This is accomplished using the "Pre-process import data" feature in the data source. This article provides some C# code examples, which are provided "AS IS" for the customer to learn from or adapt to their own purposes. Please Note: While Broadcom Technical Support can help assist the customer in understanding how to use Data Connector and provide troubleshooting, Support cannot create or troubleshoot C# code for the customer or provide education on how to do this. This service is provided by Symantec Consulting Services at https://www.broadcom.com/company/partners/symantec .
Environment
ITMS 8.x
CMDB 8.x
Resolution
Default Example: How pre-processing works.
When pre-processing is first edited for any data source, it automatically provides the following default example of how to use C#:
/*
This sample demonstrates how to iterate through the data from
the foreign data source and perform some standard operations.
*/
// Check that the required columns exist.
if (!importData.Columns.Contains("Resource Name"))
throw new Exception ("This function expects column 'Resource Name'.");
// Iterate through all rows
for (int i = 0; i < importData.Rows.Count; i++)
{
DataRow row = importData.Rows[i];
if (row["Resource Name"] != System.DBNull.Value)
{
string resourceName = (string)row["Resource Name"];
row["Resource Name"] = resourceName.ToUpper();
}
}
return importData;
What this example does, is when it finds a valid value (not null) in the specified column name, the value will be converted to all upper case characters. Note: This example will not work without first modifying "Resource Name" and changing it to the name of the column to be evaluated.
Modified Default Example: How to change one value to another.
The following is the default example but modified to do something more useful:
/*
This sample demonstrates how to iterate through the data from
the foreign data source and perform some standard operations.
*/
// Check that the required columns exist.
if (!importData.Columns.Contains("Purchase Order"))
throw new Exception ("This function expects column 'Purchase Order'.");
// Iterate through all rows
for (int i = 0; i < importData.Rows.Count; i++)
{
DataRow row = importData.Rows[i];
if ((string)row["Purchase Order"] == System.DBNull.Value)
{
string resourceName = (string)row["POrder00000001"];
row["Purchase Order"] = "POrder00000001";
}
}
return importData;
What this modified example does, is that when it finds a null value for the Purchase Order column, it will replace this with a specific "dummy" Purchase Order number, which must already exist. For this specific example, this can be used to automatically assign a valid Purchase Order number to computer assets being imported, if they do not have one already, if a Purchase Order number is required regardless.
The value being substituted can be changed to whatever the user desires the specified column to be. With this modified example, this is looking for null values. This can be changed back to not null (the default example) or to equal a specific value to be evaluated against. For example:
/*
This sample demonstrates how to iterate through the data from
the foreign data source and perform some standard operations.
*/
// Check that the required columns exist.
if (!importData.Columns.Contains("Purchase Order"))
throw new Exception ("This function expects column 'Purchase Order'.");
// Iterate through all rows
for (int i = 0; i < importData.Rows.Count; i++)
{
DataRow row = importData.Rows[i];
if ((string)row["Purchase Order"] == "POrder00000005")
{
string resourceName = (string)row["POrder00000001"];
row["Purchase Order"] = "POrder00000001";
}
}
return importData;
This will change any Purchase Order value that equals "POrder00000005" to be "POrder00000001".
Using the DateTime function.
What the following further example does, is that when it finds a null value for the Date column, it will replace this with the current date time derived from the DateTime.Today() C# function call.
/*
This sample demonstrates how to iterate through the data from
the foreign data source and perform some standard operations.
*/
// Check that the required columns exist.
if (!importData.Columns.Contains("Date"))
throw new Exception ("This function expects column 'Date'.");
// Iterate through all rows
for (int i = 0; i < importData.Rows.Count; i++)
{
DataRow row = importData.Rows[i];
string dateToday = Convert.ToString(DateTime.Today);
if (row["Date"] == DBNull.Value)
{
string resourceName = "";
row["Date"] = Convert.ToString(DateTime.Today);
}
}
return importData;
Evaluating a String for its Length
string FmtValue;
// If the column name of "SSN Last Four" does not exist in the data, throw an error:
if (!importData.Columns.Contains("SSN Last Four"))
throw new Exception ("This function expects column 'SSN Last Four'.");
// If the column name of "SSN Last Four" does exist in the data, pre-process its data by iterating through all of its rows:
for (int i = 0; i < importData.Rows.Count; i++)
{
DataRow row = importData.Rows[i];
FmtValue = (string)row["SSN Last Four"];
if (FmtValue.Length == 3)
row["SSN Last Four"] = "0" + FmtValue;
if (FmtValue.Length == 2)
row["SSN Last Four"] = "00" + FmtValue;
if (FmtValue.Length == 1)
row["SSN Last Four"] = "000" + FmtValue;
}
// End
return importData;
Troubleshooting
- For basic troubleshooting, if invalid code or functions are used, the pre-processing page cannot be saved. This will result in an error at the bottom of the pre-processing window, "Failed to compile code due to the following errors:". Correct these before the C# code can be successfully saved.
- Not all C# functions can be used; pre-processing uses a sub-set of the entire C# language. If errors occur when using what is expected to otherwise work, this is probably an indication that the code of functions being used isn't supported by Data Connector.
Feedback
