Use Condition Operators in a Query

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

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

)

   1:  //# [Use Condition Operators in a Query]

   2:  using System;

   3:  using System.Xml;

   4:  using CrmSdk;

   5:  using Microsoft.Crm.Sdk.Utility;

   6:   

   7:  namespace Microsoft.Crm.Sdk.HowTo.Query

   8:  {

   9:     /// <summary>

  10:     /// This sample shows how to use condition operators in a database query.

  11:     /// </summary>

  12:     public class ConditionOperators

  13:     {

  14:        public ConditionOperators()

  15:        {

  16:   

  17:        }

  18:   

  19:        public static bool Run(string crmServerUrl, string orgName)

  20:        {

  21:           bool success = true;

  22:   

  23:           try

  24:           {

  25:           

  26:              // Set up the CRM Service.  

  27:              CrmService service = Microsoft.Crm.Sdk.Utility.CrmServiceUtility.GetCrmService(crmServerUrl, orgName);

  28:              service.PreAuthenticate = true;

  29:   

  30:              #region Setup Data Required for this Sample

  31:              

  32:              // Create an account

  33:              account microsoftAccount = new account();

  34:              microsoftAccount.name = "Microsoft";

  35:              

  36:              Guid microsoftAccountId = service.Create(microsoftAccount);

  37:              

  38:              // Create some contacts that meet the query conditions

  39:              contact contact206 = new contact();

  40:              contact206.fullname = "Adam Carter";

  41:              contact206.firstname = "Adam";

  42:              contact206.lastname = "Carter";

  43:              contact206.emailaddress1 = "adamc@microsoft.com";

  44:              contact206.address1_city = "Redmond";

  45:              contact206.address1_stateorprovince = "WA";

  46:              contact206.address1_telephone1 = "(206)555-5555";

  47:              contact206.parentcustomerid = new Customer();

  48:              contact206.parentcustomerid.type = "account";

  49:              contact206.parentcustomerid.Value = microsoftAccountId;

  50:              

  51:              contact contact425 = new contact();

  52:              contact425.fullname = "Adina Hagege";

  53:              contact425.firstname = "Adina";

  54:              contact425.lastname = "Hagege";

  55:              contact206.emailaddress1 = "adinah@microsoft.com";

  56:              contact425.address1_city = "Bellevue";

  57:              contact425.address1_stateorprovince = "WA";

  58:              contact425.address1_telephone1 = "(425)555-5555";

  59:              contact425.parentcustomerid = new Customer();

  60:              contact425.parentcustomerid.type = "account";

  61:              contact425.parentcustomerid.Value = microsoftAccountId;

  62:              

  63:              Guid contact206Id = service.Create(contact206);

  64:              Guid contact425Id = service.Create(contact425);

  65:              

  66:              #endregion

  67:              

  68:              // Build the following SQL query using QueryExpression:

  69:              //

  70:              //      SELECT contact.fullname, contact.address1_telephone1

  71:              //      FROM contacts

  72:              //         LEFT OUTER JOIN accounts

  73:              //            ON contact.parentcustomerid = account.accountid

  74:              //            AND

  75:              //            account.name = 'Microsoft'

  76:              //      WHERE (contact.address1_stateorprovince = 'WA'

  77:              //      AND

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

  79:              //      AND 

  80:              //         contact.address1_telephone1 like '(206)%'

  81:              //         OR

  82:              //         contact.address1_telephone1 like '(425)%'

  83:              //      AND

  84:              //         DATEDIFF(DAY, contact.createdon, GETDATE()) > 0

  85:              //      AND

  86:              //         DATEDIFF(DAY, contact.createdon, GETDATE()) < 30

  87:              //      AND

  88:              //         contact.emailaddress1 Not NULL

  89:              //            )

  90:   

  91:              // Create state condition

  92:              ConditionExpression stateCondition = new ConditionExpression();

  93:              stateCondition.AttributeName = "address1_stateorprovince";

  94:              stateCondition.Operator = ConditionOperator.Equal;

  95:              stateCondition.Values = new string[] { "WA" };

  96:   

  97:              // Create city condition

  98:              ConditionExpression cityCondition = new ConditionExpression();

  99:              cityCondition.AttributeName = "address1_city";

 100:              cityCondition.Operator = ConditionOperator.In;

 101:              cityCondition.Values = new string[] { "Redmond", "Bellevue", "Kirkland", "Seattle" };

 102:   

 103:              // Create telephone condition for area code 206.

 104:              ConditionExpression phoneCondition206 = new ConditionExpression();

 105:              phoneCondition206.AttributeName = "address1_telephone1";

 106:              phoneCondition206.Operator = ConditionOperator.Like;

 107:              phoneCondition206.Values = new string[] { "(206)%" };

 108:   

 109:              // Create telephone condition for area code 425.

 110:              ConditionExpression phoneCondition425 = new ConditionExpression();

 111:              phoneCondition425.AttributeName = "address1_telephone1";

 112:              phoneCondition425.Operator = ConditionOperator.Like;

 113:              phoneCondition425.Values = new string[] { "(425)%" };

 114:   

 115:              // Create the filter used to OR the phone conditions.

 116:              FilterExpression phoneFilter = new FilterExpression();

 117:              phoneFilter.FilterOperator = LogicalOperator.Or;

 118:              phoneFilter.Conditions = new ConditionExpression[] { phoneCondition206, phoneCondition425 };

 119:   

 120:              // Create the city filter

 121:              FilterExpression cityFilter = new FilterExpression();

 122:              cityFilter.Conditions = new ConditionExpression[] { cityCondition };

 123:   

 124:              // Create the "created in last 30 days" condition

 125:              ConditionExpression last30DaysCondition = new ConditionExpression();

 126:              last30DaysCondition.AttributeName = "createdon";

 127:              last30DaysCondition.Operator = ConditionOperator.LastXDays;

 128:              last30DaysCondition.Values = new object[1];

 129:              last30DaysCondition.Values[0] = 30;

 130:              

 131:              // Create the "Not NULL" condition

 132:              ConditionExpression emailNotNullCondition = new ConditionExpression();

 133:              emailNotNullCondition.AttributeName = "emailaddress1";

 134:              emailNotNullCondition.Operator = ConditionOperator.NotNull;

 135:   

 136:              // Create the "created in last 30 days" filter

 137:              FilterExpression last30DaysFilter = new FilterExpression();

 138:              last30DaysFilter.Conditions = new ConditionExpression[] { last30DaysCondition };

 139:              

 140:              // Create the "Not NULL" filter

 141:              FilterExpression emailNotNullFilter = new FilterExpression();

 142:              emailNotNullFilter.Conditions = new ConditionExpression[] { emailNotNullCondition };

 143:              

 144:              // Create the outer most filter to AND the state condition with the other filters

 145:              FilterExpression outerFilter = new FilterExpression();

 146:              outerFilter.FilterOperator = LogicalOperator.And;

 147:              outerFilter.Conditions = new ConditionExpression[] { stateCondition };

 148:              outerFilter.Filters = new FilterExpression[] { cityFilter, phoneFilter, last30DaysFilter, emailNotNullFilter };

 149:   

 150:              // Determine which columns are returned

 151:              // NOTE: the ID of the entity being queried will always be returned

 152:              ColumnSet resultSetColumns = new ColumnSet();

 153:              resultSetColumns.Attributes = new string[] { "fullname", "address1_telephone1" };

 154:              

 155:              // Create the outer join condition

 156:              ConditionExpression outerJoinCondition = new ConditionExpression();

 157:              outerJoinCondition.AttributeName = "name";

 158:              outerJoinCondition.Operator = ConditionOperator.Equal;

 159:              outerJoinCondition.Values = new string[] { "Microsoft" };

 160:              

 161:              // Create the outer join filter.

 162:              FilterExpression outerJoinFilter = new FilterExpression();

 163:              outerJoinFilter.Conditions = new ConditionExpression[] { outerJoinCondition };

 164:              

 165:              // Create the outer join link.

 166:              LinkEntity outerJoinAccount = new LinkEntity();

 167:              outerJoinAccount.JoinOperator = JoinOperator.LeftOuter;

 168:              outerJoinAccount.LinkCriteria = outerJoinFilter;

 169:              outerJoinAccount.LinkFromAttributeName = "contactid";

 170:              outerJoinAccount.LinkFromEntityName = EntityName.contact.ToString();

 171:              outerJoinAccount.LinkToAttributeName = "accountid";

 172:              outerJoinAccount.LinkToEntityName = EntityName.account.ToString();

 173:              

 174:              // Put everything together in an expression.

 175:              QueryExpression qryExpression = new QueryExpression();

 176:              qryExpression.Criteria = outerFilter;

 177:              qryExpression.ColumnSet = resultSetColumns;

 178:   

 179:              // Set the table to query.

 180:              qryExpression.EntityName = EntityName.contact.ToString();

 181:              qryExpression.LinkEntities = new LinkEntity[] { outerJoinAccount };

 182:   

 183:              // Return all records.

 184:              qryExpression.Distinct = false;

 185:              

 186:              // Execute the query.

 187:              BusinessEntityCollection contactsResultSet = service.RetrieveMultiple(qryExpression);

 188:              

 189:              // Iterate through each contact to build a call list.

 190:              foreach (contact aContact in contactsResultSet.BusinessEntities)

 191:              {

 192:                 // Access only columns included in the column set of the query expression.

 193:                 // NOTE: All other columns will be null except for the entities ID, which is always returned.

 194:                 Console.WriteLine("Contact's ID: " + aContact.contactid.Value);

 195:                 Console.WriteLine("Contact Name: " + aContact.fullname);

 196:                 Console.WriteLine("Contact Phone: " + aContact.address1_telephone1);

 197:              }

 198:   

 199:           #region check success

 200:   

 201:           // Validate that an expected contact was returned.

 202:           if (contactsResultSet.BusinessEntities.Length == 0)

 203:           {

 204:              success = false;

 205:           }

 206:           else

 207:           {

 208:              bool contactFound = false;

 209:              foreach (contact aContact in contactsResultSet.BusinessEntities)

 210:              {

 211:                 if (aContact.contactid.Value == contact206Id ||

 212:                    aContact.contactid.Value == contact425Id )

 213:                 {

 214:                    contactFound = true;

 215:                    break;

 216:                 }

 217:              }

 218:              

 219:              if (!contactFound)

 220:              {

 221:                 success = false;

 222:              }

 223:           }

 224:           

 225:           #endregion

 226:   

 227:           #region Remove Data Required for this Sample

 228:           

 229:           // Delete contacts.

 230:           service.Delete(EntityName.contact.ToString(), contact206Id);

 231:           service.Delete(EntityName.contact.ToString(), contact425Id);

 232:           

 233:           // Delete account.

 234:           service.Delete(EntityName.account.ToString(), microsoftAccountId);

 235:   

 236:           #endregion

 237:   

 238:           }

 239:           catch (System.Web.Services.Protocols.SoapException)

 240:           {

 241:              // Perform error handling here.

 242:              throw;

 243:           }

 244:           catch (Exception)

 245:           {

 246:              throw;

 247:           }

 248:   

 249:           return success;

 250:        }

 251:     }

 252:  }

AddItem Plugin Message(CampaignActivity)

Adds an item to a campaign activity.

The relevant classes are specified in the following table.

Type                 Class

Request            AddItemCampaignActivityRequest

Response         AddItemCampaignActivityResponse

Entity               campaignactivity

Remarks

To use this message, pass an instance of the AddItemCampaignActivityRequest class as the request parameter in the Execute method.

To perform this action, the caller must have access rights on the entity instance specified in the request class. For a list of required privileges, see AddItemCampaignActivity Privileges.

  
//# The following code example demonstrates how to add an item to a campaign activity.
// Set up the CRM Service.
CrmAuthenticationToken token = new CrmAuthenticationToken();
// You can use enums.cs from the SDK\Helpers folder to get the enumeration for Active Directory authentication.
token.AuthenticationType = 0; 
token.OrganizationName = "AdventureWorksCycle";
 
CrmService service = new CrmService();
service.Url = "http://:/mscrmservices/2007/crmservice.asmx";
service.CrmAuthenticationTokenValue = token;
service.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Create the request object.
AddItemCampaignActivityRequest add = new AddItemCampaignActivityRequest();

// Set the properties of the request object.
add.CampaignActivityId = new Guid("07D5B0B3-136C-48C6-8EAC-0030B9466E78");

// The item being added must have already been added to the campaign.
add.ItemId = new Guid("b4502053-6968-dc11-bb3a-0003ffbad8542");
add.EntityName = EntityName.salesliterature;

// Execute the request.
AddItemCampaignActivityResponse added = (AddItemCampaignActivityResponse) service.Execute(add); 
 

Plug-in Context

All plug-ins must implement the IPlugin interface. The IPlugin interface defines an Execute method, which takes an IPluginExecutionContext parameter. When a system event is fired for which a plug-in is registered, the system creates and populates the context. The system then calls the plug-in's Execute method, passing in the context as a parameter.

At run time, the execution context is passed to each registered plug-in in the pipeline when they are executed. Each plug-in in the execution pipeline is able to modify writable properties in the context. For example, given a plug-in registered for a pre-event and another plug-in registered for a post-event, the post-event plug-in can receive a context that has been modified by the pre-event plug-in. The same situation applies to plug-ins that are registered within the same stage. 

The context contains information that describes the run-time environment that the plug-in is executing in, information related to the execution pipeline, and entity business information.

All the properties in the class are read-only. However, your plug-in can modify the contents of those properties that are of type PropertyBag.

There are two methods provided in IPluginExecutionContext: CreateCrmService and CreateMetadataService. We recommend that plug-in code should create an instance of the Microsoft Dynamics CRM Web services through these methods. The platform provides the correct Web service URLs and network credentials for you when you use these methods. However, do not use these methods if you are writing code for a plug-in in that is used in a child pipeline. In a child pipeline, you must instantiate the CrmService or MetadataService manually. For examples about how to call these methods, see the plug-in code provided in the SDK samples.