I maintain a SQL error log table for all my .Net apps that communicate with Epicor. Lately there have been instances where it would have been handy to write to the Errorlog from an Epicor directive. I considered using a UD table, but why reinvent the wheel? Does anyone have any code to write direct to a SQL table from within a directive?
As an example, I would like to write to the table when an error is presented to the user. Below is the code I would use if I were to write to a UD table, I am looking to replace the Ice.UD40 with my table name (MyErrorLog) and the Db.SqlConnection with something pointing to a SQL database(MyDatabase) table(MyErrorLog).
catch (Exception ex)
{
string msg = string.Format("Error during {0} step of {1}.\r\nError Message: {2}.", m_StepName, m_BPMName, ex.Message);
this.PublishInfoMessage(msg,Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual,"","");
string query = "INSERT INTO Ice.UD40 (Company, Key1, Shortchar01) VALUES ('MyCompany','NextIndex',msg)";
SqlCommand cmd = new SqlCommand(query, Db.SQLConnection);
cmd.ExecuteNonQuery();
}
The UD Tables have their own Adapter/ZBOs to write records. There are various posts here about why writing directly to the database is not a best practice.
Indeed! Hereās how some people have written to the EventLog:
Thanks Mark. Sure, I understand best practice and wouldnāt write to a table that would be used for anything within Epicor but since this is simply for display/research, that shouldnāt apply here. I already have the code to write to a UD table, and appreciate the link to the NuGet logging framework (which I have used before ). I can go with either of those if it is all that is available, but I was just hoping to get an all-in-one solution so I donāt have to go to several different sources for information if I donāt have to. I would think it should be doable.
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 ).
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);
}
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.
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.
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
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.
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.
). I can go with either of those if it is all that is available, but I was just hoping to get an all-in-one solution so I donāt have to go to several different sources for information if I donāt have to. I would think it should be doable.
. 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ā¦
as it was not my intention.