Generic SQL Error – RetriveMultiple with ConditionOperator.Contains in CRM

Today someone “me” wanted to change the requirements of the wrap I have over the CRM web services so I could reuse the method more often, but to do so I need to transform the method more standard. So I changed the ConditionOperator.Equal to ConditionOperator.Contains. But was giving me an error “Generic SQL Error” while using in the ConditionExpression. The requirement was to get all the records from an entity that contains a specific field with a certain attribute value.

Before, using Equal:

Microsoft.Xrm.Sdk.Query.ConditionExpression condition = new Microsoft.Xrm.Sdk.Query.ConditionExpression(FieldName), Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, AttributeValue);

After, using Contains: You need to add % to the attributevalue but don’t work if the table or indexed view is not full-text indexed, therefore you need to use Like.

Microsoft.Xrm.Sdk.Query.ConditionExpression condition = new Microsoft.Xrm.Sdk.Query.ConditionExpression(FieldName), Microsoft.Xrm.Sdk.Query.ConditionOperator.Contains, “%”+AttributeValue+“%”);

After, using Like: You need to add % to the attributevalue

Microsoft.Xrm.Sdk.Query.ConditionExpression condition = new Microsoft.Xrm.Sdk.Query.ConditionExpression(FieldName), 
Microsoft.Xrm.Sdk.Query.ConditionOperator.Like, “%”+AttributeValue+“%”);

#CRM2016 – Get all teams by user in C#

public Microsoft.Xrm.Sdk.EntityCollection UserTeams(Guid UserID)
{

    QueryExpression query = new QueryExpression("team");
    query.ColumnSet = new ColumnSet(true);
    LinkEntity link = query.AddLink("teammembership", "teamid", "teamid");
    link.LinkCriteria.AddCondition(new ConditionExpression("systemuserid", ConditionOperator.Equal, UserID));

    try
    {
      return service.RetrieveMultiple(query);
    }
    catch (Exception ex)
    {
       // Do your Error Handling here
       throw ex;
    }
}

CRM2016 – Check if User is a member of a team C#

The code is the following:

public static bool IsTeamMember(Guid teamID, Guid userID, IOrganizationService service)
{
QueryExpression query = new QueryExpression("team");
query.ColumnSet = new ColumnSet(true);
query.Criteria.AddCondition(new ConditionExpression("teamid", ConditionOperator.Equal, teamID));
LinkEntity link = query.AddLink("teammembership", "teamid", "teamid");
link.LinkCriteria.AddCondition(new ConditionExpression("systemuserid", ConditionOperator.Equal, userID));
var results = service.RetrieveMultiple(query);

if (results.Entities.Count > 0)
{
return true;
}
else
{
return false;
}
}

CRM2016 – Getting use of PublishXmlRequest SDK message to publish an Entity or a Web Resource

Today I had a timeout error on my application when I tried to use PublishAllXmlRequest .I resolved to use a single PublishXmlRequest in this way:

#region Publish FormXMl
//Publish entity for which formxml has been changed 
PublishXmlRequest publishRequest = new PublishXmlRequest();
publishRequest.ParameterXml ="<importexportxml>" +
"    <entities>" +
"        <entity>" + entityName + "</entity>" +
"    </entities>" +
"    <nodes/>" +
"    <securityroles/>  " +
"    <settings/>" +
"    <workflows/>" +
"</importexportxml>";

try
{
organizationService.Execute(publishRequest);
}
catch (Exception ex)
{
    throw new ex
}
#endregion

CRM2011 – Plugin Registration Error: A proxy type with the name account has been defined by another assembly

It’s second time that I have this error but everytime I forgot the solution and I need to search again…. 🙂 Now I want to save the solution here on my blog.

Problem:

So I ran into a very tricky error when building a custom tool to register CRM 2011 Plug-ins.  I wouldn’t be surprised if other people run into the same issue when registering plug-ins programmatically. And its such a strange problem I thought it deserved an explanation.

Here is the exception I got when retrieving a list of records from CRM:

<strong>A proxy type with the name account has been defined by another assembly. Current type: Plugins1.Entities.Account, App_Code.zzq-u5xe, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null, Existing type: Plugins1.Entities.Account, vender portal, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null
Parameter name: account</strong>

I trace the problem what it is? It is actually problem from Organization Service i used,It gets definition of account entity definition from two assembly and confuse to process which assembly should be executed for actual objects.

Solution:

Issue resolved by specify assembly for execution or creating objects for entities:
just have to add a new ProxyTypesBehavior:

ClientCredentials credentials = new ClientCredentials();
credentials.Windows.ClientCredential = new NetworkCredential(username, password, domain);
credentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
IServiceConfiguration<IOrganizationService> orgConfigInfo = ServiceConfigurationFactory.CreateConfiguration<IOrganizationService>(new Uri(@"[...]/XRMServices/2011/Organization.svc"));
OrganizationServiceProxy client = new OrganizationServiceProxy(orgConfigInfo, credentials);
<strong>client.ServiceConfiguration.CurrentServiceEndpoint.Behaviors.Add(new ProxyTypesBehavior(Assembly.GetExecutingAssembly()));</strong>
CRM 2011 – How to get the current user from within a worflow

CRM 2011 – How to get the current user from within a worflow

Today I needed to get the Current User inside a workflow activity. In CRM 2001 process it is not possible to get current user and in some cases a reference can be necessary in order to set the right user.
A Custom Workflow Activity can be created for this purpose, taking advantage of the InitiatingUserId property.

public class GetCurrentUser : CodeActivity
    {
        [Output("Current User")]
        [ReferenceTarget("systemuser")]
        public OutArgument<EntityReference> CurrentUser { get; set; }
        protected override void Execute(CodeActivityContext context)
        {
            IWorkflowContext workflowContext = context.GetExtension<IWorkflowContext>();
            CurrentUser.Set(context, new EntityReference("systemuser", workflowContext.InitiatingUserId));

        }
    }

Now from our workflow activity we have the current user.

CRM2011 – Search what is the database associated at Organization

Sometimes I need to search what is a database associated at an existing organization. It is simple: From CRM Server, we need to ope SQL Server tools and execuete this query:

By running this query within MSCRM_CONFIG database we should be able to easily identify each organization:

SELECT [DatabaseName]
      ,[FriendlyName]
      ,[SqlServerName]
      ,[SrsUrl]
      ,[State]
      ,[UniqueName]
      ,[UrlName]
      ,[IsDeleted]
FROM [MSCRM_CONFIG].[dbo].[Organization]

That’s all…

CRM2011 – Retrieve Plugin

2007_microsoft_dynamics_crmToday I need to execute a method every time open a Contact record.

Usually this would be frowned upon because this code could easily present a performance issues, especially if the data you’re gathering is hosted on an external resource.

In the off-change you do need to do this, here is the solution:

First, create a new Plugin and sign the assembly with a key:

public void Execute(IServiceProvider serviceProvider)
{
    // Obtain the execution context from the service provider.
    Microsoft.Xrm.Sdk.IPluginExecutionContext context = (Microsoft.Xrm.Sdk.IPluginExecutionContext)
        serviceProvider.GetService(typeof(Microsoft.Xrm.Sdk.IPluginExecutionContext));

    if (context.Depth == 1)
    {
        IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
        IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

        // Obtain the target entity from the input parmameters.
        EntityReference entity = (EntityReference)context.InputParameters["Target"];

        ColumnSet cols = new ColumnSet(
                             new String[] { "lastname", "firstname", "address1_name" });

        var contact = service.Retrieve("contact", entity.Id, cols);

        if (contact != null)
        {
            if (contact.Attributes.Contains("address1_name") == false)
            {
                Random rndgen = new Random();
                contact.Attributes.Add("address1_name", "first time value: " + rndgen.Next().ToString());
            }
            else
            {
                contact["address1_name"] = "i already exist";
            }
            service.Update(contact);
        }
    }
}

Next, hop over to the Plugin Registration Tool (included with the CRM SDK) and register the plugin:
Unbenannt

What you’ll end up with in this example is:

On first open of the record:

clip_image002_thumb

 

 

 

On all future opens of the record:

clip_image0025_thumb

CRM 2011 – Pass Data Between Plugins using SharedVariables

2007_microsoft_dynamics_crmEvery day I have new things to learn … and yesterday I had a problem about tho share variables between plugins in my CRM2011 project. MSCRM2011 and the execution pipeline provides the ability to pass data from one plug-in to another through an IPluginExecutionContext property called SharedVariables. This property is a collection of key/value pairs which developers can use to share data between plug-ins which are registered on both the pre and post events.

Here is the way to pass some paramters and get in between the steps in MS CRM 2011, while writing plugin.

Pre-Create Account Plug-in

if (context.InputParameters.ContainsKey("Target") &amp;&amp; context.InputParameters["Target"] is Entity)
{
Entity target = context.InputParameters["Target"] as Entity;
if (target != null)
{
// some wacky data validation
string city = target.GetAttributeValue&lt;string&gt;("address1_city") ?? string.Empty;
int numEmployees = target.GetAttributeValue&lt;int&gt;("numberofemployees");
int accountCategory = target.GetAttributeValue&lt;int&gt;("accountcategorycode");

// city is auckland, numEmployees &gt; 100, account category is preferred customer
bool updateRelated = city.Equals("Auckland", StringComparison.InvariantCultureIgnoreCase) &amp;&amp; numEmployees &gt; 100 &amp;&amp; accountCategory == 1;
context.SharedVariables.Add("updatedRelated", updateRelated);
}
}

Post-Create Account Plug-in

if (context.InputParameters.ContainsKey("Target") &amp;&amp; context.InputParameters["Target"] is Entity)
{
Entity target = context.InputParameters["Target"] as Entity;
if (target != null)
{
if (context.SharedVariables.ContainsKey("updatedRelated"))
{
bool updateRelated = (bool)context.SharedVariables["updatedRelated"];
if (updateRelated)
{
// additional logic to update related records of the Account
}
}
}
}