Export (0) Print
Expand All

Use Condition Operators in a Query

banner art

[Applies to: Microsoft Dynamics CRM 4.0]

Shows how to create a query expression that uses condition operators.

This sample code can be found in the following files in the SDK download:

Server\HowTo\CS\Query\ConditionOperators.cs

For more information about the helper methods in the Microsoft.Crm.Sdk.Utility.CrmServiceUtility namespace, see Utility Sample Code.

Example

This sample code shows how to build the following SQL query using a query expression:

SELECT contact.fullname, contact.address1_telephone1

FROM contact

   LEFT OUTER JOIN account

ON contact.parentcustomerid = account.accountid

AND

   account.name = 'Microsoft'

WHERE (contact.address1_stateorprovince = 'WA'

AND

   contact.address1_city in ('Redmond', 'Bellevue', 'Kirland', 'Seattle')

AND

   contact.address1_telephone1 like '(206)%'

   OR

   contact.address1_telephone1 like '(425)%'

AND

   DATEDIFF(DAY, contact.createdon, GETDATE()) > 0

AND

   DATEDIFF(DAY, contact.createdon, GETDATE()) < 30

AND

   contact.emailaddress1 Not Null

)

[CS#]
using System;
using System.Xml;
using CrmSdk;
using Microsoft.Crm.Sdk.Utility;

namespace Microsoft.Crm.Sdk.HowTo.Query
{
   /// <summary>
   /// This sample shows how to use condition operators in a database query.
   /// </summary>
   public class ConditionOperators
   {
      public ConditionOperators()
      {

      }

      public static bool Run(string crmServerUrl, string orgName)
      {
         bool success = true;

         try
         {
         
            // Set up the CRM Service.  
            CrmService service = Microsoft.Crm.Sdk.Utility.CrmServiceUtility.GetCrmService(crmServerUrl, orgName);
            service.PreAuthenticate = true;

            #region Setup Data Required for this Sample
            
            // Create an account
            account microsoftAccount = new account();
            microsoftAccount.name = "Microsoft";
            
            Guid microsoftAccountId = service.Create(microsoftAccount);
            
            // Create some contacts that meet the query conditions
            contact contact206 = new contact();
            contact206.fullname = "Adam Carter";
            contact206.firstname = "Adam";
            contact206.lastname = "Carter";
            contact206.emailaddress1 = "adamc@microsoft.com";
            contact206.address1_city = "Redmond";
            contact206.address1_stateorprovince = "WA";
            contact206.address1_telephone1 = "(206)555-5555";
            contact206.parentcustomerid = new Customer();
            contact206.parentcustomerid.type = "account";
            contact206.parentcustomerid.Value = microsoftAccountId;
            
            contact contact425 = new contact();
            contact425.fullname = "Adina Hagege";
            contact425.firstname = "Adina";
            contact425.lastname = "Hagege";
            contact206.emailaddress1 = "adinah@microsoft.com";
            contact425.address1_city = "Bellevue";
            contact425.address1_stateorprovince = "WA";
            contact425.address1_telephone1 = "(425)555-5555";
            contact425.parentcustomerid = new Customer();
            contact425.parentcustomerid.type = "account";
            contact425.parentcustomerid.Value = microsoftAccountId;
            
            Guid contact206Id = service.Create(contact206);
            Guid contact425Id = service.Create(contact425);
            
            #endregion
            
            // Build the following SQL query using QueryExpression:
            //
            //      SELECT contact.fullname, contact.address1_telephone1
            //      FROM contacts
            //         LEFT OUTER JOIN accounts
            //            ON contact.parentcustomerid = account.accountid
            //            AND
            //            account.name = 'Microsoft'
            //      WHERE (contact.address1_stateorprovince = 'WA'
            //      AND
            //         contact.address1_city in ('Redmond', 'Bellevue', 'Kirland', 'Seattle')
            //      AND 
            //         contact.address1_telephone1 like '(206)%'
            //         OR
            //         contact.address1_telephone1 like '(425)%'
            //      AND
            //         DATEDIFF(DAY, contact.createdon, GETDATE()) > 0
            //      AND
            //         DATEDIFF(DAY, contact.createdon, GETDATE()) < 30
            //      AND
            //         contact.emailaddress1 Not NULL
            //            )

            // Create state condition
            ConditionExpression stateCondition = new ConditionExpression();
            stateCondition.AttributeName = "address1_stateorprovince";
            stateCondition.Operator = ConditionOperator.Equal;
            stateCondition.Values = new string[] { "WA" };

            // Create city condition
            ConditionExpression cityCondition = new ConditionExpression();
            cityCondition.AttributeName = "address1_city";
            cityCondition.Operator = ConditionOperator.In;
            cityCondition.Values = new string[] { "Redmond", "Bellevue", "Kirkland", "Seattle" };

            // Create telephone condition for area code 206.
            ConditionExpression phoneCondition206 = new ConditionExpression();
            phoneCondition206.AttributeName = "address1_telephone1";
            phoneCondition206.Operator = ConditionOperator.Like;
            phoneCondition206.Values = new string[] { "(206)%" };

            // Create telephone condition for area code 425.
            ConditionExpression phoneCondition425 = new ConditionExpression();
            phoneCondition425.AttributeName = "address1_telephone1";
            phoneCondition425.Operator = ConditionOperator.Like;
            phoneCondition425.Values = new string[] { "(425)%" };

            // Create the filter used to OR the phone conditions.
            FilterExpression phoneFilter = new FilterExpression();
            phoneFilter.FilterOperator = LogicalOperator.Or;
            phoneFilter.Conditions = new ConditionExpression[] { phoneCondition206, phoneCondition425 };

            // Create the city filter
            FilterExpression cityFilter = new FilterExpression();
            cityFilter.Conditions = new ConditionExpression[] { cityCondition };

            // Create the "created in last 30 days" condition
            ConditionExpression last30DaysCondition = new ConditionExpression();
            last30DaysCondition.AttributeName = "createdon";
            last30DaysCondition.Operator = ConditionOperator.LastXDays;
            last30DaysCondition.Values = new object[1];
            last30DaysCondition.Values[0] = 30;
            
            // Create the "Not NULL" condition
            ConditionExpression emailNotNullCondition = new ConditionExpression();
            emailNotNullCondition.AttributeName = "emailaddress1";
            emailNotNullCondition.Operator = ConditionOperator.NotNull;

            // Create the "created in last 30 days" filter
            FilterExpression last30DaysFilter = new FilterExpression();
            last30DaysFilter.Conditions = new ConditionExpression[] { last30DaysCondition };
            
            // Create the "Not NULL" filter
            FilterExpression emailNotNullFilter = new FilterExpression();
            emailNotNullFilter.Conditions = new ConditionExpression[] { emailNotNullCondition };
            
            // Create the outer most filter to AND the state condition with the other filters
            FilterExpression outerFilter = new FilterExpression();
            outerFilter.FilterOperator = LogicalOperator.And;
            outerFilter.Conditions = new ConditionExpression[] { stateCondition };
            outerFilter.Filters = new FilterExpression[] { cityFilter, phoneFilter, last30DaysFilter, emailNotNullFilter };

            // Determine which columns are returned
            // NOTE: the ID of the entity being queried will always be returned
            ColumnSet resultSetColumns = new ColumnSet();
            resultSetColumns.Attributes = new string[] { "fullname", "address1_telephone1" };
            
            // Create the outer join condition
            ConditionExpression outerJoinCondition = new ConditionExpression();
            outerJoinCondition.AttributeName = "name";
            outerJoinCondition.Operator = ConditionOperator.Equal;
            outerJoinCondition.Values = new string[] { "Microsoft" };
            
            // Create the outer join filter.
            FilterExpression outerJoinFilter = new FilterExpression();
            outerJoinFilter.Conditions = new ConditionExpression[] { outerJoinCondition };
            
            // Create the outer join link.
            LinkEntity outerJoinAccount = new LinkEntity();
            outerJoinAccount.JoinOperator = JoinOperator.LeftOuter;
            outerJoinAccount.LinkCriteria = outerJoinFilter;
            outerJoinAccount.LinkFromAttributeName = "contactid";
            outerJoinAccount.LinkFromEntityName = EntityName.contact.ToString();
            outerJoinAccount.LinkToAttributeName = "accountid";
            outerJoinAccount.LinkToEntityName = EntityName.account.ToString();
            
            // Put everything together in an expression.
            QueryExpression qryExpression = new QueryExpression();
            qryExpression.Criteria = outerFilter;
            qryExpression.ColumnSet = resultSetColumns;

            // Set the table to query.
            qryExpression.EntityName = EntityName.contact.ToString();
            qryExpression.LinkEntities = new LinkEntity[] { outerJoinAccount };

            // Return all records.
            qryExpression.Distinct = false;
            
            // Execute the query.
            BusinessEntityCollection contactsResultSet = service.RetrieveMultiple(qryExpression);
            
            // Iterate through each contact to build a call list.
            foreach (contact aContact in contactsResultSet.BusinessEntities)
            {
               // Access only columns included in the column set of the query expression.
               // NOTE: All other columns will be null except for the entities ID, which is always returned.
               Console.WriteLine("Contact's ID: " + aContact.contactid.Value);
               Console.WriteLine("Contact Name: " + aContact.fullname);
               Console.WriteLine("Contact Phone: " + aContact.address1_telephone1);
            }

         #region check success

         // Validate that an expected contact was returned.
         if (contactsResultSet.BusinessEntities.Length == 0)
         {
            success = false;
         }
         else
         {
            bool contactFound = false;
            foreach (contact aContact in contactsResultSet.BusinessEntities)
            {
               if (aContact.contactid.Value == contact206Id ||
                  aContact.contactid.Value == contact425Id )
               {
                  contactFound = true;
                  break;
               }
            }
            
            if (!contactFound)
            {
               success = false;
            }
         }
         
         #endregion

         #region Remove Data Required for this Sample
         
         // Delete contacts.
         service.Delete(EntityName.contact.ToString(), contact206Id);
         service.Delete(EntityName.contact.ToString(), contact425Id);
         
         // Delete account.
         service.Delete(EntityName.account.ToString(), microsoftAccountId);

         #endregion

         }
         catch (System.Web.Services.Protocols.SoapException)
         {
            // Perform error handling here.
            throw;
         }
         catch (Exception)
         {
            throw;
         }

         return success;
      }
   }
}

See Also

Reference


© 2010 Microsoft Corporation. All rights reserved.


Show:
© 2014 Microsoft