 |
Epicor Business Process Management C# code that helps you put your ideas into action. |
In my previous blog post I detailed some great tips for your BPM C# code in relation to reading records from the Epicor database. In this next post I provide some further advice and guidance that you can adopt when updating, deleting, and creating records in the Epicor database (this post assumes knowledge of the Epicor application and advanced BPMs).
IMPORTANT NOTE: The examples below should only be considered for areas where you are certain you will not be breaking any business logic – such as temporary records in a User Defined table used for reporting (so if you’re unsure or a beginner with Epicor wait for my next post). The main point of the examples is to describe the transaction block.
Normally, when updating/inserting/deleting, you should make use of the method calls in the business objects – I’ll show how you to go about that in my next post and put a link through from here.
1 – Updating records in the Epicor database.
Below is a code example I have put together to demonstrate how to update an existing record in the Epicor database using C# code in a BPM. If you look closely at the “foreach” statement you will notice a section of this statement contains a “LockHint”. This is an important point that you need to always keep in mind when updating records. The inclusion of “LockHint” ensures that the record remains locked until the update to the database is triggered at the end of the transaction block (see the note at the end which explains the transaction block).
“LockHint” also ensures that your code will wait for a locked record to be released.
The example below shows how to update the value in the ShortChar01 field on a particular record in the UD01 table:
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
foreach(var UD01 in (from row in Db.UD01.With(LockHint.UpdLock) where
row.Company == Session.CompanyID && row.Key1 == "12345"
select row))
{
UD01.ShortChar01 = "abcde";
}
Db.Validate();
txScope.Complete();
}
The update is committed to memory via the “Db.Validate()” line (which, for several sales order lines, would commit them to memory all at once). You can commit them to memory one at a time by calling “Db.Validate(UD01)” from within the loop, but doing it that way is less efficient than the example above.
2 – Deleting records from the Epicor database.
I’ve provided an example below which shows how you can delete records from the Epicor database. As you can see the “LockHint” aspect of the “foreach” statement is still in place and is important as you need to ensure the record you want to delete is free for you to lock:
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
foreach(var UD01 in (from row in Db.UD01.With(LockHint.UpdLock) where
row.Company == Session.CompanyID && row.Key1 == "12345"
select row))
{
Db.UD01.Delete(UD01);
}
Db.Validate();
txScope.Complete();
}
The same aspects of the transaction block apply here in relation to “Db.Validate()” and the closing brace of the ‘using’ statement, in just the same way as in the previous example for updating a record in the Epicor database.
3 – Creating records in the Epicor database.
The example below shows how you can add a record to the Epicor database – obviously here there is no need for any lock:
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
UD01 newRow = new UD01();
Db.UD01.Insert(newRow);
newRow.Company = Session.CompanyID;
newRow.Key1 = "12345";
newRow.ShortChar01 = "abcde";
Db.Validate();
txScope.Complete();
}
Again, the same aspects of the transaction block apply.
The transaction block.
The transaction block is an important aspect of your code and should always be part of your solution when adding, deleting, and creating records in the Epicor database. The transaction block consists of the following code:
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
...
//code...
...
Db.Validate();
txScope.Complete();
}
“Db.Validate()” commits any changes to the database to memory and the “txScope.Complete()” statement confirms that changes are good and ok to commit to the database. You should generally always have these two statements as the final two statements of your transaction block. You can modify many different tables and records in a single transaction block – any failure from “Db.Validate()” and “txScope.Complete()” will mean that nothing within the transaction block will be committed to the database (you can nest transaction blocks but note that the entire root transaction will roll back if any of the nested transaction blocks fail).
The actual commit to the database only takes place when the transaction is disposed (which is at the point of the closing brace for the ‘using’ statement). Remember – the database uses optimistic locking so do not attempt to update or delete records without issuing a “LockHint” as detailed in the examples in this blog post.
I hope you find the above useful and informative. If you need assistance or advice with any piece of work you may be doing then do please get in touch and I’d be glad to help you in any way.