Write to SQL table from within BPM/Directive

,

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();
}

Hey Jim!

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:

BPM error checking popup usings & references help - ERP 10 - Epicor User Help Forum (epiusers.help)

Also, .NET has a pretty feature complete logging system.

And there is an ecosystem that adds automation to the logging so certain events can ping you or launch a fix program. Food for thought.

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 :slight_smile: ). 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.

Jim,
In the VERY least use Db Context to insert into the UD table, not straight SQL :nauseated_face:

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 :slight_smile: . 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 :slight_smile: ).

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 :wink:

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 :stuck_out_tongue:

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 :slight_smile: 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. :slight_smile:

2 Likes

Yep, as soon as I saw the title I knew this would be an interesting threadā€¦ :slight_smile:

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 :rofl:

5 Likes

Agreedā€¦Agreedā€¦No doubt, same here. :slight_smile:

Hey @jimf,

My apologies. I donā€™t mean to sound like a preachy :peach:. 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. :rofl: So again, sorry if I sounded like a :eggplant: 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