Quickly integrate with Sharepoint 2010

If you need fast access to Sharepoint 2010 through C# you should take a look at the LINQ to Sharepoint and the SPMetal tool. The SPMetal took generates a class library for all or some objects within the Sharepoint object model.

Quick guide:

  1. Open a DOS-prompt on the Sharepoint server
  2. Go to directory ‘C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN’
  3. Type the following command: ‘SPMetal.exe /web:http://mysite /code:SiteEntities.cs’
  4. Add the SiteEntities.cs to you Sharepoint integration project
  5. Create a program for accessing the Sharepoint objects through LINQ

Code example:

public List<Injury> GetInjuries(
             DateTime? fromDate = null, DateTime? toDate = null
    var lst = new List<Injury>();
    SiteEntitiesDataContext context = 
         new SiteEntitiesDataContext("http://mysite");
    if (context != null)
        List result = (
                from c in context.Injuries
                where c.OnDate >= fromDate && c.OnDate < toDate
                select c).OrderBy(x => x.OnDate).ToList();

        foreach (InjuriesItem inj in result)
            var inj = new Injury(
                           inj.OnDate.Value) ;

    return lst;

The class Injury is defined in my integration solution an is not part of the  generated file SiteEntities.cs.

But one obvious disadvantage of this tool is the static behaviour. You need to generate the classes each time you change something to be able to reflect the changes in the integration.


CRM 2011: Invoice status

Here is a useful SQL statement for quickly find the invoice status based on Invoice.StateCode and Invoice.StatusCode. The SELECT will list all invoices for year 2012 where StateCode and StatusCode as both numbers (stored in CRM-database) in addition to the textual description.

	month(createdon) as monthNo, 
		WHEN StateCode = '0' THEN 'Active'
		WHEN StateCode = '2' THEN 'Paid'
		WHEN StateCode = '3' THEN 'Cancelled'
	 END) as StateText, 
		WHEN StatusCode = '1' THEN 'New'
		WHEN StatusCode = '4' THEN 'Billed'
		WHEN StatusCode = '100001' THEN 'Complete'
		WHEN StatusCode = '100002' THEN 'Parial'
		WHEN StatusCode = '100003' THEN 'Canceled'
	) as StatusText,
	count([TotalAmount]) as InvoiceCount
FROM [dbo].[Invoice]
WHERE year(createdon) = 2012
group by month(createdon), StateCode, StatusCode
order by  month(createdon),StateCode, StatusCode ;

The above SQL returns invoice count grouped by the different statuses. An example of the outbut is shown in the screenshot below: