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.
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
public bool GetGPCustomerInfo(string gpDatabase, string customerID, ref Models.GPCustomer gpCustomer, ref string message)
{
try
{
//SQL query to get GP customer info
//Field list and field names must match object properties
string commandText = "SELECT
CUSTNMBR, CUSTNAME, CNTCPRSN, ADRSCODE, ADDRESS1, ";
commandText
+= "ADDRESS2, ADDRESS3, CITY, STATE,
ZIP, PHONE1, PHONE2 FROM RM00101 ";
commandText
+= "WHERE CUSTNMBR = @CUSTNMBR";
//Parameter for customer ID
SqlParameter[]
sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@CUSTNMBR", System.Data.SqlDbType.VarChar, 15);
sqlParameters[0].Value = customerID;
DataTable dataTable = new DataTable();
//Method
to retrieve query results into data table
int recordCount = DataAccess.ExecuteDataSet(ref dataTable, gpDatabase, CommandType.Text, commandText,
sqlParameters);
//We should only retrieve one row for the customer
if (recordCount != 1)
{
message
= recordCount + " records were
retrieved for customer " + customerID + " in database " +
gpDatabase;
return false;
}
//Get the one row
DataRow row =
dataTable.Rows[0];
PropertyInfo prop;
//Iterate through the columns in the data row
foreach (DataColumn column in row.Table.Columns)
{
//Assign the row field values to the appropriate customer
object properties
prop =
gpCustomer.GetType().GetProperty(column.ColumnName);
prop = typeof(Models.GPCustomer).GetProperty(column.ColumnName);
prop.SetValue(gpCustomer, row[column.ColumnName]);
}
message = string.Empty;
return true;
}
catch (Exception ex)
{
message = "An unexpected error occurred in GetGPCustomerInfo:
" + ex.Message;
return false;
}
}
The key part is the foreach loop that iterates through the fields (data columns) and assigns the value to the matching object property.
PropertyInfo prop;
//Iterate through the columns in the data row
foreach (DataColumn column in row.Table.Columns)
{
//Assign the row field values to the appropriate customer object properties
prop = gpCustomer.GetType().GetProperty(column.ColumnName);
prop = typeof(Models.GPCustomer).GetProperty(column.ColumnName);
prop.SetValue(gpCustomer, row[column.ColumnName]);
}
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.
if (column.DataType.ToString() == "System.Guid")
{
prop.SetValue(account,
row[column.ColumnName].ToString());
}
else
{
if (row[column.ColumnName].GetType().ToString() == "System.DBNull")
{
prop.SetValue(account, string.Empty);
}
else
{
prop.SetValue(account,
row[column.ColumnName]);
}
}
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.
No comments:
Post a Comment