currently i get data from sql stored procedure and i want to show this data on gridview through dashboard and customization screen.
I just want to know how can i call stored procedure without using External BAQ Stored Procedure using adapter.
I just want to pass the stored name under sql and parameters but not the whole T-SQL in the code
Thank you! Have a nice day!
Calling a db function from the front end directly completely breaks the SOA that epicor is built upon and itâs a pretty big security risk
You should write a BAQ and call it if thatâs what you need
You should also not create stored procedures in the epicor database these are not (and should not) be respected by epicor during upgrades and maintenance and they can and will get deleted
Epicor assumes a Pristine database that hasnât been tampered with .
I could recommend you turn to BAQs and the BOs to accomplish your goal
3 Likes
Not sure why you donât want to use an external BAQ, but I have done something similar in that I am getting data from an external database using an external BAQ. I made this updatable and wrote a script on the get list event to write the data to a UD table (using the UD Table business objects). I then use an EPICOR Function to execute the BAQ which can be scheduled or run on demand.
1 Like
thank you but using BAQ of epicor sometimes encounters large records will be slow, currently using BAQ and BO is slow so I want to switch to processing under SQL and then just call stored load the results to the grid like many systems are doing. do. Thank you very much, the security issues here are also internal to the company, with this just wanting the epicor system to develop more ways to call stored to load data. this when you upgrade the system, it is true that the DB will change, but why not separate the user and system DBs?
you misunderstand me here i still use epicorâs DB not external BAQ but i want through some epicor adapter to be able to call stored procedure written in SQL just to load the results on dashboard grid instead of using BAQ because epicorâs BAQ is sometimes not optimal when there is big data it is slow
I want epicor to build a method that can be called stored just to load data into dashboard grid
Iâm in Vietnam, so Iâm in a different time zone with you guys
Thank you very much! have a nice day!
It can be done but as others have said itâs not recommended.
Iâm all for increasing performance as much as possible, but is it really going to be that much faster with a stored procedure? Curious to hear if youâre able to quantify the difference while working through this project⌠In any case, Iâd say it would have to be a HUGE performance boost to business-critical functionality to make it worth the risks youâd by takingâŚ
But if you insist, look at Microsoft documentation for System.Data.SqlClient. You open a SqlConnection, then execute a SqlCommand of type StoredProcedure, then you should be able to bring that data into a DataView and attach to your grid.
1 Like
this is how i used to do it in an old system we built but this is epicor so i just want to have a method called stored that returns the result to a datatable and then i assign it to a view. I donât want to use SqlConnection to secure the SQL connection string. I can use customization to call exec in two ways:
private void ExecuteQuery(string cmd)
{
Ice.Core.Session session=this.oTrans.Session as Ice.Core.Session;
string data=GetConnection();
data=getdata(data);
SqlConnection sqlCon=new SqlConnection(data);
SqlCommand sqlCmd = new SqlCommand(cmd, sqlCon);
try
{
sqlCon.Open();
sqlCmd.ExecuteNonQuery();
sqlCon.Close();
}
catch {
sqlCon.Close();
}
}
OR:
private DataTable GetBaq(string baq, string key2)
{
DynamicQueryAdapter qda = new DynamicQueryAdapter(oTrans);
qda.BOConnect();
QueryExecutionDataSet qds = qda.GetQueryExecutionParametersByID(baq);
qds.ExecutionParameter.Clear();
//qds.ExecutionParameter.AddExecutionParameterRow(âkey1â,key1, ânvarcharâ, false,Guid.NewGuid(), âAâ);
qds.ExecutionParameter.AddExecutionParameterRow(âkey2â,key2, ânvarcharâ, false,Guid.NewGuid(), âAâ);
qda.ExecuteByID(baq,qds);
return qda.QueryResults.Tables[âResultsâ];
}
here I can use baq which is an EXEC statement, a Stored Procedure is fine.
I want epicor to support the development of more features called Stored Procedure to make it easier to write and retrieve data through BAQ because sometimes epicorâs BAQ is not really optimized for big data
thank you very much. I just want to exchange to see who has a better solution using guaranteed not to break the systemâs SOA.
Have a nice day!
Welcome @thanvanhai!
One other thing to be aware, a lot of online systems now practice âeventual database consistency,â including Epicor. What this means is that to make the online system more performant, more data is cached in memory and eventually is written to the database. Normally, this will happen in less than a second, but if you call your stored procedure right after an update youâve made, it may not include that last transaction.
1 Like
I see what you mean here this screen i just want to get the data to make the report data no effect insert , update , delete anything just output data to gridview instead I write stored procedure to return data data for users I want to create a screen for them to get data from instead of me having to run a stored procedure under T-SQL and then copy the results and send it to them.
Have a nice day!
Understood, I just want you to be aware that not all data may be flushed to the database yet (from other users) to be picked up by your SP, so your grid may be out of date. And for one scenario, that may not matter, but it may in others.
2 Likes
It is slower for a reason thereâs a lot of security and data sanitation implications if you want to bypass that at least use an External BAQ this bypasses all the security and data restrictions built into epicor but is still a supported method.
You can point your External BAQ to the Internal Epicor DB⌠but again you should not write stored procedures in the Epicor Db ⌠âEVERâ this is seriously a breach of support and protocol. Id recommend if you MUST then do this in a separate replicated Db.
1 Like