U2U CAML Query Builder for SharePoint 2010?

U2U CAML query builder is a great tool for SharePoint developer. I’ve used this tool frequently with SharePoint 2007 development. However, I’ve got into trouble with using the tool with SharePoint 2010. The tool is not updated for SharePoint 2010 yet. Though you can use U2U CAML builder with SharePoint 2010 (shown later in this post), you want to use new features of CAML (say join lists) and such new features are not supported in CAML builder. In this post I’ll show how you can generate CAML query for SharePoint 2010 using few tools and Linq query. First let’s discuss on how you can still use CAML builder to generate CAML for SharePoint 2010.

 

Use CAML Builder with SharePoint 2010 (I’ll not recommend)

Till now the CAML builder doesn’t support SharePoint 2010. However, you can make the tool working by using connecting via web service as shown below:

Figure 1: Use CAML builder with SharePoint 2010

 

However, you are not saved. As I mentioned already till the date I’m writing this post, the CAML builder is not updated to support SharePoint 2010. So new CAML features (like list joins) will not work in this old CAML builder.

 

Generate CAML from Linq-to-SharePoint (Better approach)

Though you can use old CAML builder with SharePoint 2010, since the CAML builder is not updated yet, you will not get the CAML new features when you will use CAML builder. Worried? Please don’t. There’s an way out. Let me explain

1. Download CKS Visual Studio Extension: There’s an useful and handy Visual Studio Extension for SharePoint developers known as Community Kit for SharePoint (CKS): Development tool edition. You can download the extension for SharePoint Server or for SharePoint Foundation.
   
   
2. Generate Entity classes from SharePoint site: Once you have installed the Community Kit for SharePoint, you can generate entity classes from Server Explorer. First open a SharePoint project in Visual Studio and then connect to the SharePoint server from Server Explorer ==> SharePoint Connections. Then right click on your web and click “Generate entity classes” as shown below. This will generate the entity classes in the current selected project of Visual Studio.
   
   
   Figure 2: Generate entity class from SharePoint site/web
   
   
   
3. Write Linq using Linq-to-SharePoint against generated entity classes: Once you have generated the entity classes as described on step2, you can use Linq to SharePoint to write your logic that you want to achieve through CAML. Once you write the Linq, you can run the code and log the CAML generated from the Linq. For example I have two lists Orders and Product. I want to join two lists to get order title and product name. The Linq will look like as shown below:
   
   

   using (var dataContext = new MySiteDataContext(siteUrl))
   {
       TextWriter textWriter = new StreamWriter(@"c:\caml.txt",false);
       dataContext.Log = textWriter;
       var result = from o in dataContext.Orders
                       join p in dataContext.Product on o.Product.Id equals p.Id
                       select new {OrderName = o.Title, p.ProductName};
       foreach (var v in result)
       {
           System.Console.WriteLine("{0}----{1}",v.OrderName,v.ProductName);
       }
   }
   Code snippet 1: Linq query using Linq to SharePoint

   
   
   As shown in the code snippet above, I have used the data context generated at step 2 and I have run a Linq query against two lists of the data context. The most import thing to notice in above code snippet is marked with yellow. I have instantiated a text writer (initialize with file stream) and then I had set it as the Log property of the datacontext. This will ensure that any CAML generated from the Linq query will be written in the writer. Once the query gets executed the CAML is dumped in the file (in my case C:\Caml.txt).
   
   So for generating any complex CAML query you can first write its equivalent Linq query and then get the CAML from log.
   
   
   
4. Get the CAML from the Linq query: After running the Linq to SharePoint query, you have got the CAML query in the log file as shown in step 3. However, you need to work a bit to make the CAML usable in SPQuery. The CAML generated from code snippet 1 is shown below:
   
   
   

   <View>
     <Query>
       <Where>
         <And>
           <BeginsWith>
             <FieldRef Name="ContentTypeId" />
             <Value Type="ContentTypeId">0x0100</Value>
           </BeginsWith>
           <BeginsWith>
             <FieldRef Name="ProductContentTypeId" />
             <Value Type="Lookup">0x0100</Value>
           </BeginsWith>
         </And>
       </Where>
       <OrderBy Override="TRUE" />
     </Query>
     <ViewFields>
       <FieldRef Name="Title" />
       <FieldRef Name="ProductProductName" />
     </ViewFields>
     <ProjectedFields>
       <Field Name="ProductProductName" Type="Lookup" List="Product" ShowField="ProductName" />
       <Field Name="ProductContentTypeId" Type="Lookup" List="Product" ShowField="ContentTypeId" />
     </ProjectedFields>
     <Joins>
       <Join Type="INNER" ListAlias="Product">
         <Eq>
           <FieldRef Name="Product" RefType="ID" />
           <FieldRef List="Product" Name="ID" />
         </Eq>
       </Join>
     </Joins>
     <RowLimit Paged="TRUE">2147483647</RowLimit>
   </View>

   Code snippet 2: CAML generated from Linq query of Code Snippet 1
   
   In the above code snippet, the lines marked with yellow can be modified if you want. Specially the content type in where part is put in the CAML to ensure only list items are selected. FYI, Content type 0x0100 means list item type. 
   
   
   
5. Use CAML query in SPQuery: Now you have got the CAML and you want to use the CAML in SPQuery. The following code snippet shows how I’ve used the CAML(from code snippet 2) in SPQuery:
   
   

   SPQuery query = new SPQuery();
   query.Query = @"<Where>
                       <And>
                       <BeginsWith>
                           <FieldRef Name='ContentTypeId' />
                           <Value Type='ContentTypeId'>0x0100</Value>
                       </BeginsWith>
                       <BeginsWith>
                           <FieldRef Name='ProductContentTypeId' />
                           <Value Type='Lookup'>0x0100</Value>
                       </BeginsWith>
                       </And>
                   </Where>
                   <OrderBy Override='TRUE' />";
   
   query.ViewFields = @"<FieldRef Name='Title' />
                       <FieldRef Name='ProductProductName' />";
   query.ProjectedFields = @"<Field Name='ProductProductName' Type='Lookup' 
                                    List='Product' ShowField='ProductName' />
                             <Field Name='ProductContentTypeId' Type='Lookup' 
                                    List='Product' ShowField='ContentTypeId' />";
   query.Joins = @"<Join Type='INNER' ListAlias='Product'>
                   <Eq>
                       <FieldRef Name='Product' RefType='ID' />
                       <FieldRef List='Product' Name='ID' />
                   </Eq>
                   </Join>";
   
   query.RowLimit = 2147483647;
   
   var list = web.Lists["Orders"];
   var items = list.GetItems(query);
   foreach (SPListItem item in items)
   {
       System.Console.WriteLine("{0}--{1}", item["Title"], item["ProductProductName"]);
   }

   Code Snippet 3: Using generated CAML in SPQuery
   
   

Integrating the Test Code in your Visual Studio Soltuion

You may need to generate the CAML from time to time in your development life cycle. My personal preference is to keep a devtest project in the Visual Studio solution to do work like this CAML generation. I keep a dev-test project (used for RnD like task), mainly console app, in the solution. So you can keep the dev-test project in your solution and in that project you can write the Linq to SharePoint query and generate the CAML. Since the project will always be in your Visual Studio solution, if you need to get the CAML anytime you can just write the Linq query in the dev-test project and run the project to get the CAML.

Conclusion

So here’s the summary on how to generate CAML query using the method described in this post:

• Install Visual Studio extension - CKS for SharePoint Server or for SharePoint Foundation.
• Generate entity classes from SharePoint site using the CKS feature
• Write Linq to SharePoint query and log the CAML in a file/Console
• Use the CAML in SPQuery

Though you can use Linq to SharePoint instead of CAML, but sometimes CAML is needed for raw query and this method will help you find out the CAML.

SharePoint 2010: Configure Form Based Authentication (FBA)

I had worked with form based authentication in SharePoint 2007. However, in SharePoint 2010, there’s few changes in the way form based authentication works. In my another post “Form-Based Authentication with ADAM”, I had described how to implement ADAM form based authentication in SharePoint 2007. Today I’ll show you how you can implement Form Based authentication using Active Directory Lightweight Directory Service. From windows server 2008, ADAM is replaced by Active Directory Lightweight Directory Service and both are LDAP based.

 

FBA works for only for Claims based authentication sites

In SharePoint 2010 to use FBA, you need to create a web application with Claims based authentication as shown below. Form based authentication will not work for web application created with classical authentication.

Figure 1: Create web application in claims based authentication

 

If you don’t create the web application with Claims Based Authentication then you’ll find the Forms Authentication type disabled in Authentication Provider settings window as shown below:

Figure 2: Forms authentication is disabled for web application created with “Classic Mode Authentication”

 

Step 1: Create a web application with Claims Based Authentication

Since form based authentication doesn’t work with “Classical Mode Authentication”, you can’t configure form based authentication with web application created with “classic mode authentication”. So to configure Form based authentication you need to have an web application created with Claims based authentication. (If you want to use windows authentication now and have plan to use forms based authentication later, then the best will be to create the web application with Claims based authentication). FYI,

• Creating a Claims based authentication will allow you to use both windows and form authentication.
• Creating a site with classic authentication mode will not allow you to configure the site to use form authentication easily.

 

Summary: In this step we’ll create an web application using Claims Based Authentication but use windows authentication as shown below. Later we’ll configure the site to use form authentication.

Figure 3: Create Claims based authentication web application with only windows authentication enabled

 

Step 2: Add membership provider entries in web.config files

In this example I’m considering you have the member provider configured already. I’ve been used “Active Directory Lightweight Directory Service” to test this form authentication. You need to modify three different web.config files (your web application, central admin and STS config file). Modifications to the three files are adding two entries (providers, connectionstring) to web.config file which are described below for three different places:

Web Application’s web.config:

You need to put the following entries in the web.config file of your web application under Configuration node:

<connectionStrings>
    <add name="MyProviderConnectionString" 
         connectionString="LDAP://myserver/O=a,OU=b,C=c" />
</connectionStrings>

Code Snippet 1: Connection String to LDAP

 

Then find the <membership> node under <system.web> and add an entry for your provider (There should be an entry with name i, added by SharePoint already). As shown below I’ve added a provider “MyProvider” in the providers list. The provider with name “i" was already in the web.config file which is added by SharePoint when you create an web application with claims based authentication.

<membership defaultProvider="i">
  <providers>
    <add name="i" 
         type="Microsoft.SharePoint.Administration.Claims.SPClaimsAuthMembershipProvider, Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />
    <add name="MyProvider" 
         type="System.Web.Security.ActiveDirectoryMembershipProvider,
System.Web,Version=2.0.0.0,Culture=neutral,PublicKeyToken=b03f5f7f11d50a3a" 
         connectionStringName="MyProviderConnectionString" 
         connectionUsername="CN=aa,CN=Admins,O=a,OU=b,C=c" 
         connectionPassword="***" 
         enableSearchMethods="true" 
         connectionProtection="None" />
  </providers>
</membership>

Code Snippet 2: My custom provider (MyProvider) added alongside the default SharePoint provider (i).

 

SharePoint by default add the provider with name ‘i’. I’ve defined my provider with name MyProvider and the provider is using MyProviderConnectionString. so your web.config file will look like as shown below:

Figure 4: ConnectionString and Provider defined in web.config.

Security Token Service’s web.config file

You need to add the same entries for two other web.config files. One is central admin web.config file. Another one is Security Token Service (STS). You can find the STS web config file as shown below. The default location is “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\SecurityToken\Web.config”.

Figure 5: Security Token Service (STS) web.cofig from IIS (Content View)

 

After opening the config file, add the two entries as shown in code snippet 1 and code snippet 2.

Central Admin’s web.config file

Finally you need to open the web.config file of central administration and add the two entries shown in code snippet 1 and 2.

 

Step 3: Change the web application’s security settings to use Form Authentication

So in Step 2, you have added the provider information (connection string, provider name, username etc) in three different web.config files. Now you need to tell the web application to use Form based authentication by connecting through your provider defined in web.config file. To do so follow the steps below:

1. Login to central administration site and click “Application Management” from left side navigation.
2. Select your web application from web application list and click “Authentication Providers” from ribbon as shown below:

   

   Figure 6: Configure Authentication Provider from Central Administration

3. From the “Authentication providers” dialog click on the zone (Default, internet etc) you want to configure the form authentication and then you will be redirected to “Edit Authentication” page.
4. In the Edit Authentication page, Put your provider name as shown below. You can enable both form and windows authentication if you want. As shown in the read in the image below, if you don’t configure windows authentication in any zone of the web application then crawling will be disabled. If you want you can enable anonymous login from this “Edit Authentication” page.

Figure 7: Enable Form authetication

 

Step 4: Assign/change site collection administrators for the site collections

As soon you change the authentication type to form, you will have to assign an user (from your provider defined in web config file) to the site collection administrators.

1. Click Application Management ==> Change Site Collection Administrators
2. Then add the users from your providers in site collection administrator's group as shown below:

Figure 8: Add/Edit Site collection administrators

If you enable both windows and form authentication then it’ll be better to use one site collection administrator from windows and another from your form based authentication’s provider.

 

You are done!

And if you have followed the steps, you are done. If you try to access the site, you will be prompted for form login page. However, if you enabled both form and windows authentication then you will prompted for authentication  type first and based on the authentication type either you will be prompted for form or windows authentication.

 

For Your Information

Few points to notice here:

• Form based authentication only works for web application created with Claims Based Authentication mode.
• You need to modify three web.config files (your web application, central web app and Security Token Service) to add your provider settings.
• Recommendation is to create an web application with Claims based Authentication mode but using windows authentication. Once you modify those three web.config files, switch the web app to form based.
• You can enable both windows and form authentication in a web application. In that case try to add one site collection administrator from form authentication provider and another from windows.
• If you want the site data to be crawled, then you need to make sure at lease one zone in the web application uses windows authentication.

SharePoint 2010: Add favicon icon to site

In SharePoint if you need to add favicon, you can do so easily by using SharePoint Out-of-box control SPShortcutIcon. The following code snippet shows how you can set the favicon:asdf

<SharePoint:SPShortcutIcon runat=”server” IconUrl=”YourIconUrl”/> 

You are done…

Awarded Microsoft MVP

The very good news for me that I’ve come to know on 1st January is that I’ve been awarded Microsoft MVP for SharePoint Server: Development. My blog has got much popularity in last few months. On November there were almost 10000 visitors and on December around 9000 visitors visited my blog. Thanks all the visitors of the site for their comments and feedback. I’ll continue my efforts to help SharePoint community with blogs, MSDN forms etc.

Thanks again to all visitors who commented and provided feedback in my blog.