CrmAdo

Huh?

CrmAdo is an ADO.NET data provider for Dynamics CRM. This means it enables you to use ordinary ADO.NET code to interact with Dynamics Crm, via a standard ADO.NET family of classes.

CrmAdo translates SQL commands into appropriate Dynamics CRM SDK web service calls behind the scenes.

Getting Started

Add the CrmAdo NuGet package to your visual studio project. This will also install the Dynamics SDK libraries and all other dependencies.

A connection string will automatically be added to your web / app config file when you install the NuGet package, you should set this connection string appropriately for the dynamics crm organisation that you want to connect to.

You can now use ADO.net to connect to and query CRM. Here is some quick sample code:

            var dbprovider = DbProviderFactories.GetFactory(CrmAdoConstants.Invariant);
            var connectionString = ConfigurationManager.ConnectionStrings["CrmOrganisation"];
            using (var conn = dbprovider.CreateConnection())
            {
                conn.ConnectionString = connectionString.ConnectionString;
                conn.Open();
                var command = conn.CreateCommand();
                command.CommandText = "Select C.contactid, C.firstname, C.lastname, A.line1 From contact C LEFT JOIN customeraddress A on C.contactid = A.parentid";
              
                using (var reader = command.ExecuteReader())
                {
                    int resultCount = 0;
                    foreach (var result in reader)
                    {
                        resultCount++;
                        var contactId = (Guid)reader["contactid"];
                        var firstName = (string)reader["firstname"];
                        var lastName = reader.GetString(2);
                        Console.WriteLine("{0} {1} {2}", contactId, firstName, lastName);
                    }
                    Console.WriteLine("There were " + resultCount + " results..");
                }
            }

        }
  

Note: the above query doesn't use parameters, however parameters are fully supported.

Supported SQL - some examples

The Dynamics CRM SDK does impose some limitations as to the T-SQL that can be used. Therefore, the following aims to give a quick overview of the T-SQL that can be used - although it is not an exhaustive list.

Joins

You can use Inner Joins, and Left Joins. Full outer joins are not supported. Nesting joins is fully supported.

   Select C.contactid, C.firstname, C.lastname, A.line1 From contact C INNER JOIN customeraddress A on C.contactid = A.parentid
   

Filter operators

You can use any of the following filter operators in your where clause:

• =
• <>
• >=
• <=
• >
• <
• IS NULL
• IS NOT NULL
• LIKE
• NOT LIKE
• IN
• NOT IN
• CONTAINS
• NOT CONTAINS

   Select C.contactid, C.firstname, C.lastname, A.line1 From contact C INNER JOIN customeraddress A on C.contactid = A.parentid WHERE A.line1 LIKE '%deathstar%'
   

Filter operators with Conjunctions

You can use conjunctions with filter operators to combine them together logically:

• AND
• OR

   Select C.contactid, C.firstname, C.lastname, A.line1 From contact C INNER JOIN customeraddress A on C.contactid = A.parentid WHERE A.line1 LIKE '%deathstar%' AND C.firstname = 'Darth'
   

More complex:

Filter operators with Conjunctions and Parenthesis

You can use multiple filters, with AND and OR conjunctions as well as parenthesis.:

• AND
• OR

   Select C.contactid, C.firstname, C.lastname, A.line1 From contact C INNER JOIN customeraddress A on C.contactid = A.parentid WHERE (((A.line1 LIKE '%deathstar%' AND C.firstname = 'Darth') OR (A.line1 LIKE '%away mission%')) AND C.lastname ='Vader')
   

DDL

I am currently busy adding support for DDL statements. This will let you create / alter / drop entities / attributes in Dynamics CRM via standard SQL commands.

Create Entity

   CREATE TABLE MyEntity(MyEntityId UNIQUEIDENTIFIER PRIMARY KEY, MyEntityPrimaryName VARCHAR(500))
   

Acknowledgements

Much thanks and appreciation goes to Travis Parks (@jehugaleahsa) for his work with the SQLGeneration library which is used under the hood by CrmAdo to carry out the complex task of parsing T-SQL. Travis has done a wonderful job with the SQLGeneration library, and this has saved me a tonne of work :D