Tuesday, December 16, 2014

C# vs. VB for Dynamics GP .NET Development

By Steve Endow

At the reIMAGINE 2014 conference, Andrew Dean and I gave a presentation on VS Tools and .NET development for Dynamics GP.

At the beginning of the session, we asked the crowd several questions to get an idea of who were consultants vs. developers, who used Dex and/or .NET, and to assess experience, etc.

One question I asked out of curiosity was, for the .NET developers, who used C#, and who used VB?

A vast majority of the room raised their hands to indicate that they used C#.  It seemed that almost all of the attendees had previously indicated that they developed with Dex, so it surprised me to see that nearly all of them also developed in C#.

When I asked about VB, I believe only 3 people indicated that they were solely VB developers.  This also surprised me.  I have known several people who were VB-only developers, so I expected more of a mix, but based on our very informal survey, perhaps 95% of the Dynamics GP .NET developers focused on C#.


Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

Buttons mysteriously resize on VS Tools Dynamics GP Forms -- VST template bug?

By Steve Endow

Sometimes strange things happen when you are developing software that can be quite puzzling.  A window may behave oddly, data may get updated in an unexpected way, or an application may crash.  I tell myself that there is always an explanation, but sometimes these things may seem nearly impossible to figure out.

Last year I developed a VS Tools AddIn in Visual Studio 2010 that worked just fine.  I later added a button to a "Dynamics GP Form" in that AddIn, and it seemed to work fine.  But after some testing, the client noted that the text on one of the buttons was cut off, and asked me to make the button wider.  I thought I had already resized that button, but when I checked it in Visual Studio, sure enough, the button wasn't wide enough.

I made the button wider, confirmed that the button text displayed correctly, and sent the customer a new version of the AddIn DLL.  A few days later, they said the button text was still being cut off.  When I opened Visual Studio, the button size had changed and was again cutting off the text.  What in the world?

After trying various things, I discovered that whenever I recompiled the VST project, two buttons on the form would resize to the default button width.  One button was wider and would shrink.  The second button was narrower, and it would get wider.  No matter what I tried, the buttons would resize after being recompiled.

I finally gave up and added some run time code that would resize and reposition the buttons.  That worked, but clearly there was some underlying issue.

Fast forward to December 2014, and I'm now seeing the same symptoms in a brand new AddIn project using Visual Studio 2013.

This is what the form looked like when I originally created it.

After doing some testing, I noticed that the button had resized and looked like this.

The first time I thought I had just forgotten to resize the button, but the second time I realized that I wasn't imagining things.  The button size was definitely changing.

But with this new project in VS 2013, the button was not resizing when I recompiled, like what I saw with VS 2010.

After trying a few things, I discovered that when I closed Visual Studio 2013 and reopened the solution, the button would resize.

While this may seem trivial, try designing a complex window with a dozen buttons that all resize every time you reopen your project.  It's a nightmare.

My only guess is that this is some type of obscure bug in the Dynamics GP VS Tools form templates, as I have only seen this issue with the Dynamics GP Form template windows.

Since I have observed that the Dynamics GP Form template has a bug with the window startup position property, I'm assuming that this is another small, but highly annoying bug in the form template.

What I need to try next is working with this project on a different server to see if it might be some problem with this development server.

Has anyone else experienced this issue?

UPDATE:  I setup VS 2013 on a separate server and am able to reproduce the issue.  I'll size the buttons, save the project, then close Visual Studio.  When I open the project and view the form, the buttons are resized.

After further testing, I found that if I just close the form designer window and then reopen it, the buttons resize.

And this issue only occurs with the Dynamics GP Form objects.  If I add a standard Windows Form to the AddIn, the buttons do not resize.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

Friday, December 12, 2014

Executing SQL DDL scripts from a Dynamics GP VS Tools AddIn

By Steve Endow

I've developed many Dynamics GP Visual Studio Tools AddIns that depended on one or more SQL scripts.

Simple single-line SQL statements are easy to include in your code, but what about more complex queries?  If I have large queries that are more than a few lines, I typically try and push those out to stored procedures.  Stored procs are easier to maintain outside of code, and calling a stored proc from a VSTools AddIn is cleaner and simpler than maintaining a large query in Visual Studio.

Okay, so that may be fairly obvious, but how do you deploy those stored procedures?  Previously, I've included them as SQL scripts that had to be manually executed as part of the deployment process.  While this was simple and low tech, it is easy to forget a script or two if you have a complex deployment.

And what about Data Definition Language (DDL) scripts?  What if you need a custom table for your customization or integration, or several?  What if you don't want to have the client manually run 10 SQL scripts as part of your AddIn deployment?  And what if your AddIn needs those tables and objects to be setup every Dynamics GP company database?  And what if the client has 80 company databases?  Not a fun deployment.

After all, the great thing about AddIns is that the ideal deployment can be as simple as copying a single DLL file to the GP AddIns subdirectory.  If you have to manually run SQL scripts you start to lose that benefit.

This week I am working on a new VS Tools AddIn that will have at least 2 custom tables.  I was tired of dealing with SQL scripts as part of my deployment, so I was determined to fully automate all SQL scripts.  I want my AddIn DLL to automatically detect whether the required stored procedures and tables are present, and if not, automatically create them.  I've done this once in the past, but it was messy and a nightmare to maintain.  I wanted a better, cleaner method that was easy to maintain.  Fortunately, I found one.

I first wrote this simple method to check if a table exists:

public bool PSV10000Exists()
    //Check if table exists
    SqlParameter[] sqlParameters = null;

    string result = DataAccess.ExecuteScalar(CommandType.Text, commandText, sqlParameters);

    if (result == "1")
        return true;
        return false;

You could abstract this to be "TableExists" and have the method accept a database name and table name as parameters, but this was my prototype.

Great, so now you know whether the table exists.  What if it doesn't?

Well, then you just execute the DDL script to create the table, right?

Nope.  Not that easy.

ADO.NET is great for straight queries or individual / discrete DDL commands, but if you have a complex statement with several "GO" commands included, it can't parse the command and will throw an error.

My table creation script has several commands, each followed by a GO command.

ALTER TABLE ADD  CONSTRAINT  (several of these)

So how do you run a multi-step script with numerous GO commands?

It turns out that there is a different "ExecuteNonQuery" command in .NET, tucked away in the SQL Server Management (SMO) assemblies.  SMO allows you to execute complex multi-step SQL scripts, including DDL scripts with multiple GO commands.

To use SMO, you first need to add four SMO references to your project.  The DLLs are available at:

C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\

And you'll need to add these references:


Once you have those references setup, you'll need to add these statements to your Data Access class.

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

With that taken care of, you can create a simple method to call the SMO version of ExecuteNonQuery.  Here is mine, which relies on the Connection property of my DataAccess class to get a connection to the GP SQL server via GPConnNet.

public static int ExecuteDDLScript(string commandText) 
    SqlConnection gpConn = new SqlConnection();
        gpConn = Connection;  //Get connection from GPConnNet
        Server server = new Server(new ServerConnection(gpConn));
        int result = server.ConnectionContext.ExecuteNonQuery(commandText);
        return result;
    catch (Exception ex)
        throw ex;

So now you have all of the plumbing to execute a DDL script.  Now what?

Let's start by getting our DDL script into Visual Studio so that our code can access it.  But where do you store a 20, 30, 50 line SQL script?

I am a huge fan of the Visual Studio Resources feature, which is a fantastic place for storing and managing large SQL scripts.

You define a resource name, paste in your entire SQL script, and add some comments if desired.  I like to note the version number of the scripts, as I often have to refine them during development.

Once you have your Resources setup, you can then put it all together in a simple method that actually executes the script.

I decided to create a new SQLObjects class, separate from my DataAccess class.

In my SQLObjects class, I created this new method.

public bool CreatePSV10000()
    string commandText = Properties.Resources.sqlCreatePSV10000;
    int result = DataAccess.ExecuteDDLScript(commandText);

    if (result == -13)
        return true;
        return false;

Look at how simple that is!  It's essentially two lines to create your table if it doesn't exist.

No manual SQL scripts required, and your AddIn can do all of the work automatically, greatly simplifying your deployment.

You may have noticed the -13 value, which is a bit odd.  In my testing, that appears to be the integer returned by the SMO ExecuteNonQuery method when the script is run successfully.  I haven't yet been able to find a list of the return codes for the SMO ExecuteNonQuery method, so I'm still researching to see if -13 is an unconditional success response, and what other values I need to look out for.

Now that I have this method setup, I look forward to never having to include SQL scripts in my deployment guides again!

UPDATE:  I looked again at the documentation for the SMO ExecuteNonQuery--specifically the ServerConnection.ExecuteNonQuery method.  Regarding the integer return value, it says:

The return value specifies the total number of rows affected by the Transact-SQL command for UPDATE, INSERT, and DELETE statements. For all other types of statements, the return value is -1.
Since I am running a DDL script, the return value should be -1, whereas I was getting -13.  Then I realized that my DDL script consisted of numerous statements separated by the GO command.  Which made me wonder if I happened to have 13 statements in my script.  I did a test where I removed all of the GO commands--my script still worked fine, and the return value was -1.  When I added 4 GO commands, the return value was -4.

So I learned that why I got a return value of -13, and I also happened to learn that all of the GO commands in a DDL script created by SQL Server Management Studio are not required.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

Tuesday, December 9, 2014

Fun with eConnect and .NET decimal rounding

By Steve Endow

There are some situations where you need to import transactions into Dynamics GP, where the source data contains line item totals that need to be broken down before being imported into Dynamics GP.

Since that probably didn't make a whole lot of sense, let me give an example.

One of my clients needs to import SOP Invoices.  Due to the nature of their business, the invoice data to be imported into Dynamics GP consists of item number, quantity, and extended price.  There is no unit price in the data.

For example:

Item:  A100
Quantity: 231
Amount:  $53,981.87

To import the line item using eConnect, you need to calculate the unit price.  If you calculate the unit price for this example, you get $233.69.  Pretty simple, right?

Well, if you verify the unit price:  231 x $233.69 = $53,982.39.  Note that this is $0.52 more than the actual line amount.

Fortunately, and surprisingly, eConnect (and the Dynamics GP client) is okay with this scenario.  You can import the calculated unit price and the extended price, even though there is a discrepancy due to rounding.

Easy peasy lemon squeezy, right?

Well, if your integration was developed with .NET, there is a caveat.  When you perform the calculation to get the unit price, you will need to round the resulting decimal value.  And this can cause a problem.

Let's use a different example for this one.

Item:  A200
Quantity: 8
Amount:  $2,089

So to get unit price:  $2,089 / 8 = $261.125

And then you dutifully round that value to two decimals with this code:

sopLine.UNITPRCE = Math.Round(lineAmount / lineQuantity, 2);

And you get a unit price of:


Say what?

Every kid knows that .125 should round up to .13, right?

So what in the world is going on?

Well, the .NET Math.Round method has two different "rounding conventions", as this article explains.

The default rounding convention is "To Even".  This means that .125 is rounded to the nearest even value, which is .12.  

If you send a quantity of 8, with a unit price of $261.12, and an extended price of $2,089, eConnect will return the following error:

Error Number = 722  Stored Procedure= taSopLineIvcInsert  Error Description = Unit Price calculation does not match out to Extended price

It took me a while to figure this out, but eConnect uses "conventional" rounding, and in this case expects $261.13.

So how do we fix this?  Well, the Math.Round method has a parameter to change the rounding convention.

Math.Round(lineAmount / lineQuantity, 2, MidpointRounding.AwayFromZero);

The MidpointRounding option allows you to tell .NET to use the more conventional rounding method which Microsoft calls "Away From Zero".

This will produce the desired result of a unit price of $261.13 and eliminate the somewhat perplexing eConnect Error Number 722.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

Wednesday, December 3, 2014

Diagnosis: Developer's Amnesia

By Steve Endow

Many years ago I was part of a corporate web development team that maintained the internal and external web sites of a Fortune 1000 corporation.  We did classic ASP web development, with projects like product catalog pages on the external web site, registration web sites for customer conferences, and internal document management and knowledge base sites.

One Monday, one of the business analysts I had been working with stopped by my gray corporate cubicle and asked if I had completed the XYZ web page. (I don't recall what exactly it was)

"The what?", I replied.

"The XYZ web page.", she repeated.

I furrowed my brow, looking at her like she might be talking to the wrong person.

"XYZ?", I asked, clearly puzzled.

"Ya, what you worked on last Friday", she slowly said, picking up that I had no idea what she was talking about.

"When?", I asked, starting to get concerned that one of us might be crazy.

"Friday.  Remember Friday?", she said, starting to get concerned.

"Um...", I said as I paused and thought.  "Um, Friday...ya, I've got nothing" I admitted.

She then proceeded to explain that she had talked to me on Friday afternoon and asked me to make some change to a web page.  It was a last minute thing, and I was the only one around, so we apparently talked about the change that she needed done ASAP.

After hearing the story, I turned to my computer, looked up the web page, checked the code, and sure enough, there were my changes, just as she had requested.  I had apparently completed the change that she requested.

I stared at the code, which I clearly wrote, with zero recollection of making the change and without recognizing the code.  I made the change, but as far as I was concerned on that Monday, I was staring at someone else's code.

Slowly it came back to me.  Friday afternoon, looking to go home, and I had to quickly make a change to a web page.  I then vaguely recalled the conversation with her just over 2 days ago.  I recalled the general request, but had no memory of any of the details.

At the time, I chalked it up to doing the work on a Friday afternoon and then having a great weekend where I didn't think about work for 2 seconds.  (Oh, how I miss those...)

But I now think that scenario is a symptom of what I'll call "Developer's Amnesia".

This came up because I was working on a Dynamics GP integration yesterday.  It was a modification to an existing integration I had developed between a web site and Dynamics GP, and I needed to import an AR cash receipt.

I emailed my colleague on the project, asking what values should be used for the cash receipt type, credit card ID, and checkbook ID.  She replied, "Why don't you just use the same values that you used on the other cash receipt import that we did for the client?".

"Huh?"  I re-read her email, thought about it for a few seconds, and a few details slowly started to slugglishly bounce around my brain.  "Um, ya, sure, that makes sense, forgot about that!" I sheepishly replied.

Then I connected to my development server and pulled up the Visual Studio code for that project.  I checked the date on the files, and saw that I had created the last project 7 weeks ago, and last updated it 5 weeks ago.  Somehow, in those 5 weeks, my memory of the project had been wiped.  Totally wiped.

I definitely remembered the project--the name and what it did in general, but somehow I forgot that it involved an AR cash receipt.  I pulled up the code and reviewed it, and I felt like I was having an out of body experience.

I was looking at the code, but I didn't recognize it.  At all.  Did I write this?

I slowly remembered some of the objects and methods, but it was a general vague recollection.  When I reviewed some specific lines and object properties, I had zero recollection of the details.

The funny part was that the code was beautiful!  "Wow, this guy can code!  Who is this C# genius?"  It has clean objects, clean methods, and an elegant design.  But there was enough complexity that in a few areas it wasn't obvious how some things worked.  "Hmm, this guy needs to comment his code better!" I thought ironically.

Apparently the code was all pretty obvious at the time, but since then, I clearly had been stricken with Developer's Amnesia,   After 30 minutes of working with the code, I'm still piecing it all back together.

I emailed a developer friend, explaining what just happened and asking him if he has ever had such an experience.  He quickly replied that he's had the exact same experience, several times, and that he knows exactly how I feel.  Well, at least that is somewhat comforting, knowing it isn't just me.

I then recalled that conversation I had over 10 years ago with that business analyst, which made me wonder how it was possible that I could forget something, that involved thousands of lines of code, so completely.  10 years ago I was that much younger, so I can't blame age.  And there are tons of useless trivial details that I remember from other projects I worked on years ago, so I am pretty sure I'm not forgetting everything.

My only guess is that I experience Developer's Amnesia when I complete a project quickly and have little or no follow up work.  My theory is that if I only spend several intense days working on something and slam it out, the project never transfers from my short term memory to my longer term memory.  Once I release the project and it is deployed smoothly without any follow up work, I move on to the next project, and my short term memory of that project gets wiped.

For projects that I work on part time over the course of a few weeks, more context develops and there is more time for the project to get transferred to my long term memory.  While I obviously can't remember every detail, I can recall most of the project.  There are long term projects I've worked on where I remember a surprising number of details, to the point where the customer emails me to ask how an integration works, and I explain the details that we discussed 3 or 4 years ago.

Whatever the reason, it seems that there are times when developers just completely forget some project or some code that they've written.  So when you ask a developer a question and they look at you funny and act like they think you're making things up, they might just have Developer's Amnesia.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

Wednesday, November 26, 2014

Dynamics GP JSON Integration Issue: null object property values for JSON parsed by MVC controller

By Steve Endow

I haven't yet seen code samples or API documentation for the new Dynamics GP 2015 Service Based Architecture (SBA), but I have a hunch that this might be relevant when we start working with GP 2015 SBA and use JSON to communicate with the GP SBA interface.

WARNING:  If the title wasn't warning enough, this post is a full-on geek fest, so if you aren't a developer that loves Riddles of the Code, flee now.

I'm currently working on a web service application that allows a Linux web server to integrate to Dynamics GP.  After considering various options, the client and I agreed that the simplest approach would be for me to develop a custom web application that could receive and send JSON.  The Linux developers were very comfortable with this, and after getting some help from an expert with experience developing JSON web apps in Visual Studio using MVC, I was able to create an integration that worked great.

After the release of the initial version of the web integration, I noticed a quirk about how blank JSON values were being received by my code.

If the client sent a JSON parameter value that was blank, that particular property on my object would be null.  So if he did not supply a Address2 value, I would get Customer.Address2 == null.

I thought that it was due to how I was defining my objects in Visual Studio using the simple property definition style, such as:

   public string Address2 { get; set; }

Since I wasn't defining a default value for the property, I thought it made sense that I was getting null, assuming that the .NET MVC JSON parser was skipping the empty JSON parameters.  Seemed to make perfect sense at the time.

I didn't think much about it, and just added some workaround code to check for null values and set them to empty strings.  In hindsight, I now know that was sloppy, but since I didn't realize the underlying problem, it was a simple fix and seemed to worked.

Now, I'm adding some additional functionality to the web application, as the customer wants to have new features, such as the ability to search for GP customers.  This time around, I'm more familiar with web app development and MVC, so I was a little more observant about the null property issue.

While testing my recent enhancements, I once again noticed that when I submitted a request to my web app, blank JSON values were being translated to null property values on my objects.  Still thinking that this was due to my object property definitions, I modified my object to use a "full" property definition, like so:

private string address = string.Empty;
public string Address
    get { return address; }
    set { address = value; }

When I walked through the code in debug mode, what I found surprised me.  Even though my property was initializing properly to an empty string, the MVC JSON parser was subsequently setting the property value to null.

So the null values weren't due to my property definitions!  So why was I getting a null, and how can I fix this?

I didn't even know how to phrase a search, but after a few random search attempts in Google, I found this Stack Overflow thread that appeared to exactly describe my scenario:


It appears that with MVC version 2.0, Microsoft changed the way that JSON is parsed.  Instead of assigning empty strings, the newer MVC versions assign null.  Here is a post that covers some of that history:


Okay, great, so how do I "fix" this "problem"?

The Stack Overflow post has several suggestions, none of which I really understand.  I understand the concept that it changes how the JSON is parsed, but the actual code is currently Greek to me.

I ended up trying the suggestion to create a new class called EmptyStringDataAnnotationsModelMetadataProvider.  I then modify my Global.asax file and add the single line to the Application_Start() method.

ModelMetadataProviders.Current = new EmptyStringDataAnnotationsModelMetadataProvider();

I am using MVC 4.0, and this solution worked for me.

Now, when I pass in JSON with blank parameter values, my string properties are empty strings and not nulls.  You know you are a geek when you get excited about an empty string.

It was a bit of a journey, but this obscure change should make my life easier.

And I suspect that once we start developing against the GP 2015 Service Based Architecture, this may come in handy if GP returns empty JSON parameters.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

Monday, November 17, 2014

Unable to read unsaved Cash Receipt Document Number using VS Tools

By Steve Endow

I have a customer that issues thousands of invoices to a handful of customers.  So when they receive a payment from a customer, they have to apply the payment to hundreds or thousands of invoices.

To accommodate this high volume / bulk payment apply process, I developed a custom AR apply window.  The window works well on its own, but the customer wanted the ability to launch the custom apply window from the Cash Receipts Entry window, immediately after they enter the payment.

When the user launches my custom apply window, they wanted the customer, payment doc number, check number, and payment amount from GP all pre-populated so that they could immediately start applying the payment, which makes complete sense.

No problem, I thought.  Easy peazy lemon squeezee, right?

Unfortunately, no.

In my VS Tools AddIn project, I added the Menu Handler to the Cash Receipt window so the user could open my custom window.  Very straightforward and worked fine.

I then added some code to read the field values from the Cash Receipt window so that I could populate the fields on my custom apply window.  That was simple as well and everything looked good.

But when I tested the code, it didn't work.  My window would launch, but the fields on my form weren't populating.

In Visual Studio, I attached to the Dynamics.exe process and started debugging.  I found that an If statement was being skipped:

if (rmCashReceiptWindow.CustomerNumber.Value != string.Empty && rmCashReceiptWindow.DocumentNumber.Value != string.Empty && rmCashReceiptWindow.CheckNumber.Value != string.Empty)

That was odd, since I had populated all of the fields on the Cash Receipt window.

The customer number, document number, and check number were all populated.

I checked the field values in Visual Studio, and to my surprise, the DocumentNumber field value was empty.

I could read the Check Number and Customer Number just fine, but the Document Number field had no value, even though it was displayed in GP.

Um, what gives?

Just to make sure I wasn't making a silly mistake, I fired up Andrew Dean's very cool VSTools Object Explorer that we demonstrated at our reIMAGINE session last week.

Sure enough, the Object Explorer confirmed that the Document Number field had no value, and that the value wasn't stored in any of the other fields accessible through VS Tools.

This confirmed that with an UNSAVED cash receipt, the Document Number field value was not accessible in VS Tools.

But after I saved the cash receipt, then pulled the receipt back up in the Cash Receipt window, I could read the Document Number value in VS Tools.

Not sure what is going on.  My two wild guesses are that during data entry, the field value is being stored in some temporary field--which seems really strange.  Or, the VS Tools field value is somehow wired up to the underlying Dex table buffer, and that table buffer value is not populated until the transaction is saved.  Which seems like a stretch as well.

I'm going to dig into it further and see if other windows have this issue, but this definitely complicates things if you are trying to trigger events in VS Tools that depend on the GP document number during data entry.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter