Hello… this is my new issue. Still working on converting E10 customizations to Kinetic 2022x.
There is a custom that runs some direct queries using the SqlCommand method. Since we are moving to azure direct access to DB seems not to be possible, at least that’s what we have been told, so we need to change this. I thought about creating a BAQ with the query and then calling it with the Ice.Proxy.BO.DynamicQueryImpl… but have no idea on how to adapt current code to that because of the parameters I need to use. Any suggestion will be very appreciated.
You are on the right track.
(Depending on what you are doing, Linq may also be appropriate, but I digress.)
You then need to decide where it’s most appropriate place to run these queries, the client, or
the server.
The server is going to be the most logical place going forward, but either can be done.
You can call Epicor Functions from the client using REST to get data back to the client,
or call the Dynamic Query Adapter directly.
(You can also call business objects and services via REST as well, but I digress… again.)
If you explain in more detail what you are doing, we can probably give you more specific answers.
//Add the dynamic query adapter from the customization wizard:
//Tools->Wizards->Customization Wizards=>Reference Adapter/BL Assemblies->Get Adapters
//Scroll way down, it List Erp Adapters first, then Ice Adapters, so the alphabetical list starts over
using Ice.Adapters;
string queryToRun = "TestQuery";
DynamicQueryAdapter dQ = new DynamicQueryAdapter(UD01Form);
dQ.BOConnect();
QueryExecutionDataSet qeds = dQ.GetQueryExecutionParametersByID(queryToRun);
//qeds will already have these in it, but if you don't know what order they are in, this is easier.
qeds.ExecutionParameter.Clear();
qeds.ExecutionParameter.AddExecutionParameterRow("Parm1", "Mark_Wonsil" , "nvarchar", false, Guid.NewGuid(),"A");
qeds.ExecutionParameter.AddExecutionParameterRow("Parm2", "Yo Adrian" , "nvarchar", false, Guid.NewGuid(),"A");
dQ.ExecuteByID(queryToRun, qeds);
epiUltraGridC1.DataSource = dQ.QueryResults.Tables["Results"];
dQ.Dispose();
Server ->Function:
//Call from BPM/UBAQ is similar, but you would use ServiceRenderer
//Ice.Contracts.DynamicQuerySvcContract dQ = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>(Db);
CallService<Ice.Contracts.DynamicQuerySvcContract>(dQ =>
{
string queryToRun = "TestQuery";
Ice.Tablesets.QueryExecutionTableset dsQueryExecution = new QueryExecutionTableset();
ExecutionParameterRow drRow = null;
drRow = new ExecutionParameterRow();
drRow.ParameterID = "Parm1";
drRow.ParameterValue = "Mark_Wonsil";
drRow.ValueType = "nvarchar";
drRow.IsEmpty = false;
drRow.RowMod = "A";
drRow.SysRowID = new Guid();
dsQueryExecution.ExecutionParameter.Add(drRow);
drRow = new ExecutionParameterRow();
drRow.ParameterID = "Parm2";
drRow.ParameterValue = "Yo Adrian";
drRow.ValueType = "nvarchar";
drRow.IsEmpty = false;
drRow.RowMod = "A";
drRow.SysRowID = new Guid();
dsQueryExecution.ExecutionParameter.Add(drRow);
DataSet dsResults = dQ.ExecuteByID(queryToRun, dsQueryExecution);
var dt = dsResults.Tables["Results"];
});
Really really appreciated @klincecum … your code helped a lot.
right now I`m just working on “fix” everything we used to have in E10 to work in 2022.2 and have it ready for our upcoming go live late April…
here is a copy of the code I “tweak” and seems to be working…
probably in the near future… as an EoY project, I will need to start working on moving everything we have to Kinetic and start using Application Studio…
private void SearchOnVendorAdapterShowDialog()
{
// Wizard Generated Search Method
// You will need to call this method from another method in custom code
// For example, [Form]_Load or [Button]_Click
if (_edvUD10.Row == -1){
MessageBox.Show("Please Create A New Service Entry First");
return;}
bool recSelected;
//string whereClause = string.Empty;
string phone = string.Empty;
System.Data.DataSet dsVendorAdapter = Ice.UI.FormFunctions.SearchFunctions.listLookup(this.oTrans, "VendorPPSearchAdapter", out recSelected, true, whereClause);
if (recSelected)
{
System.Data.DataRow adapterRow = dsVendorAdapter.Tables[0].Rows[0];
string supplierAddress = (string)adapterRow["Address1"] + "\n" + adapterRow["City"] + " " + adapterRow["State"] + " " + adapterRow["Zip"];
DataRow editRow = this._ud10Adapter.UD10Data.UD10.Rows[this._edvUD10.Row];
editRow.BeginEdit();
// Map Search Fields to Application Fields
this._edvUD10.dataView[this._edvUD10.Row]["ShortChar13"] = (string)adapterRow["Name"];
this._edvUD10.dataView[this._edvUD10.Row]["ShortChar02"] = (string)adapterRow["VendorNumVendorID"];
this._edvUD10.dataView[this._edvUD10.Row]["Character09"] = supplierAddress;
editRow.EndEdit();
Ice.Proxy.BO.DynamicQueryImpl dQ = WCFServiceSupport.CreateImpl<Ice.Proxy.BO.DynamicQueryImpl>((Ice.Core.Session)oTrans.Session, Ice.Proxy.BO.DynamicQueryImpl.UriPath);
Ice.Core.Session session = (Ice.Core.Session)ProjectEntryForm.Session;
string number = "";
string query = "SearchOnVendorAdapter";
QueryExecutionDataSet qeds = dQ.GetQueryExecutionParametersByID(query);
qeds.ExecutionParameter.Clear();
qeds.ExecutionParameter.AddExecutionParameterRow("Company", session.CompanyID, "nvchar", false, Guid.NewGuid(), "A");
qeds.ExecutionParameter.AddExecutionParameterRow("VendorID", adapterRow["VendorNumVendorID"].ToString(), "nvchar", false, Guid.NewGuid(), "A");
qeds.ExecutionParameter.AddExecutionParameterRow("PurPoint", adapterRow["PurPoint"].ToString(), "nvchar", false, Guid.NewGuid(), "A");
dQ.ExecuteByID(query, qeds);
DataTable PurPoint = new DataTable();
foreach(DataRow dr2 in PurPoint.Rows)
{
number = (string)dr2["ShortChar02"];
}
phone = number;
if(phone == "")
{
bool venSelected;
whereClause = "VendorNum=" + adapterRow["VendorNum"].ToString();
System.Data.DataSet dsVendorBaseAdapter = Ice.UI.FormFunctions.SearchFunctions.listLookup(this.oTrans, "VendorAdapter", out venSelected, false, whereClause);
if (venSelected)
{
if (dsVendorBaseAdapter.Tables[0].Rows.Count > 0)
{
System.Data.DataRow vendorRow = dsVendorBaseAdapter.Tables[0].Rows[0];
phone = (string)vendorRow["PhoneNum"];
}
}
}
editRow.BeginEdit();
this._edvUD10.dataView[this._edvUD10.Row]["ShortChar14"] = phone;
this._edvUD10.dataView[this._edvUD10.Row]["ShortChar15"] = (string)adapterRow["PurPoint"];
editRow.EndEdit();
dQ.Dispose();
}
}
PS. if you find opportunity areas on the code above… I will be more than glad to read and implement them!!!
Makes the code a bit more readable, and then everything looks the same when you are looping through a collection. If anyone can confirm or correct me please do, as I have been coaching folks here to follow the second method.
My advice: Spend some time seeing how Kinetic Web UI works,
and start moving your classic style customizations to the same pattern.
Move what you can to the server. Then when the Kinetic Web UI is mature
enough for what you need to do, the move is easier.
That worked? Interesting. Should be "nvarchar".
If it works, leave it. It’s gonna have to be redone anyway.
once again, thank you very much for all the help. Will focus, for now, on get everything “working” as soon as possible, after that, will start moving everything to functions and/or to kinetic UI…
