Configurator woes

Epicor ERP 10
1

We’re in the middle of transitioning from E10.1 to 10.2.

We have a configurator that works perfectly in 10.1.600, but when we run the same config in 10.2.600 we get an error with SQL not disposing correctly. (image below)

The configurator looks up some product specs from outside of Epicor, but I’m not certain this is the source of our error. Code used to connect to this data is in a UDMethod that looks like this:

string sReturn = "";

using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(cs))
{
    try
    {
        using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sSQL, conn))
        try
        {
            cmd.Connection.Open();
            sReturn = cmd.ExecuteScalar().ToString();
        }
        catch (Exception ex1)
        {
            sReturn = ex1.ToString();
        }
		finally
		{
		    cmd.Dispose();
		}
    }
    catch (Exception ex)
    {
        sReturn = ex.ToString();//debug - show error
    }
	finally
	{
		conn.Dispose();
	}
}
return sReturn;

Parameters:
cs is the string connection string
sSQL is the string select sql

I’m getting the product specs on the config form so I know my connection is working, but when I complete my config I get the following:

image
image581×529 36.9 KB

It looks to be tripping up when it starts to perform KeepWhen evaluation but I’m not sure where. Is there a way I can debug this more specifically? I have no idea where this is failing and hence no idea how to fix it.

Thanks in advance.

2

Anyone have any ideas how I could debug this one? I don’t know a way to find the exact point this configurator is falling over.

My assumption is this:
There are multiple places in the inputs where I call data via SQL, LINQ and PCLookup functions. All of them bring the data in correctly and allow the configurator to complete. One of these is not disposing correctly though.
When the configurator completes and runs through configurator rules, it falls over at the first GetPartDefault that is called with the above errors about not being able to establish a DB connection because a prior connection wasn’t disposed.

I’m at a real loss about how to find the point where a connection is not disposed, as the code breaks at a different point. Do any of you gurus know how I can trace this error?

Thanks!

3

It looks like you are doing some form of Sql Injection… I know that Epicor Development has been locking down this “feature” because of strong security reasons. I fully doubt that you will be able to do SQL injection anywhere in the future.
What might be happening here is that on the client side, you are “getting away” with it… but in method rules, the server has locked it down, and not allowing the Sql command to run.
It is best to call business objects instead of doing direct Sql commands.

1 Like
4

image

1 Like
5

Thanks for the reply Tim.

The only reason for the SQL is that I’m reading data from a program outside Epicor. Is there a method of doing this that isn’t frowned upon like my direct “SQL injection”? This SQL only uses select commands - I’m not writing to these tables at all.

All calls made to Epicor data passes through either LINQ or PCLookup commands (though I’m phasing out PCLookup in favour of UD tables). Will LINQ give me headaches in the future, and is there a better way to handle internal lookups within the configurator?

Thanks for your input.

6

Someone who knows more about SQL may be able to answer this better… but as I understand it, direct access to the DB is going to get harder, not easier. That said, I believe that LINQ will still be allowed in the future. There has also been some talk about future configurator versions being able to call Epicor Functions (but this is NOT there yet!).
If trying to access a DB outside of Epicor, it may be best to create your own (Rest) service that you could call… that service would then do the reading of the other DB and return the data.

2 Likes
7

If you can migrate relevant data outside of this separate database and store it in Lookup tables and/or UD tables within your Epicor database, it’s worth doing so. Then you can drop the SQL shenanigans and just use LINQ to build queries / pull your data into Configurator. That said, I don’t think what you’re doing now is absurdly egregious since it is a separate database, it’s just not a good long-term maintainable solution.

If the outside data is static, then migrating it to Epicor db is a no brainer. If it’s a “live” db I’d explore possible integration options for pushing the data to Epicor lookup/UD tables. No integration is easy, but unless you plan to stay On Prem forever you’ll need to do something eventually.

But if you absolutely must keep the data in a separate database, I wonder if you can rig an External BAQ to work from code? Since you can reference assemblies in UD methods it might be possible, unless Epicor is doing something weird with External BAQs. I bet @josecgomez would know.

If it turns out ExtBAQs don’t work then you might be stuck with getting your “direct SQL connection” to work, for now. (I am calling this “direct SQL” instead of “SQL Injection”, as injection has a specific meaning that is not really this).

You said you weren’t sure if the code you posted is the source of the error, so first thing to do is definitively prove which UD method / code chunk is causing the error. Commenting stuff out one by one is probably your best shot. My guess is that you will find an instance of your “direct SQL” to be the cause, but I can’t think of a mechanism in which upgrading Epicor would break that (maybe the .Net libraries are different and System.Data.SqlClient behaves differently??)…

Anyways, this whole post caught my eye because I had to touch a separate db from a customization a while back and I remember struggling with closing/disposing the connection. I couldn’t wrap my head around exactly what Using does in c# in terms of “disposing stuff”… In the end I got it to work but I doubt I did it “right”. In fact it probably isn’t right. But if you get desperate and are ready to try anything to get your code working, the syntax below might give you a few ideas to try:

try
{
    using(SqlConnection connection = new SqlConnection(connString))
    {       
        connection.Open();
        try
        {
            using(SqlCommand command = new SqlCommand("redacted", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.ExecuteNonQuery();                      
            }   
        }
        catch
        {
            throw;
        }
        finally
        {
            connection.Close();
        }
    }
MessageBox.Show("redacted");
}
catch (Exception ex)
{
    ExceptionBox.Show(ex);
}
1 Like
8

Thanks @timshuwy and @TomAlexander

I think you hit it on the head Tim. It looks like I can still grab the data from the client but the security has been tightened server-side. I’ve used the same code in the config designer to populate a combobox which works fine, but falls over when I try to use it from the Method Rules.

Tom, I’d been down the external BAQ path but as my data is reasonable static I’ve started down the arduous path of converting to a UD Table. I think this will be safest in the long run. I’ve converted most of my UD Methods to Linq now and they seem to be working fine. I still have to track down all of the Rules, but I’m underway and it seems to be working now.

Thanks for your input and ideas.