3.4. Improved Storage with Lists
There are several techniques using lists that make storage easier or more functional for your needs. The following sections cover using folders, cross-list queries, and indexing.
3.4.1. Using Folders on All Lists Option
In the past, users could organize document libraries by using folders inside of the list, but this could only be done with document libraries in Windows SharePoint Services 2003. It is now possible to have folders on all list types simply by activating the option.
Start by going to the Settings of the list.
Select the Advanced Settings option.
Under the List Advanced Settings, you will find the Folders section. To enable folders on the document library, select Yes under the Folders section. This will add the ability to have the New Folder option under the New menu of the list.
3.4.2. Indexing Lists
SharePoint now enables you to create an index on a list in order to improve performance when retrieving list items. In previous versions of SharePoint, it could be difficult to store large lists without having an impact on the performance of the site itself. Even though 2000 items per folder was large for some lists, it was not enough to use the lists as a real storage source for some external applications that needed much larger lists.
To solve this problem, SharePoint now allows lists to be indexed, allowing for the storage and use of much larger lists. To activate a column as an indexed column, navigate to the Settings page of the list and then select the Indexed columns from the Columns selection area.
Once you are on the Indexed Columns page, select the column that should be indexed. Keep in mind that a few resources will be used for every column that is being indexed, so try to keep it reasonable.
3.4.3. Using Cross-List Queries
A Cross-List query is a way to query a list that could be in the same site or another site and get back the meta data related to that list. This can come in useful when you need to display information about a list that is located in another site such as an HR department displaying information about a list in accounting. To make the retrieval of list data easier across multiple lists, the SPSiteDataQuery object can be used to bring back a collection of items. To perform the same operation in previous versions of SharePoint required the creation of custom code to bring back all of the list items individually from each list. To perform the query, the SPWeb object is used with a method called GetSiteData that passes in the SPSiteDataQuery object and returns a DataTable with the list items selected.
Follow these steps to enable the user to use cross-queries:
Using the CAML markup language set the list types that will be searched through. Every list has a numeric value that is used to define the list.
SPWeb site = SPContext.Current.Web; SPSiteDataQuery query = new SPSiteDataQuery(); query.Lists = "<Lists ServerTemplate="107" />"; DataTable dtResults = site.GetSiteData(query);
To define a query that will return the list items requested, first create a query in CAML.
NOTE
To help create CAML statements, community members have created a CAML Builder at www.u2u.info/SharePoint/U2U%20Community%20Tools/Forms/AllItems.aspx and a CAML viewer at www.codeplex.com/SPCamlViewer.
SPWeb site = SPContext.Current.Web; SPSiteDataQuery query = new SPSiteDataQuery(); query.Lists = "<Lists ServerTemplate="107" />"; query.Query = "<Where>" + "<Eq>" + "<FieldRef Name="Status" />" + "<Value Type="Choice">Not Started</Value>" + "</Eq>" + "</Where>"; DataTable dtResults = site.GetSiteData(query);
Because you are searching the entire site for information, the list data could be very large. The RowLimit property helps limit the size of data that is returned to you.
SPWeb site = SPContext.Current.Web; SPSiteDataQuery query = new SPSiteDataQuery(); query.Lists = "<Lists ServerTemplate="107" />"; query.Query = "<Where>" + "<Eq>" + "<FieldRef Name="Status" />" + "<Value Type="Choice">Not Started</Value>" + "</Eq>" + "</Where>"; query.RowLimit = 10; DataTable dtResults = site.GetSiteData(query);
The ViewFields property allows you to specify the fields that will be part of the list of items returned. This way, only the fields that are important are returned, without all of the other columns that don't matter. This also helps limit the amount of data returned to the user.
SPWeb site = SPContext.Current.Web; SPSiteDataQuery query = new SPSiteDataQuery(); query.Lists = "<Lists ServerTemplate="107" />"; query.Query = "<Where>" + "<Eq>" + "<FieldRef Name="Status" />" + "<Value Type="Choice">Not Started</Value>" + "</Eq>" + "</Where>"; query.RowLimit = 10; query.ViewFields = "<FieldRef Name="Title" />"; DataTable dtResults = site.GetSiteData(query);
The Webs property allows you to manage the scope in which the query executes. The query is set to the current web site when created, but can be changed based on the Webs property. The current web site is determined from the method GetSiteData of the current SPWeb object. There are two possible values that can be used with this option: recursive and SiteCollection.
Recursive—Instructs the query to search all web sites below the current web site object.
SiteCollection—The query will search all locations that are in the same site collection as the web site object.
The following code demonstrates how you can limit the scope of your query just to the SiteCollection. This allows you to get data only from the locations that are required.
SPWeb site = SPContext.Current.Web; SPSiteDataQuery query = new SPSiteDataQuery();
query.Lists = "<Lists ServerTemplate="107" />";
query.Query = "<Where>" +
"<Eq>" +
"<FieldRef Name="Status" />" +
"<Value Type="Choice">Not Started</Value>" +
"</Eq>" +
"</Where>";
query.RowLimit = 10;
query.ViewFields = "<FieldRef Name="Title" />";
qry.Webs = "<Webs Scope='SiteCollection' />";
DataTable dtResults = site.GetSiteData(query);