C-Sharp Activity
Historically, VBScript-based activities were used extensively in the Workflow application to perform data fetch, update and insert operations. C# offers a better platform to perform such operations. Its compiled code reduces invalid code. C# can be modulated for better code management and has better trace capabilities to debug potential issues. The Workflow dll on the back end that supports the C# activity code has better error handling and reporting mechanisms.
The C# activity for database-related operations (fetch, update and insert) only supports the tables that are present in the BusinessPlus database schema. Views or non-BusinessPlus tables are not supported.
Delete operations are allowed by C# on a business rule basis; that functionality is not discussed on this page. This document illustrates the basic code logic that can be used for various data table based operations.
Create a C# Workflow Activity
In the Workflow Designer application, select "C#" from the Action drop-down menu in the Activity (documentation found at Workflow Designer/Definitions - WF/Activities) dialog. Next, select the Settings button to open the Execute C# Settings dialog box.
Settings
The Execute C# Settings dialog box consists of an Initialization section and a Source Code section.
Initialization
Assembly references, using statements, and member variables are listed in the display window. Use the Edit Initialization link to edit the list.
Source Code
New Method: Enter the return type (void, int, string, DataTable, decimal, etc.) of the function, the name of the function, and any parameters that are being passed to the "New Method" field. Press Add to add the method to the Method field drop-down.
Method: Lists available methods. Use the New Method field to add to this list.
Add: Add a new method to the C# code. The Method drop-down list is updated, and the new method is loaded into the C# Editor window.
Delete: Deletes the currently selected method.
C# Editor: Displays the currently selected method.
Validate: Validates the code displayed in the C# editor window.
Run Function
"Run" is the main function (method). Valid C# code can be added in this function. While entire coding logic can be added to the main function body, it is recommended to add more functions addressing specific needs to better manage the code.
The Validate button can be used to check if the code is valid or not. Pressing OK also validates the code and reports any errors. Code cannot be saved until the errors are addressed.
Data Fetch
Data from any BusinessPlus table, with a list of columns to fetch, for given filters (if any), can be fetched using the following data fetch logic:
string sCreator = Model.GetCreator(); //user id of the workflow model instance creator
string sActivityUser = Model.GetActivityUser(); //user id of the current (this) C# activity
//Declare the object with the table name
IFAS.Common.GenericBT20Object popPvDtl = new GenericBT20Object("pop_pv_dtl");
//List any number of columns to be fetched. At least one column name is required
popPvDtl.FetchColumns("Column1", "Column2", "Column3");
//Filters [Optional]
//One 'WhereCondition' call for each filter.
//Sign and Operator parameters are optional.
//Default sign is 'Equal', default operator is 'AND'
//Following two filter calls are exactly the same
popPvDtl.WhereCondition("ColumnA", "SomeValue");
popPvDtl.WhereCondition("ColumnA", "SomeValue", WhereSign.sign_equal, WhereOperator.eWhereAND);
//Few more filters. 'NotEqual', 'GreaterThan' and 'LessThanOrEqual'
popPvDtl.WhereCondition("ColumnB", "SomeValue", WhereSign.sign_ne);
popPvDtl.WhereCondition("ColumnC", "99", WhereSign.sign_gt);
popPvDtl.WhereCondition("ColumnD", "01/01/2015", WhereSign.sign_le);
//For complicated or mix of 'AND' & 'OR', plain text can be used for a valid ‘where’ condition
popPvDtl.WhereClause = "(ColumnE = 'A' or ColumnE = '' or ColumnE is null) AND ColumnF <= '12/31/2015'";
//Sorting [Optional]
//[ColumnName [Optional Direction, Default is Asc]]. List any number of columns
popPvDtl.OrderByColumns("Column1 desc", "Column2");
The code above will result in the following SQL Query:
select Column1, Column2, Column3 from pop_pv_dtl
where ColumnA='SomeValue' and ColumnB <> 'SomeValue' and ColumnC > 99 and ColumnD <= '01/01/2015'
and (ColumnE = 'A' or ColumnE = '' or ColumnE is null) AND ColumnF <= '12/31/2015'
order by Column1 desc, Column2
//Data (if fetched successfully), will be returned in DataTable object.
//Column names in this table will be the same as listed in the fetch list
//Data types will be string, DateTime and Decimal based upon the
//data types of the columns to be fetched
//In case of any error, an exception will be thrown
try
{
//Set the user id if need to apply the security for the user in question[optional]
popPvDtl.IfasUserId = sActivityUser;
DataTable table = DataHelper.GetTableData(popPvDtl);
if (table.Rows.Count > 0)
{
//data from the first row (0 index based) for the given column
string str = table.Rows[0]["Column1"].ToString();
}
//or
foreach (DataRow row in table.Rows)
{
string str = row["ColumnA"].ToString();
int n1 = Convert.ToInt32(row["ColumnB"]); //unsafe to use if value is not numeric
int n2;
if (!int.TryParse(row["ColumnA"].ToString(), out n2)) //Better
{
//"invalid numeric value for ColumnA"
}
else
{
//use n2
}
}
}
catch (Exception e)
{
Model.WriteTrace(e.Message); //This trace will be added to BT50WF.Managed trace module
return; //abort
}
Details about the processing, SQL query, and exceptions will be logged in the trace file for BT50WF.Managed, IFAS.Common modules.
All Signs and Operations
Various values for WhereSign are sign_equal, sign_lt, sign_gt, sign_le, sign_ge, sign_ne, sign_in, sign_like, sign_isnull, sign_isnotnull, sign_notlike. Sign_equal is the default value, if not provided.
Values for WhereOperator are eWhereAND, eWhereOR, eWhereAND is the default value, if not used.
Table Records Count Example
The following SQL query:
"select count(*) from Table_Name where ColumnA='SomeValue' and ColumnB <> 'SomeValue'"can be accomplished as:
IFAS.Common.GenericBT20Object popPvDtl = new GenericBT20Object("pop_pv_dtl");
popPvDtl.WhereCondition("ColumnA", "SomeValue");
popPvDtl.WhereCondition("ColumnB", "SomeValue", WhereSign.sign_ne);
//Set the user id if need to apply the security for the user in question[optional]
popPvDtl.IfasUserId = sActivityUser;
int nCount = DataHelper.GetRecordCount(popPvDtl);
Model.WriteTrace("Found " + nCount + " records");
Adding New Custom Functions
To add a new custom function, add the return type (void, int, string, DataTable, decimal, etc.) of the function, the name of the function, and any parameters that are being passed to the "New Method" field. Press Add.


Data Fetch using SQL Functions MAX, MIN, SUM
Sum, Max and Min functions are supported for the data fetch columns. Example:
IFAS.Common.GenericBT20Object popPvDtl = new GenericBT20Object("pop_pv_dtl");
popPvDtl.FetchColumns("sum(Column1)", "max(Column2)", "min(Column3)");
popPvDtl.WhereCondition("ColumnA", "SomeValue");//optional
try
{
//Set the user id if need to apply the security for the user in question[optional]
popPvDtl.IfasUserId = sActivityUser;
DataTable table = DataHelper.GetTableData(popPvDtl);
if (table.Rows.Count > 0)
{
decimal sum = BTHelper.To<decimal>(table.Rows[0]["Column1"].ToString());
string max = table.Rows[0]["Column2"].ToString();
}
}
catch (Exception e)
{
//e.Message
//error
}
Data Fetch using Group by, Having Count
An SQL query like:
"select Column1, Column2, Column3 from Table_Name where ColumnA='SomeValue' and ColumnB <> 'SomeValue'
Group by Column1, Column2, Column3 Having Count(*) > 1"
can be accomplished using the following code:
IFAS.Common.GenericBT20Object popPvDtl = new GenericBT20Object("pop_pv_dtl");
popPvDtl.FetchColumns("Column1", " Column2", " Column3");
popPvDtl.WhereCondition("ColumnA", "SomeValue");
popPvDtl.GroupBy("Column1", " Column2", " Column3");
//Set the user id if need to apply the security for the user in question[optional]
popPvDtl.IfasUserId = sActivityUser;
DataTable table = DataHelper.GetTableData(popPvDtl);
Model.WriteTrace(string.Format("Found {0} rows", table.Rows.Count));
foreach(DataRow row in table.Rows)
{
int nCount = BTHelper.To<int>(row["Count"].ToString());
if(nCount > 1)
{
Model.WriteTrace(row["Column1"].ToString());
//do something
}
}
When the GroupBy method is used, the process will automatically add column "Count" to the DataTable returned by GetTableData. The table will have all rows with Count 1 and higher. The user needs to manually check each row to verify the Count value of that row.
Other operations can be performed on the table object to find the desired data as:
DataRow[] rows = table.Select("Count > 1");
foreach (DataRow row in rows)
{
//do something here
}
Data Update
Any BusinessPlus table data can be updated using the code logic given below. All values must be in the string type. Quotes and apostrophes in the data values should be properly escaped for a valid query statement.
IFAS.Common.GenericBT20Object popPvDtl = new GenericBT20Object("pop_pv_dtl");
popPvDtl.SetColumn("Column1","value"); //at least one required
popPvDtl.SetColumn("Column2", "245.25");
popPvDtl.SetColumn("Column3", "07/01/2019");
popPvDtl.WhereCondition("ColumnA", "SomeValue"); //required
//Set the user id if need to apply the security for the user in question[optional]
popPvDtl.IfasUserId = sActivityUser;
//--- USE ONE OF THE FOLLOWING METHODS TO UPDATE DATA
//Use the following method call to update a record from the table without causing a table trigger.
int nUpdated = DataHelper.UpdateData(popPvDtl);
//If a table trigger is required when a record in the table is updated use the following.
int nUpdated = DataHelper.UpdateDataTriggerWorkflow(popPvDtl);
Model.WriteTrace("updated " + nUpdated + "records");
Data Insert
Data Insert in any BusinessPlus table is similar to Data Update. The user needs to make sure all the required columns are included. If there is a foreign key constraint, the insert may fail but may not provide a clear error message unless we review the full system trace.
IFAS.Common.GenericBT20Object popPvDtl = new IFAS.Common.GenericBT20Object("pop_pv_dtl");
popPvDtl.InsertColumn("Column1", "value1");
popPvDtl.InsertColumn("Column2", "95.50");
popPvDtl.InsertColumn("Column3", "01/01/2015");
popPvDtl.InsertColumn("Column4", "1");
try
{
//Set the user id if need to apply the security for the user in question[optional]
popPvDtl.IfasUserId = sActivityUser;
//--- USE ONE OF THE FOLLOWING METHODS TO INSERT DATA
//Use the following method call to insert a record from the table without causing a table trigger.
DataHelper.InsertData(popPvDtl);
//If a table trigger is required when a record in the table is inserted use the following.
DataHelper.InsertDataTriggerWorkflow(popPvDtl);
}
catch(Exception e)
{
Model.WriteTrace(e.Message);
}
Data Delete
Any BusinessPlus table data can be deleted using the code logic like given below. Second parameter value of ‘DELETE’ is required during the declaration of the BT20 object to make sure user is aware that invoke the ‘Delete’ call was intentional.
Example Code:
IFAS.Common.GenericBT20Object popPvDtl = new GenericBT20Object("pop_pv_dtl", "DELETE");
popPvDtl.WhereCondition("ColumnA", "SomeValue");//required
try
{
//Set the user id if need to apply the security for the user in question[optional]
popPvDtl.IfasUserId = sActivityUser;
//--- USE ONE OF THE FOLLOWING METHODS TO DELETE DATA
//Use the following method call to delete a record from the table without causing a table trigger.
DataHelper.DeleteData(popPvDtl);
//If a table trigger is required when a record in the table is deleted use the following.
DataHelper.DeleteDataTriggerWorkflow(popPvDtl);
}
catch(Exception e)
{
Model.WriteTrace(e.Message);
}
Built-in Functions
Some of the built-in functions that can be called from the C# activity:
//Get the id of the user that was assigned to this activity
string sActivityUserId = Model.GetActivityUser();
//Get the id of the creater of this instance of the model
string sCreaterId = Model.GetCreator();
//Get the user id of the final approver of the instance
string sApproverId = Model.GetFinalApprover();
/*Construct a complete 7i web request, pass it to the engine to send, and receive the response xml document. If a 7i web request needs to be sent to a specific 7i web server then provide the second parameter with a qualified url of the web server. Otherwise skip the second parameter.
Example: "http://server name/ifas7/isapi/btwebrqb.dll"
NOTE: If url has 'https' then TLS 1.2 SSL procol will be used*/
XmlDocument xmlResponse = Model.SendRequest(xmlrequest, url);
//Get ODBC connection name that the server is using for BusinessPlus
string sCnxName = Model.GetConnectionName();
//Get the 7i web server url of this server
//Example:"http://your server name/ifas7/isapi/btwebrqb.dll"
string sUrl = GetWebrqbUrl();
//To mark the instance 'Pending' use the following. This will exit the C# activity and leave the workflow model if 'Pending' is set to “true” then it will re-enter the workflow model based on the activity expiration settings. This can be used to delay the activity by X minutes. For example, if X condition has not been met yet then wait X minutes. Or if a job is launched a function can be written to check the job status and not continue with the model until it has completed.
Model.Pending = true;
//Send email to the given email address
Model.Mail(to, subject, message);
Model.WriteTrace(message);
XmlDocument xmlInstanxce = Model.GetInstanceData();
Example: xDataTriggerWorkflow function
Please note: The xDataTriggerWorkflow function cannot be used with the GetTableDataDirectFetch since xDataTriggerWorkflow requires the IFAS.Common.GenericBT20Object to define the table.
public void Run() { WFInstance wfInstance = new WFInstanceClass(); wfInstance = Model.GetInstanceRecord(); string wfModelId = wfInstance.ModelId; string sUniqueKey = Source.UniqueKey; Model.WriteTrace("Starting " + wfModelId + "sql activity"); string sql = "select unique_id from hr_empnotes where unique_key='" + sUniqueKey + "'" ; try { DataTable table = DataHelper.GetTableDataDirectFetch(sql); foreach (DataRow row in table.Rows) { string sUniqueId = row["unique_id"].ToString(); DataParam HREmpnotes = new DataParam("hr_empnotes"); HREmpnotes.SetColumn("addl_4", sUniqueId); HREmpnotes.WhereCondition("unique_key",sUniqueKey); try { DataHelper.UpdateData(HREmpnotes); } catch(Exception e) { Model.WriteTrace(e.Message); } } Model.WriteTrace("End sql activity"); } catch (Exception e) { Model.WriteTrace(e.Message); return; //abort } }