Performing Direct SQL Transactions Within Epicor BPMs

Let’s get this out of the way up front - doing any sort of direct SQL transactions to your Epicor tables is generally frowned upon and you run a chance of voiding your product warranty. Especially for things consequential for accounting, DO NOT DO DIRECT DATABASE UPDATES. But, that said, there may be legitimate times when a direct database update within a BPM is alright. Perhaps it is a custom table you have created or some UD field. The code within your BPM is really quite simple:

var query = "UPDATE MY_CUSTOM_TABLE SET NAME = 'ADAM' WHERE ID = 100";
SqlCommand command = new SqlCommand(query, Db.SqlConnection);
command.ExecuteNonQuery();

So that first line we are defining the query we wish to run. The second line we are instantiating a SqlCommand using the database connection already defined as a property of the ‘Db’ object. Then the last line we simply execute that command.

Want to do this same sort of direct SQL update from a customization? Consider creating an updatable BAQ with a parameter for the statement you wish to execute. Then override the GetList method to take that parameter and pass it into this structure!

---