Write to SQL table from within BPM/Directive

Epicor ERP 10
,
21

No worries. Point taken. ANYTHING outside of the Epicor native system I am sure could be a security and/or upgrade issue that all should be aware of. I walked into an environment that was replacing a home-grown roll your own system and had to endure many a meeting that discussed the age-old “Do we change the needs of the business to meet the needs of the software, or vice-versa”. For the most part we could convey the importance of staying as vanilla as possible for all the reasons we all know from a technical perspective (maintenance, upgrades, as well as why did you want to replace the old system to just rewrite the new?). Of course there were a few processes that were beyond compromise :frowning: and that is where my .Net code library came in handy. I am seeing responses from @TomAlexander and @josecgomez as I write this that that are also well taken. Security precautions are obviously important. I have always been a proponent of network security first and foremost (if they can’t get in then SQL injection, etc. is not really an issue - On Premise anyway - Oops, may have tripped the Zero Trust Security alarm there, but the second (application) layer is important just in case). I will heed the warning and use Jose’s suggestion of an Epicor external data source from within the BPM. I would post the code when I am done, but no need to encourage (anymore than I already have) how to “misuse” Epicor :slight_smile: .

Thanks all.

3 Likes
22

I do not usually recommend writing directly to SQL. But let’s say you want to write to another database, your own SQL tables. You can use External BAQ and Invoke the BO to do an Update.

Or here is a Direct SQL Example that I have for executing a sp in SQL. May give you some ideas.

BPM Example

/*
using System;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Net;
*/
 
int   iCustNum   = ttCustomerDocs.First().CustNum;
string  sShipToNum = ttCustomerDocs.First().ShipToNum;
string  sCompany   = ttCustomerDocs.First().Company;
 
SqlCommand _cmd = new SqlCommand();
 
string _sql = @"[Testing].[sp_myspecial_sp]"; 
 
_cmd.CommandTimeout = 300;
_cmd.CommandType = CommandType.StoredProcedure;
_cmd.CommandText = _sql;
 
 
SqlParameter spRC = new SqlParameter("@RC",0);
SqlParameter spCustNum = new SqlParameter("@CustNum", iCustNum);
SqlParameter spShipToNum = new SqlParameter("@ShipToNum", sShipToNum);
SqlParameter spCompany = new SqlParameter("@Company", sCompany);
SqlParameter spReturnValue = new SqlParameter("@RETURN_VALUE", DBNull.Value);
SqlParameter spReturnMessage = new SqlParameter("@RETURN_MESSAGE", DBNull.Value);
 
spRC.SqlDbType = SqlDbType.Int;
spCustNum.SqlDbType = SqlDbType.BigInt;
spShipToNum.SqlDbType = SqlDbType.VarChar;
spCompany.SqlDbType = SqlDbType.VarChar;
spReturnValue.SqlDbType = SqlDbType.BigInt;
spReturnMessage.SqlDbType = SqlDbType.VarChar;
spReturnMessage.Size = 1000;
 
spCustNum.Direction = ParameterDirection.Input;
spShipToNum.Direction = ParameterDirection.Input;
spCompany.Direction = ParameterDirection.Input;
spRC.Direction = ParameterDirection.ReturnValue;
spReturnValue.Direction = ParameterDirection.InputOutput;
spReturnValue.Direction = ParameterDirection.InputOutput;
 
_cmd.Parameters.Add(spRC);
_cmd.Parameters.Add(spCustNum);
_cmd.Parameters.Add(spShipToNum);
_cmd.Parameters.Add(spCompany);
_cmd.Parameters.Add(spReturnValue);
_cmd.Parameters.Add(spReturnMessage);
 
_cmd.Connection =   Db.SqlConnection;
_cmd.ExecuteNonQuery();
5 Likes
23

Very elegant and thorough. Thanks @hkeric.wci !

24

I just wanted to give kudos to @josecgomez . I remembered his reply above that showed an example of a correctly written insert from a BPM and even though I didn’t use it back then, I recalled replying a little further down that someone would be able to use his example for good. I found a use case for it today a little over a year later, so the someone was me…your answers are still timeless. Thanks again.

5 Likes