April 27, 2015

GetAttributeValue demystified

Note: the content of this post is similar to this post from Dave Berry but I wanted to approach the same argument with some example code and tips.

Dynamics CRM allows to create fields with the following data types:

Except for "Single Line of Text" and "Multiple Lines of Text" (both use string), each one uses a different underline data type in .NET, some of these data types are nullable, some are not.

What means nullable and why we need to care about this?
Let's start with an example: In our CRM we have two records, the first record has all the fields filled with a value, in the second one all the fields are empty. When we use GetAttributeValue and there is a value, the method (fairly) returns the value. But what happens with our empty record? The response is "depends".

GetAttributeValue uses Generics, so we can choose to get a nullable type or not:
bool boolean = entity.GetAttributeValue<bool>("new_boolean");
bool? booleanNullable = entity.GetAttributeValue<bool?>("new_boolean");
In this case, if the value is null (for a Boolean/Two Options field means that no value is set) the first variable will contains false, the second will contains null.
The next table is a summary:

CRM Type .NET Type can hold null? default value
Single Line of Text string Yes
Option Set OptionSetValue Yes
Two Options bool No false
Image byte[] Yes
Whole Number int No 0
Floating Point Number double No 0.0
Decimal Number decimal No 0
Currency Money Yes
Multiple Lines of Text string Yes
Date and Time DateTime No DateTime.MinValue
Lookup EntityReference Yes
For the types that can hold null, we use GetAttributeValue and after check if it's null or not:
EntityReference lookupRef = entity.GetAttributeValue<EntityReference>("new_lookupid");
if (lookupRef == null) {
   // no value set
} else {
   // we have a value
For the types that can't hold null we need to ask ourselves: "The default value is enough for the requirement?"

If we are in a loop and we need to do a sum of an int field, the default value (0) is ok, so we can just do
int totalSum = 0;
foreach (Entity row in RowCollection.Entities) {
   int number = entity.GetAttributeValue<int>("new_wholenumber");
but if we are doing a multiplication we need to skip the null values, so we use int?
int totalMulty = 0;
foreach (Entity row in RowCollection.Entities) {
   int? number = entity.GetAttributeValue<int?>("new_wholenumber");
   if (number != null) {
otherwise with a null value our totalMulty variable will be 0.

DateTime is a particular case, it can't hold null but the default value (MinValue = 01/01/0001) can't be a valid CRM value (as happens with bool and numeric fields) so we can do the following check:
DateTime dateTime = entity.GetAttributeValue<DateTime>("new_datetime");
if (dateTime == DateTime.MinValue) {
   // no value set
} else {
   // we have a value
Practically when we don't use the nullable form, Dynamics CRM is doing the following:
double floating = entity.GetAttributeValue<double>("new_floating");
// equals to
double floating = entity.GetAttributeValue<double?>("new_floating").GetValueOrDefault();
The combination of the nullable form and the GetValueOrDefault can be useful in some scenarios. Let's say that we need to do a data migration to an external system, but if the source decimal Quantity is null, the target decimal Quantity must be -1.
decimal quantity = entity.GetAttributeValue<decimal?>("new_quantity").GetValueOrDefault(-1);
In this way we deal automatically the null values and they are ready for the target system.

Now you are a Dynamics CRM True Survivor!

April 2, 2015

Simplified Connection with complicated passwords

CRM 2011 introduced a very easy way to connect to Dynamics CRM instances: the Simplified Connection (MSDN: https://msdn.microsoft.com/en-us/library/gg695810.aspx).
Basically it's necessary to build a connection string instead of dealing with the specific deployment type (OnPremise, IFD or Online).

The downside of using a Simplified Connection is its weakness management of passwords containing special characters like double quotes, single quotes, ampersands.

Considering the MSDN example for a CRM Online connection:
Url=https://contoso.crm.dynamics.com; Username=jsmith@contoso.com; Password=passcode;
If the password is ;abc123 (note the semicolon) an exception will be thrown with the following message:
Format of the initialization string does not conform to specification starting at index 102. The solution for this problem is to include the password inside single quotes, the following connection string will work:
Url=https://contoso.crm.dynamics.com; Username=jsmith@contoso.com; Password=';abc123';
Assuming the connection string is builded dynamically the following code can be used:
string connectionString =
    String.Format("Url={0}; Username={1}; Password='{2}';", url, username, password);
What if our complicated password contains single quotes as well? Let's consider for example the following password: ;a''bc'123
In this case the previous exception (Format of the initialization string) will be thrown again. This issue can be solved "escaping" the single quotes using a Replace:
string connectionString =
    String.Format("Url={0}; Username={1}; Password='{2}';",
    url, username, password.Replace("'","''"));
Please note that the escape must be done also if your connection string is stored inside your app/web.config:
<add key="CRM"
value="Url=https://contoso.crm.dynamics.com; Username=jsmith@contoso.com; Password=';a''bc'123';"/>
But in this case our replace method will not work, because it will replace also the single quotes delimiting the password. In this scenario I suggest to put inside the app/web.config a placeholder instead of the delimiting single quotes that will be replaced after (for example #XYZ#):
<add key="CRM"
value="Url=https://contoso.crm.dynamics.com; Username=jsmith@contoso.com; Password=#XYZ#;a''bc'123#XYZ#;"/>
Then after the connection string is loaded we do the escape and the replace:
string connectionString = ConfigurationManager.ConnectionStrings["CRM"].ConnectionString;
// escape the single quotes inside the password
connectionString = connectionString.Replace("'","''");
// replace the placeholder with single quotes
connectionString = connectionString.Replace("#XYZ#","'");
Of course this will not work if the password contains the placeholder as well, so it's better to choose a long placeholder.

When the password is stored inside the app/web.config it's necessary to deal with another problem, the case that our password contains XML special characters (mostly double quotes) because this file is an XML.

If it's necessary to encode the password the following .NET method can be used:
string xmlPassword = System.Security.SecurityElement.Escape(password);
The result for the password ;a''b"c'123 (note the double quote between b and c that will create problems if not encoded) will be ;a&apos;&apos;bc&apos;123, a valid string to be written inside the app/web.config.