Dynamics Community Forum Thread Details

Hi,

We have been using GpConnNet.dll's Connect() service for a few years now to get connections to the Dynamics GP database. In fact, we have numerous C# addins that access the database.

If you look at the example code provided by MS support when you get your GpConnNet keys, there is A LOT of boilerplate code every time you use a connection. For example, you have to create the underlying SqlConnection (or OdbcConnection), call GPConnection.Startup(), create the GPConnection object that wraps the SqlConnection, call GpConnection.Init(), call GpConnection.Connect(), check the return codes, create the SqlCommand object, set the Command text, call SqlCommand.ExecuteReader(), close the underyling SqlConnection, set the GPConnection reference to null and finally call GPConnection.Shutdown. Whew! That's a lot of code to repeat everytime you use a connection!

To simplify data access, any sane developer should use an ADO data access library that does all the boiler plate code for you so all you need to do is set the CommandText and read the result set, using a callback idiom. For example, if you have used the data access libraries in the SpringFramework.NET, you'll know what I'm talking about. Repeating that boilerplate code in every single data access method is just plain stupid and will lead to bugs, extra maintenance and slow development times.

What I have done to MASSIVELY simplify data access in our application is to create a DbProvider class that is essentially a database connection factory that hides the complexity of all the boiler plate code required for using GpConnNet.Connect(). I call my class GpDbProvider and it extends the SpringFramework.NET's DelegatingDbProvider class. It does all of the work of calling GPConnection.Startup(), GPConnection.Init(), GPConnection.Shutdown, etc. The most important method is my CreateConnection() method, which calls GPConnection.Connect() and returns the underlying SqlConnection as an IDbConnection. My DbProvider class plugs right into SpringFramework.NET's data access library, allowing me to simply write SQL statements and read the result sets without having to worry about tedious boilerplate code.

Now, here is the main reason for my forum post. GpConnNet.dll's Connect() method tries to do two things at once (combining of concerns) instead of doing one thing at a time (separation of concerns). Specifically, it 1) creates a connection with the encrypted password AND 2) opens the connection. In my opinion, GpConnNet should be only a connection factory that encrypts the plain text password and creates an unopened connection.

It should be the caller's responsibility to open the connection. This is because most ADO libraries for simplifying data access (e.g. SpringFramework.NET) expect that a newly created connection is closed when it receives a connection from a connection factory. Unfortunately, because GpConnNet's connect() service combines these two concerns, I'm forced to call connection.close() immediately after calling GpConnNet's connect() service, before I pass on the connection to the ADO libraries that expect a closed connection. If I don't call close(), then when the ADO libraries try to open the connection, I get "System.InvalidOperationException : The connection was not closed. The connection's current state is open."

This "open the connection twice" workaround is inefficient (it takes time to open connections).

GpConnNet should have a new service that creates a connection WITHOUT opening it. Alternatively, it should have a new service that takes the unencrypted password as a parameter and then returns the encrypted password.

Interestingly, in older versions of GpConnNet (e.g. the one that came with Dynamics GP9), I could add "Persist Security Info = true" to the connection string before calling GpConnNet.connection() and then I could read the encrypted password from the connection string after calling GpConnNet.connection(). This saved me having to open connections twice. Sadly, newer versions of GpConnNet (e.g. the one that came with Dynamics GP 2010) remove "Persist Security Info = true" from the connection string before creating the connection. Thus, I have no way of getting the encrypted password so that I can avoid doing the "open connection twice" workaround.

So, how about it Microsoft? Will you make some improvements to GpConnNet.dll so we don't have to repeat boiler plate code or use the "open the connetion twice" workaround?

Please separate the concerns in GpConnNet.dll's Connect() service

Helpful resources

News and Announcements

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates