Showing posts with label CAML. Show all posts
Showing posts with label CAML. Show all posts

Wednesday 11 June 2014

CAML Designer for SharePoint 2013 is Brilliant

I always used U2U for checking my CAML queries in SharePoint.  I have just used CAML Designer for SharePoint 2013 and it is brilliant.

Link to the CAML Designer tool
http://karinebosch.wordpress.com/2012/12/13/caml-designer-for-sharepoint-2013-released/

Simple Example of using CAML to query a list:
string riskListCAMLQry  = "<Query><Where><Eq><FieldRef Name='Status' /><Value Type='Text'>Scheduled</Value></Eq></Where><View><RowLimit>1000</RowLimit></View></Query>"

Call using C# CSOM
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = riskListCAMLQry;  
// Add a where clause on the status on the list to restrict the result set, only return the first 1000 rows

Sunday 1 May 2011

SP2010 Data Access options for developers

Overview:  This post contains 4 wmv's that explain both client side and server side data access options for developers.  This was a single pesentation that I have broken up into 4 smaller units of 10-15 minutes each is about 15 Megs in size. 

Part 1 - Overview of Data Access options for SharePoint 2010 developers
Part 2 - Introduction to LINQ to SharePoint
Part 3 - More options
Part 4 - LINQ to SharePoint tips

Sunday 24 April 2011

Referential Integrity between Lists

Problem: SP2010 adds referential integrity between lists using a lookup column.  I wish to push out cascade referential integrity between my lists using declarative CAML to build and link my 2 SharePoint lists.

Initial Hypothesis:  I am going to work through an example to highlight 2 key concepts when deploying a list declaratively using CAML (Using XML which is CAML I am declaratively creating 2 lists namely: Customers and Orders). 
1) I want to display a project field not the ID column that is doing the binding as my lists will make more sense to users updating them.  As shown below:

2)  Setup Cascade referential integrity between the lists (can't be done on the Site Column Lookup).  Referential integrity is done between list instances not on the Site Column.  Therefore, I use a feature receiver to wire up the referential integrity. The result of setting referential integrity is Shown as it will appear in the UI.

There are 3 ways to create lists namely: UI, programmatically or declaratively.  In MOSS the best option for lookup lists was to do it progratically using feature receivers.  My preferred approach is to use the declarative CAML approach.

Tip: The correct way to create a list is to ensure you have followed the 4 steps in this order using CAML: Site Columns, Content Types, List Definition & List Instance.

Tip: CKSDev is an VSIX extension that you can load into Visual Studio 2010 (VS2010) using Microsoft Gallery, it is a codeplex based project that adds functionality to VS2010.  You can use you "Solution Explorer" in VS2010, navigate to the site columns, right click and generate the CAML to declativtively build Content Types and Site columns.  So you can create the content types thru the UI then generate the CAML and now you have a repeatable mechanism of deploying content types and site columns. 

Resolution:
1) Lookup Field (Lookup Site column) should use the customer name not the default ID for linking (This actually links on the customer "ID" but the link displays the "Customers Name".  This is done on the field in the CAML using the attributes "ShowField" and "DisplayName".


2) The second point and title of this posts is about setting referential integrity between lists.  You cannot use the site column declaritive code to create the referential integrity.  The attribute "RelationshipDeleteBehaviour" is used to set the referential integrity behaviour, this can be either None (Default), Restrict or Cascade at a Site Column level.  Using the attribute "RelationshipDeleteBehaviour" does not work as shown below as referential integrity is done between list instances not on the site column.  Why the attribute "RelationshipDeleteBehaviour" is present I have no idea.

Add a new feature to the VS2010 sandbox solution project (Right Click "Features" in "Solution Explorer" and select "Add Feature")
Add the feature Receiver code (Right Click "Feature2" (or whatever you called it), select "Add Event Receiver".
Add the appropriate code to the feature receiver as shown below:
  
Tip:
"You can apply a cascade delete rule. In this case, if a user deletes an item from a parent list, related items in the child list are also deleted. This helps to prevent orphaned items in the child list.
You can apply a restrict delete rule. In this case, users are prevented from deleting an item that is referenced by items in a related list. This helps to prevent broken lookup links in the data model." MSDN

Download:
Download the Visual Studio 2010 project that builds the 2 SharePoint lists in a sandbox solution and adds referential integrity to the list lookup relationship.

More Info:

Wednesday 3 November 2010

Reverse Engineer Lists, Content Types and Site Columns and retrieving the CAML

Problem: You need to create deployable lists, this should be done using a feature.  You can create a lists using the UI and then get the CAML that created the list.  The code will have some hicups but it a good place to start for building your Lists based on List definitions, content types and their corresponding site columns.

Resolution:
Get the CAML and create your feature based on the declaritive CAML
http://%7bwebapplication%7d/%7Bsite%7D/_vti_bin/owssvr.dll?Cmd=ExportList&List={ListGuid}
Thanks to Sahil Malik

Read More:
http://blah.winsmarts.com/2008-2-Dev_Tip__The_SharePoint_University_of_Reverse_Engineering.aspx
http://www.synergyonline.com/blog/blog-moss/Lists/Posts/Post.aspx?ID=24

Download: SharePoint 2010 sandbox project example that creates 2 lists with a lookup field. Updated 24/03/2011


Monday 1 November 2010

Using CAML and SPQuery to query using dates

Problem: Using SPQuery I can't filter using a datatime parameter.
Hypothesis: I can see my custom web part code is formatting the DateTime selection (SharePoint:DateTimeControl) differently in my custom code CAML than the CAML generated by U2U.
Resolution:
Use the Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime() method to get your DateTime control value into the correct format.
SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.UtcNow)

More Info:
http://blogs.msdn.com/b/saurabhkv/archive/2008/05/05/spquery-with-boolean-and-datetime.aspx
http://snahta.blogspot.com/2009/07/spquery-few-important-things.html
http://www.aidangarnish.net/post/Using-SPQuery-and-CAML-to-filter-and-order.aspx

Thursday 5 August 2010

Retrieving Publishing Columns using a CAML Query

Overview: LINQ is easy and strongly type however, occasionally we need to get data in an optimised fashion or LINQ won't do the job i.e. inefficient queries, Publishing fields and we need to revert to a CAML query.

Code Example:

Explanation:
  1. Perform a CAML query, U2U still works on SP2010 and I use it to work out my CAML query.
  2. Add code that performs the query, optimise it (you don't need every field returned by CAML, you can look at the query results without the "ViewFieldOnly" & "IncludeMadatoryFields" setting and you will see how many fields are returned which for big queries isn't good).
  3. Using the SPListItem retrieve the field data from the publishing fields.
Andrew Connell explains how to extend SPMetal
Tobias Zimmergrin's blog has good Linq to SharePoint 2010 info, his blog on showing the CAML generated by LINQ to SharePoint queries is invaluable to work out what SPMetal is generating.
Update: 09/10/2010 Extend SPMetal to retrieve list attachments

Update 27/10/2010 - A re-hash of Tobias Zimmergrin's blog describing retrieving the CAML generated by a LINQ to sharePoint Query.

Saturday 31 July 2010

SharePoint 2010 simple reporting options

Overview: SSRS has it's place but I want to gather reports from a composite application where data is stored in SharePoint lists.
Solutions:
OOTB web parts - UI may be tricky to get right but useful for quick reporting that can be performed by power users for general business users. Use filter, List View (XLVWP), CQWP, data View, chart web parts.
Custom Web parts - either write or buy web parts for querying lists. Custom CAML queries provide a solution to display data. Con is that it's pretty development heavy, inflexible and requires code deployment. SPMetal has issues with joins, publish columns and hidden columns such as "Created by". Also see Ninetix reporting.
Dataviews - Displaying list data using SPD - need SPD access. Joining 2 lists.

Giles Hamson has a good chart explaining reporting options.

Monday 26 July 2010

Problems with SPMetal in SharePoint 2010

Overview: I have been doing a fair amount of LINQ to SharePoint lately and I have found a couple of issues, my vitriolic rants are:
  1. SP metal can run against external content type (BCS) - external content types don't get created by SPMetal;
  2. Hidden fields are not available to the SPMetal created proxy i.e. createdby, modified. Parameters.xml can be changed to display these hidden fields;
  3. Only SharePoint Foundation field types are generated.  Column types are not picked up by SPMetal include the "Managed Metadata columns", "Publishing Html" or "Publishing Image". Additionally any custom created columns are not included by SPMetal;
  4. Anonymous LINQ needs a work around.  Update 27/11/2010, August Cumulative Update (CU) for SharePoint 2010 apparently fixes the anaonymous LINQ to SharePoint issue. Ensure you get the latest CU due to the re-release issues.
  5. Update: 08 Oct 2010 - List attachments are not picked up by SPMetal.  You will need to use the Server side object model or extend SPMetal using a partial class.
  6. Update: 14 Oct 2010 - Multiselect columns are not update-able with multiple values using LINQ to SharePoint.  You can update with 1 value only.
  7. Update 18 Oct 2010 - SPMetal does not like spaces in the url to the site that it generates off. Error the web at 'http://demo.dev/sites/my site' could not be found.
  8. Update 27 Nov 2010 - Using Linq to SharePoint across site collections.  Scope is to the current site collection.

LINQ to SharePoint Posts on this blog
Extending SPMetal for field columns no available to SPMetal by AC (Update) or AC
CAML query for retrieving Publishing HTML and Publishing image columns
Configuring SPMetal default generated code

To see the CAML generated by SPMetal:
StringBuilder sb = new StringBuilder();

System.IO.TextWriter tw = new System.IO.StringWriter(sb);
updatedataContext.Log = tw;
// CAML Query here i.e. var x from customers select customers;
string camlOut = sb.ToString();  // CAML generated.

Update: 09/10/2010 Extend SPMetal to retrieve list attachments

Friday 2 July 2010

Dynamic LINQ to SharePoint 2010

Problem: I have 2 connected Web Parts, the provider provide multiple values for lookup columns. I started with LINQ to SharePoint (SPMetal) but could not build up the query dynamically.
Hypothesis: My initial reaction was to use a dynamic CAML query as done in MOSS using U2U to work out my query. The issue is that the CAML is not safe (as we don't get validation until run-time) but at least I can dynamically build up my query.

Using Dynamic link with LINQ to SharePoint to can achieve the required result. The code is safe as least for run-time (my logic is still dodgy). Only issue is with the performance and the results if the list if large. Using a very broad LINQ-to-Sharepoint query that is converted into a CAML query anyway I get a large result set. I then filter using dynamic LINQ. Pretty heavy filtering and inefficient querying. Throttling concern: If I returned more than 5,000 items (default list throttling limit for SP2010 lists) I now loose results that should be queried (SharePoint would trim my SPMetal query and then Dynamic LINQ would work on the max of 5,000 items). Sure you can turn throttling off as a farm admin but not a great idea. You could programatically override the throttling (SPQueryThrottleOption.Override) using the server OM but this doesn't help for your SPMetal query.
In this case, my best option is to use a dynamic CAML query and get the exact set of data I am looking for.

Tip: Only get the fields you are using in CAML queries.
More Info:
Building CAML queries
Dynamic LINQ - Scott Guthrie

LINQ to SharePoint Posts

Thursday 24 June 2010

Inefficient Queries - SPMetal join issue

Problem: LINQ to SharePoint 2010 (SPMetal) is not querying 2 SharePoint lists, when using SPMetal on a visual web part. I am trying to JOIN 2 lists inside a web part. The Application error message is "The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet.".



Hypothesis: At the SharePoint conference in Vegas Oct 2009 I remember in 1 of the sessions that inefficient LINQ queries are blocked. This was a little vague but a good start noting the stack trace message "InvalidOperationException: The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet.]
Microsoft.SharePoint.Linq.Rules.QueryEfficiencyProcessor.Process(Expression e)"
.
Exception Details: System.InvalidOperationException: The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet.
Using LINQ I can query each of the lists.  Each list has a field of the same type that I am trying to join on. I was totally confused but realised it must be an issue with the efficiency/validity of the query. A simpler query worked so it looks like the CAML is not being correctly formed. I extracted the CAML using the DataContext Log method.
My choices are:
  1. To get and fix the CAML query and then run a CAML query manually in my code;
  2. Use LINQ to objects; or
  3. Perform 2 queries and link the data manually.
Resolution:
Reading a post on MSDN on 2 stage queries gave me a quick fix. By adding the ToList() method, the query works.
var empQuery = from bug in Bugs.ToList()
join emp in Employees on bug.AssignedTo equals emp.Title
select new
{ emp.Title,
Project = bug.Project
};

Explanation: ToList() method forces immediate query evaluation and returns the generic that contains the query result.  As described in the MSDN article above LINQ can't convert the LINQ to SharePoint into a CAML query so by using the ToList() method, the query is broken into 2 stages.  This will apply to queries that use JOINS, UNIONS, and various other LINQ operators as described in the MSDN Unsupported LINQ queries article.

Tip: Turn off Object Change Tracking if you are only reading data.

LINQ to SharePoint Posts on this Blog
Links:
Linq to SharePoint 2010 examples
Tip: CAML query for optimised speed settings, only bring back columns you will use querySearch.ViewFieldsOnly = true;
querySearch.IncludeMandatoryColumns = false;
Display the CAML that LINQ to SharePoint is running

Sunday 6 June 2010

Application Architecture in SharePoint 2010

Problem: I have a client that needs a composite built using 2010. The application is used to manage digital assets for suppliers. Key questions are where to store data and how to access the data.
Hypothesis: Where to store data, previously in MOSS I would of used SQL Server and accessed it via the BDC or custom web parts. In SP 2010 lists have been vastly improved and are now a good option for storing data. The new External Content Type can be used on SQL but goes beyond my requirement.
The key improvements to SharePoint lists are:

  1. Lookup lists are improved and easier to implement;
  2. Query joins and LINQ improve retrieving data;
  3. LINQ gives you strongly typed lists which using the old "Server Object Model" were weakly typed;
  4. LINQ querries are converted to CAML querries and therefore more performant than using the Server object model method and alot cleaner than using CAML to query data;
  5. Formula based validation on fields;
  6. Store level enforcement (MOSS enforce requirements such as null values only at the UI level);
  7. Improved referential integrity between lookup lists.
My choice was between using SQL Server to store the application data and SharePoint lists. SQL tables have the following key advanatages of they are more performant, and are transactionable. The easy of use and adavantages meant that SharePoint lists are the prefered option for storage.
Application for the suppliers and users also has several options however SharePoint provides most of the admin screens. The actual app can be built using application pages or web parts or the method i prefer, the client object model.

Accessing SharePoint Data options to consider are: Client Object Model (Weakly typed), however REST API's are Strongly typed for SP lists only. New in SP2010
Server Object Model (OM) is weakly type lists but offers the most flexibility. The Server OM existed on MOSS but is improved in SP2010. LINQ gives strongly type SP lists that can be read-write (new in SP2010)
Resolution:
Used SP lists to store the digital assets, the ordering data is also stored in SharePoint lists. For speed of development in the prototype I built using application pages using web parts that accessed the lists using LINQ to SharePoint. The final application will be built using the Client OM.

Friday 4 June 2010

Linq to SharePoint overview

Problem: Access data in SharePoint Lists.
Hypothesis:
1.> LINQ for SharePoint is a data access mechanism that allows developers to create SQL like syntax against a data sources. Linq improve performance by allowing the back end data source to be queried using CAML to solve the query. SharePoint 2010 fully supports LINQ for lists so that developers can query SharePoint easily and quickly.
2.> LINQ for SharePoint is also know as SPMetal.
3.> The SPMetal utility generates C# or vb.net code to allow you access to existing SharePoint lists.
4.> You can automate you VS environment using the prebuild cmd prompt to regenerate your Linq data access class for builds.
Steps to work with Linq for SharePoint:
1.> Using the stsadm cmd, create the API to your existing SharePoint App

2.> Import the created Data.cs file into the Visual Studio solution. Fix the namespaces on the file.
3.> Begin Using LINQ to SharePoint.
DataDataContext dc = new DataDataContext(http://intra);
// TODO! - retrieve from the web.config
EntityList Assets;
Assets = dc.GetList("My Assets");
var assetQuery = from asset in Assets
where asset.Id == Assetid
select new{
asset.Title,
asset.Description
};

// Bind to a Grid view

More Info:
Getting started with LINQ for SharePoint 2010
Channel 9