What I’m wondering is, is there a way to run an UPDATE BAQ statement using the DynamicQueryAdapter object (or anything else in the customisations code)?
I’ve got an updateable BAQ set up and when I run the Update in the analyse tab it does work, however when I look at the query phrase in the General tab it’s still a SELECT statement so I’m not sure how I would go about using it in a customisation.
Is it possible to run an UPDATE statement from a BAQ in a similar manner to the one above (so what would happen is I would click a button and then the BAQ would run the UPDATE statement for now, later on I’d add parameters and a textbox for the updated value) or is that not possible?
The reason I’m using a BAQ and not a SqlCommand with the SQL passed in as a string (where I could just write the UPDATE query myself) is because I’m testing the runtime of BAQ’s, so the UPDATE has to be ran through a BAQ.
Hi @Rik first, you should NEVER do the above. Never in Epicor should you write a SQL Command and or a SQL update command. Everything you do should be via the BOs or UBAQs. Running SQL violates your Epicor support agreement and it is a quick way to get into a lot of pain.
Yes you can run an updatable BAQ from code all you need to do is run a Trace on the BAQ Designer and see what it does / replicate on your code.
Used a DynamicQueryAdapter to run the query then updated the DynamicQueryAdapter’s QueryResults “Results” table with what I needed updating and then ran the DynamicQueryAdapter’s Update method.
@Rik, any chance you can provide the code you got working? I’m looking to do the same thing and haven’t been able to find any good examples of updatable BAQs within UI customization (C#).
@Chris_Conn, thank you for the example. Populating the grid with the query results is working fine.
How do I modify the code to save any changes made to the data in the grid? The BAQ is already updatable, but I am not sure how to initiate the update from the customization. Any suggestions?
If you use the ExecuteByID method on DynamicQuery then the BAQ won’t be updatable in your code.
Here’s a version of what we use:
if (baqName != string.Empty)
{
if (!gotBAQ)
{
if (adptr.GetByID(baqName)) { gotBAQ = true; }
}
if (updateable)
{
if (!(ds != null)) { ds = adptr.DynamicQueryData; }
if (ds.DynamicQuery.Rows.Count == 0)
{
Ice.BO.DynamicQueryDataSet dsQDesign = adptr.QueryDesignData;
DataRow targetRow;
foreach (DataTable table in ds.Tables)
{
foreach (DataRow sourceRow in dsQDesign.Tables[table.ToString()].Rows)
{
targetRow = table.NewRow();
targetRow.ItemArray = sourceRow.ItemArray;
table.Rows.Add(targetRow);
}
}
}
if (!(dsBAQ != null)) { dsBAQ = adptr.GetQueryExecutionParameters(ds); }
}
else
{
if (!(dsBAQ != null)) { dsBAQ = adptr.GetQueryExecutionParametersByID(baqName); }
}
if (baqParams != null)
{
int i = 0;
foreach (KeyValuePair<string, string> p in baqParams)
{
bool empty = false;
string key = p.Key;
string val = p.Value;
if (key.Substring(0,1) == "-")
{
if (val == string.Empty) { empty = true; }
key = key.Substring(1, key.Length - 1);
}
dsBAQ.ExecutionParameter[i].ParameterID = key;
dsBAQ.ExecutionParameter[i].IsEmpty = empty;
dsBAQ.ExecutionParameter[i].ParameterValue = val;
i++;
}
dsBAQ.AcceptChanges();
}
if (updateable)
{
adptr.Execute(ds, dsBAQ);
}
else
{
adptr.ExecuteByID(baqName, dsBAQ);
}
if (adptr.QueryResults != null && adptr.QueryResults.Tables.Count > 0)
{
results = adptr.QueryResults.Tables["Results"];
}
else
{
results = new DataTable();
}
if (!(edv != null)) { edv = (EpiDataView)oTrans.EpiDataViews[baqName]; }
if (!(edv != null))
{
edv = new EpiDataView();
oTrans.Add(baqName, edv);
}
edv.dataView = results.DefaultView;
if (grid != null) { grid.DataSource = results; }
}
You can leave out the last line and bind the grid to the EpiDataView directly if you want. Either way, as long as you make sure the parts of the above where “updateable” = true are the ones being used, the BAQ data will be updatable.
Look up the DynamicQuery adapter Update method signature in Object Explorer. You need to pass your DataSets etc back into it. I think that’s your key problem.
I could not find it in object explorer - it is not listing this adapter, I tried a lot in google also the method signature for DynamicQueryAdapter.Update() but no luck;
could you please tell me the signature or where exactly I can see the signature.
It IS in the Object Explorer, but it tricks you by starting alphabetically again for the Ice adapters after the Erp adapters. You need to scroll down further.
Just a quick query . Code is working but instead update changes is coping current row and adding new row to it so i have old row plus new one with changes. I don’t understand why:(
Here is code I used to exec a UBAQ and add new Rows which I use as my Server File Writer, so I can on-prem write to UNC Paths as the Service Account (Server Side) and not Client Side!
public System.Data.DataSet xyz()
{
// Declare and create an instance of the Adapter.
DynamicQueryAdapter adapterDynamicQuery = new DynamicQueryAdapter(this.oTrans);
adapterDynamicQuery.BOConnect();
// Declare and Initialize Variables
string BAQName = "HASO-FileWrite";
bool more;
var dqds = adapterDynamicQuery.GetQueryExecutionParametersByID(BAQName);
var ds = adapterDynamicQuery.GetList(BAQName, dqds, 0, 0, out more);
// This shows me adding 2 Rows manually, here you would loop through your dataset and add many many rows
// then you would call .Update once! And then in the BPM you can Combine them all and use
// WriteAllText to write it out in a single file open.
var newRow = ds.Tables["Results"].NewRow();
newRow["Calculated_FileName"] = "MyFileName.txt";
newRow["Calculated_Content"] = "rrrrrra";
newRow["RowMod"] = "A";
newRow["SysRowID"] = Guid.NewGuid();
newRow["RowIdent"] = newRow["SysRowID"].ToString();
ds.Tables["Results"].Rows.Add(newRow);
var newRow2 = ds.Tables["Results"].NewRow();
newRow2["Calculated_FileName"] = " MyFileName.txt";
newRow2["Calculated_Content"] = "eeeeee";
newRow2["RowMod"] = "A";
newRow2["SysRowID"] = Guid.NewGuid();
newRow2["RowIdent"] = newRow2["SysRowID"].ToString();
ds.Tables["Results"].Rows.Add(newRow2);
adapterDynamicQuery.Update(BAQName, ds);
// Get Results if we need to read for example a Success or Failure Column
// results.Tables["Results"].Rows[0]["Calculated_SomeSuccessColumn"]
DataSet results = adapterDynamicQuery.QueryResults;
// Cleanup Adapter Reference
adapterDynamicQuery.Dispose();
return results;
}
PS: For those always saying I want to log to a shared text-file on my on-prem here you go make a UBAQ and safely write in a controlled manner (dont let the User pick a path) to a UNC Path as a Service Account, Logs Away!