E10: BPM that updates database table

Yahoo Archive
1
It doesn't hurt to filter on company, I just hate typing stuff that I don't need.

After adding Ice.Contracts.BO.UD110 to the references.

Ice.Contracts.UD110SvcContract boUD110 = null;
Ice.Tablesets.UD110Tableset dsUD110 = null;

// make connection
boUD110 = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.UD110SvcContract>(Db);

// call
dsUD110 = boUD110.GetRows(ud110Where, String.Empty, ud110aWhere, String.Empty, pageSize, absolutePage, out morePages);

Jim Kinneman
Encompass Solutions, Inc


1 Like
2
Hi All,

So I’m testing my first converted BPM from E9 that updates a database table and I can’t seem to get it working.
Below is my code.
This BPM is updating OrderRel.ReqDate whenever and associated Job’s Due Date is updated.
This is a Post Processing BPM on JobEntry.Update.

When this runs, the error is:
Description: The underlying provider failed on EnlistTransaction.
Inner Exception: The operation failed because the session is not single threaded.

Any ideas what I have wrong?


Erp.Tables.JobProd JobProd;
Erp.Tables.OrderRel OrderRel;

foreach (var ttJobHead_iterator in (from ttJobHead_Row in ttJobHead
where ttJobHead_Row.Company == Session.CompanyID
select ttJobHead_Row))
{
var ttJobHeadRow = ttJobHead_iterator;
foreach (var JobProd_iterator in (from JobProd_Row in Db.JobProd
where string.Compare(JobProd_Row.Company, ttJobHeadRow.Company, true) == 0
&& string.Compare(JobProd_Row.JobNum, ttJobHeadRow.JobNum, true) == 0
select JobProd_Row))
{
JobProd = JobProd_iterator;
if (JobProd.OrderNum > 0)
{
using (var txscope1 = IceDataContext.CreateDefaultTransactionScope())
{
OrderRel = (from OrderRel_Row in Db.OrderRel
where string.Compare(OrderRel_Row.Company, JobProd.Company, true) == 0
&& OrderRel_Row.OrderNum == JobProd.OrderNum
&& OrderRel_Row.OrderLine == JobProd.OrderLine
&& OrderRel_Row.OrderRelNum == JobProd.OrderRelNum
select OrderRel_Row).FirstOrDefault();
if (OrderRel != null)
{
OrderRel.ReqDate = ttJobHeadRow.ReqDueDate;
Db.Validate(OrderRel);
}
txscope1.Complete();
}
}
}
}




Joe Rojas | Director of Information Technology | Mats Inc
dir: 781-573-0291 | cell: 781-408-9278 | fax: 781-232-5191

addr: 37 Shuman Ave | Stoughton | Ma | 02072-3734
jrojas@... | www.matsinc.com
Ask us about our clean, green and beautiful matting and flooring

[cid:b5ee6f.png@8875290c.4b8fd0c1]
This message is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company.




[Non-text portions of this message have been removed]
3
Nested LINQ statements that iterate will create a multi-thread transaction which will give you the error.  A workaround that sometimes handles things is to use a different DB context for lookups that won't be changed (jobprod) and then do update on the primary context.

ErpContext DbLookup;

DbLookup = new ErpContext();

foreach (var JobProd_iterator in (from JobProd_Row in DbLookup .JobProd ...

Another approach is to use business objects to do your look-ups and updates, this will avoid this issue too.  BO methods in E10 don't carry the overhead they did in E9 so not much of a performance trade-off to use them.  

Use the appropriate getbyid (or maybe getrows) to get the relevant jobprod and order records and then use order.update to make the change.  Using BO methods is actually recommended so that all the business logic and validation will be applied.

On your look-up on ttJobHead , no need to filter on company, temporary tables only exist in the context of your session so only contain records relevant to the current company.

Jim Kinneman
Encompass Solutions, Inc

4
Thanks Jim!

I will look into these options.
I didn’t know about not needing to filter on Company. It’s just a habit that I picked up with E9 where I was just taught to always include company.

Do you have a quick snippet of code to show how to instantiate a BO from a Method Directive?





Joe Rojas | Director of Information Technology | Mats Inc
dir: 781-573-0291 | cell: 781-408-9278 | fax: 781-232-5191

addr: 37 Shuman Ave | Stoughton | Ma | 02072-3734
jrojas@... | www.matsinc.com
Ask us about our clean, green and beautiful matting and flooring

[cid:83eec5.png@413dcdcc.429071f2]
This message is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company.


From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Wednesday, November 26, 2014 4:30 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: E10: BPM that updates database table



Nested LINQ statements that iterate will create a multi-thread transaction which will give you the error. A workaround that sometimes handles things is to use a different DB context for lookups that won't be changed (jobprod) and then do update on the primary context.

ErpContext DbLookup;

DbLookup = new ErpContext();

foreach (var JobProd_iterator in (from JobProd_Row in DbLookup .JobProd ...

Another approach is to use business objects to do your look-ups and updates, this will avoid this issue too. BO methods in E10 don't carry the overhead they did in E9 so not much of a performance trade-off to use them.

Use the appropriate getbyid (or maybe getrows) to get the relevant jobprod and order records and then use order.update to make the change. Using BO methods is actually recommended so that all the business logic and validation will be applied.

On your look-up on ttJobHead , no need to filter on company, temporary tables only exist in the context of your session so only contain records relevant to the current company.

Jim Kinneman
Encompass Solutions, Inc




[Non-text portions of this message have been removed]