Kinetic 2022.1 C# BPM SQL Call Stored Proc

We have a number of BPMs that have code which rely on System.Data.SqlClient to run stored procedures and work fine in 10.2.300.X. We are finding that upon upgrading to Kinetic 2022.1 these BPMs fail to compile. Upon adding the existing System.Data.SqlClient as an additional reference, we receive compilation error CS0731 - The Type forwarder for type ‘System.Data.SqlClient.SqlConnection’ in assembly ‘System.Data.SqlClient’ causes a cycle. Does anyone have any suggestions?

Don’t update the database directly? :person_shrugging:

Seriously, while using Sprocs is a known thing to your team, for this very reason you’re better off working within the Epicor framework. What do these SProcs do?

2 Likes

I understand that would be bad practice to used stored procedures to read or write Epicor data directly. The main reason we are doing this is that we call procedures in external databases which are used to fill tablesets. The tablesets get iterated through to call business objects in Epicor to populate necessary information (ie, QuoteHeader, QuoteLines, QuoteAsm, QuoteOpr, QuoteMtls). FWIW we’ve also used it to speed up queries which require more than a simple TVF in Epicor but we would never write directly to that dataset

You didn’t have functions in 10.2.300 but you will in K2021 (10.2.500+). Not sure what you have in your external databases, but if you make an API to pull that information into your BPM, you can continue that general idea in a function without the dependency of System.Data.SqlClient.

We tried this too and the stored procedure ran before Epicor flushed the application cache. This was a label printing function and it would be missing data the first time it was called and have it the second time. Yes, those SP queries are fast but in our case, too fast. Bart warned us about caching and reading directly from the database. Now I know why.

2 Likes

So @Mark_Wonsil , for a simple BPM that does some calculations and stores the results in an external DB for a legacy system to consume, are you able to point me to tools that might work?

I haven’t found a way to keep using my System.Data.SQLClient. I’d happily build an efx function, but as far as I can tell it doesn’t have a service that accesses external DB either.

Maybe an updateable external BAQ combined with a dynamic query GetService? Seems like a convoluted chain to build, as would be a REST client pointing at said EBAQ.

Does MSSQL have an API?

Or @tanner - did you come up with anything?

I get all the reasons for not making this available, but it still seems like the alternatives are way more complicated.

@SteveFossey We ended up creating a simple .NET REST service that takes generic SQL as the payload to execute and created an Epicor function to call the service. The returned JSON object is then parsed into a generic DataTable[]. In my opinion, it is not ideal but once we got it working, the pattern is actually fairly painless. I am not convinced this is the simplest way to transition but this worked for us. Hope that helps and please let us know if you find a simpler way

1 Like

new guy question - is this something in Epicor? Or in Windows? Would you mind giving me a few threads to tug on about this?

For the Rest function library:
Custom Library for sending REST calls out of Epicor - Code Review / Sharing - Epicor User Help Forum (epiusers.help)

To develop REST endpoint:
We cannabalized an older ASP.NET ApiController project example
Build RESTful APIs with ASP.NET Web API - ASP.NET 4.x | Microsoft DocsT
You may want to consider using newer technology
Tutorial: Create a web API with ASP.NET Core | Microsoft Docs

Hope this helps

5 Likes

Nice resources.

ah yes, ok, the endpoint is the piece I was missing. Was hoping you’d say, what? you didn’t know (Epicor tool abc / SQL Server API / magic Windows squirrel / etc) would do that!?

Ok yes, I’m familiar with RestSharp and will have a go at the end point. Thanks!

(Dude asks Wonsil for his opinion about integrations. :person_facepalming: Get comfortable…)

In a recent Twitter conversation, there was a great discussion about the term “seamless integration.” Since my days in EDI Integration, I have come to loathe the term, because it’s not only bull :poop: but it’s not what we really want, IMHO. We want well-defined seams that are easy to observe.

Epicor’s Multi-Company direct connect service integration is seamless. We have no idea what it does and it is very difficult to debug when something goes wrong. There was a recent post about an SMTP error that blew up a BPM. It’s seamless, and now it’s going to take extra time to debug it. :person_shrugging:

As with most quality programming, we try to break things up into processes that do one thing well and pass them on to the next process that does one thing well, etc. This was the main thrust of Unix and we keep learning this lesson again, and again.

So I try to do integrations this way too. Split the process up into well-defined pieces:

  • Determine the payload
  • Transfer the payload
  • Apply the payload (update/add/delete)

In your case, I would try a little event-sourcing. Write a reference to a queue of what needs to be sent. The queue would contain what I want to send (order number, packslip, etc.), a status (like “NEW”), and a timestamp. When it’s time to transfer the payload, you select the records you need from the queue and format the payload. I personally prefer pull systems, so another process would call an Epicor Function to transfer the payload at that time. The status of the queue is updated to “SENT”. The pulling program would update your external database or you could add it to its own input queue. If you’re really cool, you can update the Epicor queue status to “PROCESSED” along with the timestamp when the update is successful.

Yes, this is more work. The benefits over in-process integrations are:

  • If a document is redone, the customer or other receiving system, is not getting multiple notifications if the changes happen before the transmission.
  • Transmission can be delayed and batched or sent immediately.
  • If there is an error in sending, it’s easy to resend.
  • A simple aged queue query can notify others if processing is not happening.
  • If the receiving system is down, then the document will go with the next attempt.
  • If you have to rebuild the receiving system, you could resend all documents from the restore point by changing the status back to "NEW.’

I would do something similar with inbound data to Epicor. Receive the document to a UD table and use a status field. Validate the data as much as possible. Process the validated data. And so on…

4 Likes

all in all, you’re right; but regardless, going from easily using an external DB with a dozen lines of code to having to create an API and then consume it is definitely problematic.

I realize the OP found a resolution, but does anyone know why System.Data.SQLClient stopped working, and whether it can be re-enabled or replaced with another reference?

1 Like

I would still go @tanner’s route and write a dozen line code program that runs a BAQ in the REST Client and use SQLClient to update the external db. It won’t break during upgrades either. Just a thought. You know, change the things you can, accept the ones you can’t, …

cruel and unusual punishment, but ok.

so how do you secure something like this? To me, having no development organization outside of epicor, if we create little softwares with no security and no control, then if developers like me get underbussed the company could be screwed.

I don’t suggest things that can’t be secured! :rofl:

The REST call still requires credentials and in Epicor you can use Scope to limit that user account to be able to call only one function from one library. As for your external database, it would be the same as you were doing before in your BPM.

Did you try creating an external DLL? I’m pretty sure it’s just a bug with the available bpm code editor and the available libraries being referenced

Oh good, so this IS Mark :smile: and your account hasn’t been hacked…

Ok, once again wearing my ignorance on my sleeve. I had a single code widget using System.Data.SQLClient to post 5 fields to a non-Epicor database.

Are you guys saying to create an external DLL that does the same thing, and then call that from my code widget? Or create an API endpoint on the SQL Server machine, and then use (for example) RESTSharp to call that endpoint?

In the former case, I can figure out how but have just finished eliminating externals; and in the latter case, I’m a bit lost - it sounds like everything is outside of Epicor, and my SQL Server machine would have to be exposed to the web.

Many thanks for the time you’ve already put in… but could you explain it like I’m five?

Right - at this point it looks like an external DLL is the way to go, and I’ll leave my SQL Server alone

Sorry guys - I’m investing in better training but the upgrade deadline is sooner than the training is!

Hi Steve,
Does this approach not work? Note this example gets its connection string from the External DataSource table but the concept applies and its working for me in 2022.1

string conn = "";

// The connection string is encrypted at Rest so we need to decrypt it before we can use it.
using(System.Security.Cryptography.SymmetricAlgorithm symmetricAlgorithm = Epicor.Security.Cryptography.SymmetricAlgorithmFactory.Create(Epicor.Security.Cryptography.SymmetricCryptoAlgorithm.Aes)) {

    var extConnStr = (from i in Db.BAQExtDatasource where i.DatasourceName == "Azure-Replication"
    select i.ConnectionString).FirstOrDefault();

    conn = Ice.Lib.Bpm.Helpers.TextEncryptionHelper.Decrypt(Convert.FromBase64String(extConnStr), symmetricAlgorithm); //Decrypt Connection String.
}

// Run the SQL Update.
using(SqlConnection sqlConn = new SqlConnection(conn)) 
{
  sqlConn.Open();
  using(SqlCommand _cmd = sqlConn.CreateCommand()) 
  {
    
      foreach(var reslt in this.queryResultDatasetHolder.Original.Results.Where(r => r.Updated())) 
      {
        try
        {
          _cmd.CommandText = $"Update dbo.OrderHed Set PONum = {reslt.OrderHed_PONum} where SysRowID ='{reslt.OrderHed_SysRowID}'";
          _cmd.ExecuteNonQuery();
        }
        catch(Exception ex)
        {
          reslt.Calculated_ConnString = ex.Message;
        }
      }
   
  }
  sqlConn.Close();
}

what is SQLConnection and SQLCommand a member of?

Aside from getting the string from the datasource (thanks for that!), I’m doing approximately what you describe in 10.2.700 but in 2022.1 it’s giving the errors mentioned by the OP. I’m not at my pc but I think the objects are the same.

So nope, can confirm that I cannot access these methods and properties, and not only that, I can’t seem to get DLL methods to show up in the Invoke External Method widget. Including where such are currently working in 10.2.700.

@tanner sorry to hijack your solved thread, I’ll review next week and start a fresh one.