 |
Epicor Business Process Management C# code that is clean, efficient, and adheres to best practices. |
One of the important attributes for an Epicor Software Technical Consultant is to have the ability to not only produce solutions that work, but produce solutions that are efficient and well written. Here are a few handy C# coding tips that I’d like to provide to assist you in developing efficient Epicor BPMs. These tips relate to the reading of records from the database.
1 – Global variables. What’s available?
A number of variables are available within BPMs that you can make use of in your C# code. These are listed below:
Session.CompanyID
Session.PlantID
Session.UserID
When reading records from the database and joining tables in a LINQ query always ensure you use Company (even if you are setup as single company).
With “Session” you can get access to other properties related to the current session such as CompanyName, DEC_COST, DEC_GENERAL, DEC_PRICE, DefaultUOM, EmployeeID, and WorkstationID.
2 – Use “Any()” to check if a record exists or “FirstOrDefault()” to return the first matching record.
When reading records from the Epicor database you should decide what it is you wish to do. Do you need to simply check if a record exists on file, or return the first record that matches your query? The example below shows an efficient way of checking if a record exists without having to pull any of the data from file:
bool OrderHedExists = (from row in Db.OrderHed where row.Company == Session.CompanyID && row.OrderNum == ordernum select row).Any();
To read in a single record (as opposed to just checking if it exists) you just need to swap the “Any()” with “FirstOrDefault()”.
Leaving off the “Any()” function will return all matching records.
3 – Read records with a field list.
If you only require one or two fields from a record then, rather than return the entire record, read it with a field list so that you only pull the required data from file. The example below shows how you can best achieve this:
var OrderDtlColResult = (from row in Db.OrderDtl
where row.Company == Session.CompanyID &&
row.OrderNum == ordernum &&
row.OrderLine == orderline
select new { row.PartNum, row.Description }).FirstOrDefault();
if (OrderDtlColResult != null)
{
string partnum = OrderDtlColResult.PartNum;
}
The properties that OrderDtlColResult will have will be just “PartNum” and “Description”. Always consider what data you need from the record and decide if it makes sense to use a field list as opposed to pulling the entire record from file.
4 – Reading records from multiple tables with JOIN.
If you want to pull related data from multiple tables then by far the best way to do this is with a JOIN. This means you can do a single LINQ query to access the data you need without having to do multiple select statements. Shown below is an example of this:
var joinResultRows = from orderhedRow in Db.OrderHed
join custRow in Db.Customer
on new { orderhedRow.Company, orderhedRow.CustNum }
equals new { custRow.Company, custRow.CustNum }
select new {orderhedRow.PONum, custRow.CustID};
foreach(var joinResult in joinResultRows)
{
string custID = joinResult.CustID;
string ponum = joinResult.PONum;
}
The example above joins the OrderHed and Customer tables on the Company and CustNum fields. The result set that is returned is comprised only of the OrderHed.PONum field and the Customer.CustID field.
I hope these tips help you the next time you are writing some C# code in a BPM, and I’ve plenty more of these helpful code snippets that I’ll be posting up soon. Reading records from the database in the most efficient way keeps your code efficient and precise. Oh – don’t forget to keep your code commented as this helps you in the future should you need to revisit your BPM and it helps others understand what you’ve written!