Jose, isn’t there a way you can write to an ice.Log (app server log) when there is an error?
I feel like I have seen code of @hkeric.wci that has lines in it to write to app server logs.
1 Like
Yeah
This
Ice.Diagnostics.Log.WriteEntry($"[ Updating FirmRelease ] OrderNum: {row.OrderNum} - OrderLine: {row.OrderLine} - OrderRel: {row.OrderRelNum}");
Writes to the AppSarver’s Envent Log
And this
Action<string> WT = (msg) =>
{
Epicor.Hosting.Trace.ServerLog.WriteTraceMessage(
"trace://ice/custom/MyProject",
"MyAwesomeBPM",
() => msg
);
}
};
Will write tot eh ServerLog (when enabled via trace flag)
3 Likes
Thanks Jose!
Jim, not sure if this helps at all since this will be combined in the same event logs for everything else, but these are two other ways to log events.
I feel like if you are creating your own .Net Apps you could just write to a SQL table of your choosing- not an Epicor SQL table…
This was kind of my point. It’s funny because I came into this knowing I would get resistance to writing to SQL, and knowing the “cardinal sin” nature of that statement, I added the usage case for context so it would be obvious. What I hadn’t thought of was just relaying my usage case would send everyone down the “here is what/how you should write/use to log errors” route 
. I have been coding .net apps to communicate with Epicor since I started working with Epicor in 2009. Someday I may transfer some of those thousands of lines of code appropriately to Rest, but for now it is what it is. I know the reasons you don’t write to SQL, and they just don’t apply here. This is not information that will be transferred back into Epicor, nor is it needed in or to be visible from within Epicor. I already have a SQL table I use for reporting and rather than writing to another table needing to be joined later, it would be much simpler to write to what I have already designed and built a reporting system around (hence the “reinventing the wheel” reference). I was hoping that someone who has done something similar could relay what they did (@josecgomez ?) and not feel like they were condoning writing directly to SQL without a specific reason (like this one ).
Either way, as always, thanks for the responses.
2 Likes
I won’t judge or pressure you to do something else, but in your first post it sounds like you already have the code you need for your approach??
Are you just missing how to replace the “Db.SQLConnection”?
It should be as simple as something like this (but maybe add some try and/or usings and close the connection to make it cleaner)
string connString;
connString = "Data Source=" + yourSQLServer + ";Initial Catalog=" + yourDatabase + ";User ID=" + SQLUserID + ";Password=" + SQLPassword; //or use integrated security
SqlConnection connection = new SqlConnection(connString)
connection.Open();
SqlCommand cmd = new SqlCommand(query, connection);
cmd.ExecuteNonQuery();
1 Like
All right here’s a way to do that same thing without breaking any rules in your BPM replace that INSERT Statement with something like this (not suggesting you should do that you’ve made your position clear, just adding this as a reference for the next guy to do it “RIGHT” ← notice the quotes not ragging on ya 
using(var ud01svc = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.UD01SvcContract>(Db))
{
var ud01NewDS = new UD01Tableset();
ud01svc.GetaNewUD01(ref ud01NewDS);
var newUD01Row = ud01NewDS.UD01.Where(r=>r.Added()).FirstOrDefault();
newUD01Row .Key1= "NextIndex";
newUD01Row .Shortchar01 = msg;
ud01svc.Update(ref ud01NewDS);
}
Same exact outcome, but coloring within the lines 
3 Likes
Well, heck. Simple .Net code at the end of all this. You are correct in knowing what I am looking for. Thanks Tom. I will give this a shot.
1 Like
ha I misunderstood your original question, I think because the code you provided made it look like you were inserting into an Epicor table and I did my usual skimming and missed the meat.
https://thumbs.gfycat.com/BaggyUnselfishAmurstarfish-size_restricted.gif
Sorry about that. Tom has given you an answer that works to write to a custom table
Hey @Banderson feel free to make a TLDR; badge for me… tis getting BAD!
1 Like
Thank you Jose. I can always count on your consistency. I have ruled out the UD table, but as always, you are the g.o.a.t. and I am sure someone will be able to use your example for good.
2 Likes
Yep, as soon as I saw the title I knew this would be an interesting thread…
Agreed… I probably picked that code up from @josecgomez at some point. I should really start sending him and @hkeric.wci royalty checks at this point, half my income is probably from adapting their code 
5 Likes
Agreed…Agreed…No doubt, same here.
Hey @jimf,
My apologies. I don’t mean to sound like a preachy 
. I think I’m a little sensitive because the last few jobs I’ve come into places where people “rolled their own” INSERT-NAME-HERE systems. In the last two cases, there were security “incidents” and we had to learn about the non-standard systems in the middle of an emergency situation. Not fun. So whenever I hear about a roll-your-own solution, I get a little…
In one security incident, we had an ASP.NET intranet with similar code as above. Active Directory had to be completely rebuilt, SQL servers got new names, etc. We spent days finding and replacing code like that above. In your case, the logging isn’t important and wouldn’t have to be recovered. But for important logging (for audit and compliance reasons), one really wants to do it to an immutable target so it survives database corruption or deletion.
There are no cardinal sins here. There are only choices based on business requirements. I often feel compelled to respond for the others who will read this thread later and not necessarily to the author.
Also, I’m giving a talk about Zero Trust Security this year at Insights and have been listening to too damn many security podcasts that are making me very, very paranoid these days. So again, sorry if I sounded like a 
as it was not my intention.
3 Likes
For the record, I 100% agree with this…
A slight improvement to my suggestion would be to pull those server/db names and creds from something like a User Code so it’s at least not 100% hardcoded in the BPM.
Epicor already has an external data source where you can store creds and retrieve them when needed.
3 Likes
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 
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 .
Thanks all.
3 Likes
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
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