A better way to learn LINQ-to-CRM

In Microsoft Dynamics CRM 4.0, we introduced LINQ for our developers through the Advanced Developer Extensions for Microsoft Dynamics CRM 4.0 toolkit. We have further improved this support in Dynamics CRM 2011 by including LINQ as a first class data query mechanism. A better alternative to QueryExpression, LINQ provides a simple, intuitive and unified approach for data retrieval across different data sources.

Those of you who already use LINQ in some other .NET projects are possibly familiar with a great tool that makes LINQ developers’ life easier – LINQPad (www.linqpad.net). LINQPad is similar to SQL Management Studio – it lets you connect to various data sources (for which LINQ providers exist), examine their structure (just like you do with tables and columns in SQL Management Studio), and, finally, write and execute LINQ queries on a fly. LINQPad is a terrific tool to experiment with LINQ. It eliminates a need to create a separate sample C# project to test your queries.

I have written a new tool “LINQPad Plugin for Microsoft Dynamics CRM 2011” to help CRM developers learn and test LINQ queries against any CRM 2011 deployment. The project is available for download from its MSDN Code Gallery page - http://code.msdn.microsoft.com/crmlinqpad

The application is fairly easy to use. You create a connection to a CRM organization by entering organization URL and credentials, and then the plugin retrieves information about the organization’s entities. A tree-like structure of entity sets and entity attributes will be displayed on the left panel of the application. Once that is done, you can write and execute LINQ statements from the main application window. The results of the execution will be shown in a table on the bottom panel.


Features
1) Support of all Microsoft Dynamics CRM 2011 deployment types: Online, OnPremise and SPLA
2) Entity schema browsing – navigating through entity sets, entity attributes and relationships
3) Writing and executing LINQ queries against entity sets
4) Examining QueryExpression used for actual data retrieval

Installation instructions
1) Make sure you have .NET Framework 4.0 and Windows Identity Foundation installed
2) Download and install LINQPad for .NET Framework 4.0 from http://www.linqpad.net
3) Download CrmLINQPadPlugin.lpx from http://archive.msdn.microsoft.com/crmlinqpad/Release/ProjectReleases.aspx?ReleaseId=5329
4) Run LINQPad and register the plugin – go to "Add Connection\View More Drivers\Browse" and select CrmLINQPadPlugin.lpx
5) To create a CRM connection, click "Add Connection" and select "MS CRM 2011" in "Use a typed data context from your own assembly" section

The plugin supports all CRM deployment types – Online, OnPremise and SPLA.

More instructions on how to download and configure LINQPad and the plugin can be found in the Home section of the plugin’s MSDN Code Gallery page.

Some Changes in CRM 2011 plugin coding

There are a lot of syntax and functional changes in MSCRM 2011 plugins. I have listed some of them below:
1. New References
• System.ServiceModel
• System.Runtime.Serialization

2. Microsoft.Crm.Sdk has been replaced by Microsoft.Xrm.Sdk

3. The default parameter for execute method has been changed from
IPluginExecutionContext to IServiceProvide

public void Execute(IServiceProvider serviceProvider)

4. IpluginExecutionContext has been changed to a type of service and to get the reference use the following syntax.

IPluginExecutionContext context = (IPluginExecutionContext)
serviceProvider.GetService(typeof(IPluginExecutionContext));

5. ICrmService has been changed to IOrganizationService

IOrganizationServiceFactory serviceFactory =
IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

6. Dynamic entity has been changed to “Entity”

if (context.InputParameters.Contains("Target") &&
context.InputParameters["Target"] is Entity)
{
Entity entity = (Entity)context.InputParameters["Target"];
}


7. Dynamic Entity’s properties syntax has been changed to attributes

entity.Attributes.Contains("accountnumber")

8. Strongly typed classes (account, contact) are missing in Microsoft.XRM.Sdk assembly but there is an exception to this.

9. CRM 2011 provides the crm utility called “crmsvcutil” to generate strongly typed classes that can be used in plugins.

10. Strongly typed classes has been changed to use like loosely typed classes.
Task task = new Task();
Task[“description”] = ”test description”

11. No more soap exception
catch (FaultException ex)

Plugin Development in 2011

1. Create a class library project in vs2010.
2. Sign the assembly by using the Signing tab of the project's properties sheet.
3. Add references to microsoft.crm.sdk.proxy , Microsoft.xrm.sdk ,
System.ServiceModel and System.Runtime.Serialization.
4. Here is code for this tutorial. This plugin will check if the account number
field is empty, create a task for the user to fill this up.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
// Microsoft Dynamics CRM namespace(s)
using Microsoft.Xrm.Sdk;
using System.ServiceModel;

namespace CRMPlugin1
{
public class createNote:IPlugin
{
public void Execute(IServiceProvider serviceProvider)
{

// Obtain the execution context from the service provider.
IPluginExecutionContext context = (IPluginExecutionContext)
serviceProvider.GetService(typeof(IPluginExecutionContext));


// The InputParameters collection contains all the data passed in the message request.
if (context.InputParameters.Contains("Target") &&
context.InputParameters["Target"] is Entity)
{
// Obtain the target entity from the input parmameters.
Entity entity = (Entity)context.InputParameters["Target"];



try
{
//check if the account number exist

if (entity.Attributes.Contains("account number") == false)
{

//create a task
Entity task = new Entity("task");
task["subject"] = "Account number is missing";
task["regardingobjectid"] = new EntityReference("account",new Guid(context.OutputParameters["id"].ToString()));

//adding attribute using the add function
// task["description"] = "Account number is missng for the following account. Please enter the account number";
task.Attributes.Add("description", "Account number is missng for the following account. Please enter the account number");



// Obtain the organization service reference.
IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);


// Create the task in Microsoft Dynamics CRM.
service.Create(task);


}
}

catch (FaultException ex)
{
throw new InvalidPluginExecutionException("An error occurred in the plug-in.", ex);
}

}

}
}//end class
}//end name space


5. Compile the code and register it on the registration tool provided with sdk.
6. Register it on the post create event of account entity.
Note :check the sdk for instructions to register a plugin

Sql server Integration Service

using the ssis(sql server integration service) we can easily intergrate the sql table with a excel sheet
sql table is source and excel sheet is destination

by running the new ssis import export wizard we can easily create the ssis integration project
in this it will automatically create the excel file in your specified path and it will be integrating with that file

problem in this is if we are again running the integration project means it will show erroror it
will create duplicate data in the destination excel for that we can add a script task before all the things and

we can write the code to delete the existing excel file so that our ssis project  wil be working smoothly


in C# we have a class for manipulating all the file operations ..


  System.IO.File

you can write the code like this in the script task



if(System.IO.File.Exist("path\filename.xls"))
{
   System.IO.File.Delete("path\filename.xls");
}