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 IFAS database schema. Views or non-IFAS tables are not supported. Delete operations are allowed by C# on a business rule basis; that functionality is not discussed on this page.

Create a C# Workflow Activity

In the Workflow Designer application, select "C#" from the Action drop-down menu in the Activity 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 IFAS 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.DataParam popPvDtl = new DataParam("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)
{
//e.Message
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 and eWhereOR. eWhereAND is the default value, if not provided.

Table Records Count

The following SQL query:

"select count(*) from Table_Name where ColumnA='SomeValue' and ColumnB <> 'SomeValue'"

can be accomplished as:

IFAS.Common.DataParam popPvDtl = new DataParam("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.

IFAS.Common.DataParam popPvDtl = new DataParam("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 = Convert.ToDecimal(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.DataParam popPvDtl = new DataParam("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 = Convert.ToInt32(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.DataParam popPvDtl = new DataParam("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;

int nUpdated = DataHelper.UpdateTable(popPvDtl);
Model.Trace("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.

DataParam popPvDtl = new DataParam("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;
DataHelper.InsertData(popPvDtl);
}

catch(Exception e)
{
Model.WriteTrace(e.Message);
}