Wednesday, July 29, 2009

Accessing multiple lists across multiple sites - SPSiteDataQuery

Sometimes, we may require to access multiple lists in multiple websites using a single query. Sharepoint provides two ways to achieve this-
1. joined subview using sharepoint designer ( click here to see)
2. Using SPSiteDataQuery class
SPSiteDataQuery class can be used to run CAML query across multiple lists in multiple websites within a site collection. It can be defined as-
SPSiteDataQuery query = new SPSiteDataQuery();
SPSiteDataQuery class returns a DataTable Class which can be used to access the data.
SPSiteDataQuery class has few properties which need to be set in order to use it. These properties are-

a. Lists- Can be used to specify list type, lists which has to be used in query through ListID, Maximum number of lists can be used in query etc. List Type is specified by BaseType attribute. The value of BaseType attribute will be
0 for a generic list
1 for a document library
3 for a discussion forum
4 for a survey
5 for a issues list.
The Lists property can be set as
query.Lists="<Lists BaseType="'0'/">";
If we want to query some specific lists only then we must specify their list ID as below-
<List ID=\"E8A71D9E-DDB6-4A2A-985C-976FCFBBDD23\"/>
to get the list ID go to list setting page take the value of 'List' querystring from page URL. It will be like-

replace %7B with '{', %7D with '}' and %2D with a '-' and we will have the list ID as
The maximum number of lists that the query can use will be specified by MaxListsLimit attribute as below-
<Lists BaseType='0' MaxListsLimit='10'>

b. Webs Property- This will specify the scope of the query. It may have any of the three values-

1. SiteCollection- query will run through the whole site collection.
query.Webs = "<Webs Scope="'SiteCollection'/">";

2. Recursive- query will run within website and its child site.
query.Webs = "<Webs Scope="Recursive">";

3. Blank- query will run through current web only.
query.Webs = "<Webs Scope="'">";

c. ViewFields property- It can be used to specify the column we are going to display. It can be used a follow-
query.ViewFields = "<FieldRef Name='Department' Nullable='TRUE' /><FieldRef Name='desc' Nullable='TRUE' />"
If we set the value Nullable='TRUE' then the query will fetch the data even if the column has null value in the corresponding row.

d. Query property- It is used to specify the CAML query as below-
query.Query="<Where><Eq><FieldRef Name="'ID'"><Value Type=" 'Counter'"> 1</Value></Eq></Where>";

e. RowLimit- This property will limit the number of rows which has to be fetched.
It can be used as-
query.RowLimit=5; ( To limit the number of rows to 5)

Below is the sample code to use SPSiteDataQuery-

SPWeb web = SPControl.GetContextWeb(Context);

SPSiteDataQuery q = new SPSiteDataQuery();

query.Query = "<Where><Eq><FieldRef Name='ID' /><Value Type='Counter'>1</Value></Eq></Where>";

query.ViewFields = "<FieldRef Name='Department' Nullable='TRUE' /><FieldRef Name='desc' Nullable='TRUE' /><FieldRef Name='DepartmentLink' Nullable='TRUE' />";

query.Lists = "<Lists BaseType='0'><List ID=\"E8A71D9E-DDB6-4A2A-985C-976FCFBBDD23\"/><List ID=\"B59FC80A-B586-4F31-A376-2FE20896641D\"/><List ID=\"3482EBF1-A2A7-4420%2DA920-09EA2635C557\"/></Lists>";

query.Webs = "<Webs Scope='SiteCollection'/>";

query.RowLimit = 10;

DataTable t = web.GetSiteData(query);
The DataTable will be populated by the data fetched from the list. It can be used to display the contents on the web page.

Wednesday, July 22, 2009

Displaying data from more than one list- Joined subview in SharePoint designer

When we works with sharepoint designer, generally DataView is used to display content from sharepoint lists/libraries. We can easily display contents from a single list. But what if we have to display contents from multiple lists? Dataview has ability to display data from multiple lists. We need to create new data source which will have data from multiple lists. We need to follow some steps to accomplish this-
1. open the site from sharepoint designer.
2. Select data source library from right task pane and and then click on 'create a new linked source'. Below is the screen shot-

3. In data source properties dialogue box, click on configure linked source.
4. A new box will appear as below. Select the lists/library that you want to link and then click on next.

5. In the linked data source wizard, there will be two options- merging and joining. Now select the second option i.e. join the contents and then click finish and then Ok. Below is the screen shot-

Now our data source has been created.

6. Now open the page where you want to display the data. Add a dataview control from dataview menu to the content region.

7. Now click on newly created data source and then select show data as below-

8. After clicking on show data, it will display all the columns in the data source. select the columns from first list which you want to display on dataview. Drag and drop the selected column to the dataview control-

9. The selected column will be inserted in dataview inside TD tag. Now suppose you wants to display data from second list on the right. Then right Click on TD in designer and then select insert cell to the right as below-

10. Now Select the desired columns from second list and then select "insert as joined subview" from top as below-

11. A join subview dialog box will be open. Here we will columns from the two list which have matching data as here I used ID column-

After selecting the column click on Ok. Now the selected column will be inserted in the newly created TD tag.

Its done now. It will display data from the two list based on the value of matching column. The look and feel of the subview might not be perfect but it can be updated by doing a little modification in HTML which is generated for the joined subview and by applying custom CSS classes.

Tuesday, July 7, 2009

MySIte Customization

In Sharepoint, a user can have his own personal site where he can add his personal information, documents and other related information. Sharepoint dont have any built-in mechanism to customize the look and feel of MySite template. If we wants to customiza it through Sharepoint designer then we will have to customize it for each user seperately. What if we wants to have a custom template for MySite common to all users? We will have to change its master page. The master page for MySite can be found at
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\GLOBAL\ application directory with name default.master.
we can open it in Sharepoint designer and customize it to give look and feel. But this is not a recommended approach. If we will modify the default.master, then we will not be able to use default view of mysite anymore as we are customizing the master page from site definition.
An alternate and recommended way to do it is feature stapling. In feature stapling, we can create a custom master page and replace that with default.master at run time using the feature.

The site templates for MySite are-

  • SPSPERS (private view)

  • SPSMSITEHOST (public view)

The master page for these two templates need to be replaced with our custom master page. I found a great blog by sridhar to achieve the feature stapling-

Using this blog I was able to customize the look and feel of master page.
Now my next task was to add a content editor webpart in default.aspx. The solution for this is a bit tricky. I had to play with ONET.XML to add the webparts.

Here is the steps I followed

1. Open the ONET.XML file from the location - C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\SiteTemplates\SPSPERS\XML\ using some editor.( dont forget to take a backup of the same.)
2. Find the Modules tag and then tag 'AllUsersWebPart' .
3. Remove all the lines inside tag 'AllUsersWebPart' and add the following lines-< /a>"> Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c Microsoft.SharePoint.WebPartPages.ContentEditorWebPart Use for formatted text, tables, and images. 1 None true true true /_layouts/images/mscontl.gif true < xmlns="< a href=">< /a>" /> < xmlns="< a href=">"><div><TABLE< /a> style="WIDTH: 100%" summary=""><TBODY><TR><TD>&nbsp;Put your title here</TD></TR><TR><TD>&nbsp;your picture here</TD></TR><TR><TD>enter your detail</TD></TR></TBODY></TABLE></div>< /content> < xmlns="< a href=">< /a>" />

If you dont want to see the other webpart, you can comment or remove them.
4. save and close the ONET.XML
5. Do an IIS reset.

Thats it. Now the default.aspx will display content editor webpart.

Adding custom links
To add custom left links and remove the links coming on the left, do the following-

Open ONET.XML and find 'navbar' tags. 'navbar' tag is for links coming on left. this tag has 'name' attribute, the value of this attribute (prefixed by dollor) is coming from resource file core.resx. we can find this file at location- C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\Resources\.
So when we add our custom link, we will have to add them in resource file also. tag also have attributes ID and URL. give an unique ID value in ID attribute and give URL of the page to the attribute URL. suppose I am adding a link Home. then entry would be-

For this, the entry in the resource file would be-
Name -- category_MySiteHome
value-- Home

Its fine. Now comment the existing tag and add your custom link by adding new entry. dont forget to put an entry in core.resx file for each tag you have added. Now save and close the ONET.XML. do an IIS Reset. your custom link will be visible on the default.aspx page of MySIte.

'Home' will be the text appeared on the link.

Adding built-in page for custom links

Now we have created custom link. we would like to have a custom page which will be linked to the custom link we have created.
Create an ASP.Net Application. Add a page home.aspx into the application. In the aspx file, put proper HTML and webpart zone as you want to be on the page. If you are adding a zone then add 'webpartpages:webpartzone' tag instead of 'asp:webpartzone' tag. you may add the additional functionality and events on code behind file. give a strong name to the project and build it.
Now we need a feature which will be used to deploy this page into mysite.

First create feature.xml file. open it in a editor and add the following-

< ?xml version="1.0" encoding="utf-8" ?>
< id="A28CE77C-7E79-49e1-867E-AF4280466BA7" title="custom pages for mysite" description="" xmlns="" scope="Web" hidden="false" version="">
< location="element.xml">
< location="home.aspx">
< /elementmanifests>
< /feature>

Now Create element.xml file. Open it in an editor and add the following-

< ?xml version="1.0" encoding="utf-8" ?>
< xmlns="">
< url="">
< url="home.aspx" name="home.aspx" type="Ghostable">
< /File>
< /Module>
< /Elements>

Now create a folder say- MySitePages. Put feature.xml,element.xml and home.aspx file into it. put this folder in C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\FEATURES\ directory. Take the DLL of your ASP.Net application and put it into GAC.

Now we may use stsadm -o installfeature and stsadm -o activatefeature utility to deploy and activate the feature. we can activate the feature at site level after creating mysite.

After activating the page will be attached to the mySite.
Its done now. :)