I have some code that I wrote in a form event that reads in data from an external BAQ to populate the ProjectCst table. It works great in the form but it has been requested that we do this as a batch-type process where the user can just run the process once instead of having to do into each project to do it.
I decided to try using an Epicor Function to do this. My problem is that it can can use the QueryExecutionDataSet to execute the query but it can’t seem to find the DynamicQueryAdapter or oTrans object to do it as it would in the form. I have not been able to add the proper using statement to find those. I also started to go down the path of using SQL connection commands as I have in other applications (not in Epicor) but I was still missing something.
Any assistance would be appreciated. I have attached the code as it exists within the form event plus some code to attempt to loop through all active projects.
var project = Db.Project.Where(e => e.ActiveProject == true);
string vProject = "";
DynamicQueryAdapter dqa;
QueryExecutionDataSet qeds;
DataTable dt;
DataRow r;
string Cobra_Project = "";
DataTable dtCobra;
var edv;
DataRow dr;
decimal calcVal = 0;
foreach (var PROJ in project)
{
vProject = PROJ.ProjectID;
dqa = new DynamicQueryAdapter(oTrans);
dqa.BOConnect();
qeds = dqa.GetQueryExecutionParametersByID("ASI_Cobra_Project");
qeds.ExecutionParameter.Clear();
qeds.ExecutionParameter.AddExecutionParameterRow("ProjectID", vProject , "nvarchar",false, Guid.NewGuid(),"A");
dqa.ExecuteByID("ASI_Cobra_Project",qeds);
dt = dqa.QueryResults.Tables["Results"];
if (dt.RowCount > 0)
{
//just go get the first row and result
r = dt.Rows[0];
Cobra_Project = r["UD19_Character01"].ToString();
//now go get the data Cobra_Project_Cost
qeds = dqa.GetQueryExecutionParametersByID("Cobra_Project_Cost");
qeds.ExecutionParameter.Clear();
qeds.ExecutionParameter.AddExecutionParameterRow("ProjectID", Cobra_Project , "nvarchar",false, Guid.NewGuid(),"A");
dqa.ExecuteByID("Cobra_Project_Cost",qeds);
dtCobra = dqa.QueryResults.Tables["Results"];
edv = oTrans.Factory("ProjectCst");
calcVal = 0;
edv.dataView[edv.Row].BeginEdit();
foreach (dr in dtCobra.Rows)
{
if (dr["Calculated_Code_Type"].ToString() == "LABOR")
{
edv.dataView[edv.Row]["ManEstCtcLbrHrs"] = dr["Calculated_Total_Labor_Hours"].ToString();
edv.dataView[edv.Row]["ManEstCtcBurHrs"] = dr["Calculated_Total_Labor_Hours"].ToString();
calcVal = Convert.ToDecimal(dr["Calculated_Total_Labor_Dollars"].ToString()) - Convert.ToDecimal(edv.dataView[edv.Row]["TotActLbrCost"]) - Convert.ToDecimal(edv.dataView[edv.Row]["TotCtcLbrCost"]);
edv.dataView[edv.Row]["DocManEstCtcLbrCost"] = calcVal;
calcVal = Convert.ToDecimal(dr["Calculated_Total_Labor_Burden"].ToString()) - Convert.ToDecimal(edv.dataView[edv.Row]["TotActBurCost"]) - Convert.ToDecimal(edv.dataView[edv.Row]["TotCtcBurCost"]);
edv.dataView[edv.Row]["DocManEstCtcBurCost"] = calcVal;
}
else if (dr["Calculated_Code_Type"].ToString() == "ODC")
{
calcVal = Convert.ToDecimal(dr["Calculated_Total_ODC"].ToString()) - Convert.ToDecimal(edv.dataView[edv.Row]["TotActODCCost"]) - Convert.ToDecimal(edv.dataView[edv.Row]["TotCTCODCCost"]);
edv.dataView[edv.Row]["DocManEstCtcODCCost"] = calcVal;
}
else
{
calcVal = Convert.ToDecimal(dr["Calculated_Total_Material"].ToString()) - Convert.ToDecimal(edv.dataView[edv.Row]["TotActMtlCost"]) - Convert.ToDecimal(edv.dataView[edv.Row]["TotCtcMtlCost"]);
edv.dataView[edv.Row]["DocManEstCtcMtlCost"] = calcVal;
calcVal = Convert.ToDecimal(dr["Calculated_Total_Material_Burden"].ToString()) - Convert.ToDecimal(edv.dataView[edv.Row]["TotActMtlBurCost"]) - Convert.ToDecimal(edv.dataView[edv.Row]["TotCtcMtlBurCost"]);
edv.dataView[edv.Row]["DocManEstCtcMtlBurCost"] = calcVal;
}
}
edv.dataView[edv.Row].EndEdit();
}
}
Db.SaveChanges();
I have been trying to change this up a bit and use the DynamicQuerySvcContract to be able to run the BAQ but I have not been able to get the declaration right. While the syntax may be ok it always fails with not being able to convert types. I have tried things like this:
I have seen that, but it doesn’t translate from the widget to code well. In the code the Ice.DynamicQuery BO method doesn’t seem to exist or be accessible. I have other things going on around the BAQ to define the parameters being sent into the query so I am not sure how to get all that to work within the widget world (I’m new to Epicor and especially Epicor Functions). I was trying to find out what I need to do to be able to the the Dynamic Query or Dynamic Query Adapter into the code. BTW none of the adapters are available in the references. So the bottom line is that I am trying to find the coding solution.
Adapters are only used in the form/front end. In BAQ’s and functions you use the contracts. On the front end, the adapter handles making and managing the object for you, in the contract you have to do that yourself. So you can’t just drop the same code into a function or BPM and have it work.
Here’s an example where someone calls a BAQ from a BPM.
The issue I have wit the code you referenced is that when you try to do the Ice.Contracts.DynamicQuerySvcContract boDynamicQuery = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>(Db);
the Db isn’t the right context. I get a compiler error
System.Drawing.Bitmap CS1503 Argument 1: cannot convert from ‘EFx.ASILibrary.Implementation.ILibraryContext’ to ‘Ice.IceDataContext’
I have not been able to figure out how to get the right context variable set up to run. If anyone knows how to get that it may solve some of my problems. The other thing I have tried is the following code:
var context = Ice.Services.ContextFactory.CreateContext<IceContext>();
Ice.Contracts.DynamicQuerySvcContract tQuery = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>(context);
but then I get run time errors on the var context statement that MSDTC must be enabled. I have it enabled everywhere (my sql server, app server, and client) but I still get the error.
I have no idea how to get this working. Has anyone run a BAQ in a function?
You need to add the reference to the Dynamic Query BO in the function. It’s not done programatically, it’s done via the UI in the function
To know how to use to BO itself, you’d need to perform a trace of a dynamic query or use the forums to see how others have done it.
Not too late since I put this off for a while. I put your code into my module to see what would happen and I still get the “DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool” error. As far as I can tell it is enabled everywhere (client and servers). I must still be missing something.
MSDTC is usually a red herring in my experience, and is the error you get when something more fundamental is actually wrong. But having said that, I can’t imagine what it might be in this case. I can confirm I’ve used that exact form of code quite often by now, without problem.
It’s more likely to be in the structure of how you’re using the Function.
When I’ve been bitten by that MSDTC error, it was because a BPM was firing twice instead of once, and before the first one had finished. So it was trying to duplicate the transaction, and that’s why it was looking to be able to handle distributed transactions.
Is your code where you’re calling DynamicQuery likely to be doing anything like that?
Here is the code I have. I have stripped out the part needing the Dynamic Query just to see if I can get the basics to work. Basically, I am looping through all open projects, looking into a UD table to look up a cross reference, then call an external BAQ to return data to update the project. I have the external BAQ and updates stripped out of this.
string vProject = "";
DataTable dt;
DataRow dr;
string Cobra_Project = "";
string BAQName = "ASI_Cobra_Project";
DataTable dtCobra;
var context = Ice.Services.ContextFactory.CreateContext<ErpContext>();
using (var svc = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>(context))
{
}
foreach (var PROJ in project)
{
vProject = PROJ.ProjectID;
var CProj = Db.UD19.Where(e=> e.Key1 == vProject.ToString());
//pretend to loop - there should only be one line
foreach (var CName in CProj)
{
Cobra_Project = CName.Character01;
//set up the project cost table
var PCost = Db.ProjectCst.Where(e=>e.ProjectID == vProject);
foreach (var PCst in PCost)
{
//run the external BAQ
//PCst.ManEstCtcLbrHrs = value;
}
Db.SaveChanges();
}
}
What is there is giving me the MSDTC error. I’ll remove some of the Db stuff to see if that helps. Thanks.
EDIT - I removed all but the Db code at it ran. I put in just one Db statement and I get the error. I have a very simple function that’s using Db to set the earliest apply date to today just fine. Not sure what I have different between the two functions, but it must be missing something.
In my flailing efforts to get this working I checked both the Requires Transaction and For Internal Use Only on the function page. Once I unchecked them it ran without the MSDTC error! I am now working on debugging my code and when I do get it working I will post a sample.