When I develop integrations, I sometimes have to retrieve a handful of data from a record in the Dynamics GP company database so that I can perform some other process.
Today, I had to create a routine that would automatically process credit card transactions. Well, the API for the credit card software requires that I send in a bunch of customer and account info in order to process a transaction. So I have to query a dozen or so fields from GP and then use those field values for the CC transaction.
To keep my code clean, I like to have one method to submit the transaction, and separate methods to retrieve any other data that is required. So I might have a SubmitCCTransaction method that uses a GetGPCustomerInfo method to get data about the customer.
But if I am querying a dozen or more fields for the GP customer, I don't want to pass a dozen or more by ref parameters. So I instead create a GPCustomer object that has a bunch of properties. This is pretty standard.
Here is a simplified GPCustomer object with a dozen properties.
So I call GetGPCustomerInfo and send a GPCustomer object by reference. GetGPCustomerInfo then retrieves data from GP, populates the GPCustomer properties, and sends the data back to my SubmitCCTransaction routine.
But I didn't want to write code to retrieve each field in the data table and assign it to each property of my GPCustomer object. I wanted to essentially convert the row in my data table into a GPCustomer object.
Here's an example of how to do that.
The key part is the foreach loop that iterates through the fields (data columns) and assigns the value to the matching object property.
The only requirement is that your object properties must match the field list in your SQL query. Your SQL query can contain a fewer number of fields than the number of object properties, but it cannot contain more. And the object property names must match the field names that are queried.
Anyway, it took me a little bit of digging to find the specific syntax to do this, so I wanted to post it here for future reference.
UPDATE: After some testing, I found that the code may require further refinement, depending on your SQL data source. In my case, I was querying a table for a third party CC product. That table has a field that is a GUID data type, and it also has fields that allow NULL. Both of those conditions will cause errors with the above code. Here is modified code to demonstrate one way to deal with those situations.
Before calling SetValue, I check the DataType of the column to see if it is a SQL GUID data type. Then, I also have to check if the field value is NULL, in which case I assign am empty string.