Introduction

SharePoint comes out of the box with several default Lists: Announcements, Tasks, Events, Contacts etc. It also allows end users to create Lists of their own, modify existing Lists by adding / removing columns and create different views on a List. Lists are a very powerful feature of SharePoint and provide quick ad-hoc data storage capabilities to the end user. Out of the box Sharepoint also provides various pages for each List to add, remove, edit and view data which is contained in the List. As Lists are so important, and they directly interact with the end user, you will invariably come across various requirements to program against the data stored in a SharePoint List. At first it may seem like a daunting task as you may not have any idea how SharePoint stores this data internally, which tables it uses to store the items and how it keep track of changes and updates.

Luckily for us, SharePoint provides a List API to programmatically access the Lists. There is absolutely no need to connect to the SharePoint databases directly. Accessing and modifying the SharePoint databases directly is never a recommended practice and is also not supported by Microsoft. However, the List API provided by Microsoft provides a lot of functionality to develop against the SharePoint Lists – query, update and edit functionality for the data stores in existing SharePoint Lists. There is also a web service, which you can use to connect to the various Lists, but I shall be examining the List API in this article.

The API classes to access and manipulate the SharePoint Lists are housed inside the SPList class. It contains all the classes required to connect and manipulate SharePoint Lists. You will need to include the Microsoft.SharePoint namespace in your project in order to program against the SPList class.

If you are wondering if this article is for SharePoint Portal Server or Windows SharePoint Services, I would like to clarify that Lists are the building blocks of both these products and hence are available in both of them. The same code will run on Portal Server as well as Windows SharePoint Services.

System Requirements

To run the code for this sample you should have:

• Windows SharePoint Services 2.0 or SharePoint Portal Server 2003


• The .NET Framework version 1.1


• Visual Studio.NET 2003

Installing and Compiling the Sample Code

The sample download for this article contains a VS.NET 2003 solution with the following projects:

• ASPToday: This project consists of one Web Part called TaskList which is discussed in the article.


• ASPTodayInstaller: This project creates a CAB file to install the Web Part.

To install the sample, you can either install the Web Part manually using the .dwp file and the dll built from the ASPToday project, or you can deploy using the stsadm command line using the CAB file generated with the ASPTodayInstaller project.

To install using the CAB, you need to go to the command prompt and run the stsadm utility found at C:Program FilesCommon FilesMicrosoft Sharedweb server extensions60BIN with the following command line.

stsadm -o addwppack -filename 
  c:ASPTodayASPTodayInstallerDebugASPTodayInstaller.CAB

The path to the CAB file will differ on your machine according to where the project is built. This will deploy the Web Part to your Virtual Server Gallery in SharePoint, and you can drag and drop it to the page where you want to add the Web Part.

There are two useful options which can be used to deploy the Web Part along with the above command line.

• globalinstall : This option can be used to install the Web Part into the Global Assembly Cache (GAC). You will need to secure sign your Web Part assembly first in order to be able to install in the GAC.


• force : This will overwrite any previous installation of this Web Part replacing the old assembly with the new one.

If the site where this Web Part is added does not contain a Tasks List, go into Documents & Settings, and create a new Tasks List called Tasks. This Web Part requires that a Task List called Tasks exist in the site where it is added.

The Problem

In order to explore the SPList API, I shall be building a sample solution as stated above. You should be aware of the standard SharePoint Tasks List which comes out of the box. From the standard view, you can add items to the List, edit items, and view all tasks. The entire process to edit the status of any task consists of, clicking on the item, selecting edit, selecting the new status, and clicking save. This is a bit cumbersome and hence the goal is to provide a SharePoint Web Part which will reduce the number of clicks to achieve this task. Along the way you will learn how to program against the SPList class. I leave the addition of the items as default, from the standard UI pages.

The Solution

The first goal of this Web Part will be to read all the tasks from the Tasks List of the current web and display it in a grid. For this, you will need to connect to the SharePoint List using the SharePoint API.

First I will quickly look at some of the important methods and properties available in the SPList class in order to provide some background. The code for this article shows the use of some of them and prepares groundwork to connect and query a List. You can easily extend it using any of the methods or properties below to suit your requirements. However, this is not an exhaustive list, I have just listed items which are very widely used in general application scenarios. You can find the full list of methods and properties for the SPList class from the SharePoint Products and Technologies SDK available from the Microsoft website.

Methods

Method Name	Purpose
GetItemByID()	Gets a particular List Item by its unique ID.
GetItems()	Gets the Items available in the List based on the parameters passed. It can either be used to get Items from a particular View or execute a SPQuery query to retrieve Items based on conditions.
GetView()	Use this method to get a particular View from your List by its ID. Alternatively you can get all Views from the Views collection (explained below).
SaveAsTemplate()	Saves the List as a template in the SharePoint List Template Gallery.

Properties

Property Name	Purpose
DefaultView	This gets a SPView object for the default View of the List.
Fields	Gets a SPFieldCollection object of all the fields available in the List. It also gets all internal fields along with your custom fields.
Hidden	Lets you toggle the status of the List to either hidden or visible.
ID	This is the unique ID which identifies this List in the SharePoint database
ItemCount	Gets a quick count of all the Items in the List.
Items	Gets a SPListItemCollection object representing all of the Items available in the List.
OnQuickLaunch	Specifies if the List will appear on the Quick Launch navigation bar or not.
RootFolder	Gets the root folder of the List.
Title	Allows you to read or change the title of the List.
Views	Gets a SPViewCollection object containing all the views defined for the List, including any user created Views.

First off, you will need to instantiate an object representing the Tasks List. I am presuming that you have added a few tasks in your Tasks List, and will be using this Web Part of that SharePoint web. If you have not added any tasks in the Tasks List, then go ahead and add a few tasks. Hence based on this assumption, the Tasks List which we are interested in will be in the same Web to which this Web Part will be added.

SPWeb currentWeb = SPControl.GetContextWeb(Context);

SPList tasksList = currentWeb.Lists["Tasks"];

This code will get the SPList object which points to the Tasks List. Instead of hardcoding the name of the Task List in the code, you want the user to be able to enter the Task List to use. To enable this, you will implement a Web Part property which the user can set by modifying the properties of the Web Part from the context menu. So, first I add the property definition in the code as follows:

private string _listName;

[Browsable(true),

Category("Miscellaneous"),

DefaultValue(""),

WebPartStorage(Storage.Shared),

FriendlyName("List Name"),

Description("Name of the Task List to use.")]

public string ListName

{
  get
  {
    return _listName;
  }
  set
  {
    _listName = value;
  }

}

You cannot rely on the user setting this property correctly all the time, and hence some kind of error handling should be done to make sure that the Web Part knows how to react to such scenarios. The simplest option is to check this property for correct values before doing anything.

The following code shows the modified code which now picks up the name of the List from the Web Part property, but also gracefully handles any problems.

SPWeb currentWeb;

currentWeb = SPControl.GetContextWeb(Context);

if (ListName != null && ListName.Length > 0)

{
  try
  {
    tasksList = currentWeb.Lists[ListName];
    if (tasksList.BaseTemplate != SPListTemplateType.Tasks)
    errorMessage = "This webpart only works with Tasks lists. Please enter 
      the name of the task list to use in the settings of this webpart.";
    else
    {
        // We have the correct tasksList object. Do something.
    }
  }
  catch
  {
    errorMessage = "Cannot find the list " + ListName + ". Please enter the 
    name of the task list to use in the settings of this webpart.";
  }

}

else
  errorMessage = "Please enter the name of the task list to use in the 
    settings of this webpart.";

This will check first if the List Name property has been set. If it has been set, then the code checks if it is a valid List name by checking for any exceptions while trying to get a reference to the List object. As the Web Part works only on a Tasks List, I also check if the List object obtained is of the type Tasks by comparing the BaseTemplate of the List object. If everything succeeds, then at the end I have a reference to the Task List and can proceed further to do something with that object.

Now that I have the reference to the SPList object, the next task is to read all the tasks from the List and display them in a grid. This can be achieved in two ways, let me illustrate both of them.

To read the Items in the List once I have a SPList object for our Tasks List, I can enumerate through the Items collection in that List. The code below prints all the Items available in the List.

for (int i=0; i< tasksList.Items.Count; i++)

{
  Context.Response.Write(tasksList.Items[i]["Title"] + " – " +   
  tasksList.Items[i]["Status"]);

}

The goal is the get all the active tasks, so I will have to put some additional code to check if the status is active and then only consider those tasks.

for (int i=0; i< tasksList.Items.Count; i++)

{
  if ((tasksList.Items[i]["Status"]  "Not Started") ||  
  (tasksList.Items[i]["Status"]  "In Progress"))
  {
    // This is our valid task. Do something with it.
  }

}

One useful performance tip found in the code above is that I am directly iterating the taskList.Items to check the individual Items in the collection. This is a bit slower to work with as it is observed that the code hammers the database each and every time you try to iterate over the Items collection. A better way is to store the collection in a local instance, and then instead of hitting the database, the code will cache the entire collection and use that instead.

So in essence, instead of:

SPList tasksList;

tasksList = currentWeb.Lists[ListName];

string status = tasksList.Items[i]["Status"]

You can gain serious performance improvement by just doing this instead:

SPList tasksList;

tasksList = currentWeb.Lists[ListName];

SPListItemCollection myItems;

myItems = tasksList.Items;

string status = myItems[i]["Status"];

Referencing the Items collection through a local variable quickens up the access. This is especially visible when there is an iterative loop which goes through all the Items in a List.

So far, I have determined which Task List to use for the Web Part, gotten an object to that Task List, and then iterated through the Item collection in order it to get to the individual Tasks. However, there is a better way to achieve all this, and I won’t be developing the previous technique any further. I just wanted to use it to demonstrate that you can iterate through the rows and columns of a List.

To get the required data which contains all the active tasks, I shall instead use the SPQuery functionality of SharePoint to form a query and get all the required data in one call. This eliminates the need to manually go through each Item and get the required tasks.

StringBuilder query = new StringBuilder();

query.Append("<Where>");

query.Append("<Or>");

query.Append("<Eq><FieldRef Name=‘Status’ />
  <Value Type=‘String’>Not Started</Value></Eq>");

query.Append("<Eq><FieldRef Name=‘Status’ />
  <Value Type=‘String’>In Progress</Value></Eq>");

query.Append("</Or>");

query.Append("</Where>");

SPQuery sQuery = new SPQuery();

sQuery.Query = query.ToString();

SPListItemCollection items = tasksList.GetItems(sQuery);

foreach (SPListItem item in items)

{
  Context.Response.Write(item["Title"]);
  Context.Response.Write(" – ");
  Context.Response.Write(item["Status"]);
  Context.Response.Write("<br>");

}

The above block of code, forms a query which will get all the items from the Tasks List where the Status is either Not Started or In Progress. Once you have the query string built, all you have to do is call the query from the linksList object which points to the Tasks List. It will return exactly the data you’re interested in. This technique is very handy for retrieving Items from a List based on some conditions, and provides better performance than manually filtering the Items.

Let me explain a bit about SPQuery while I am on this topic: The SPQuery object has a property called Query which essentially takes an XML string defined in Collaborative Application Markup Language (CAML). This XML string is nothing but the where clause of the query. It lets you filter out records based on the conditions specified in the query. In the example above, I have specified to filter out and get records where the Status field matches Not Started or In Progress. When this query is passed to the GetItems() method of the tasksList object, it ends up getting just the records which I am interested in. Instead of looping through hundreds of records in the List, by forming a query, I let SharePoint do the work of filtering out the records and returning just the List of Items which I am interested in. It is not very elegant looking code, but performs much better than looping through the collection manually.

Now that I have the data which I am interested in, my goal is to show it in a grid on the page. So next I modify the code from above to create a dataset from the data, which can easily be bound to a DataGrid.

DataTable table;

StringBuilder query = new StringBuilder();

query.Append("<Where>");

query.Append("<Or>");

query.Append("<Eq><FieldRef Name=‘Status’ />
  <Value Type=‘String’>Not Started</Value></Eq>");

query.Append("<Eq><FieldRef Name=‘Status’ />
  <Value Type=‘String’>In Progress</Value></Eq>");

query.Append("</Or>");

query.Append("</Where>");

SPQuery sQuery = new SPQuery();

sQuery.Query = query.ToString();

SPListItemCollection items = tasksList.GetItems(sQuery);

table = items.GetDataTable();

Now I have a DataTable with exactly the data I need. The rest is easy, I just bind the DataTable to a grid to show the values. To achieve the task of displaying the active tasks and a button next to it to easily toggle the status of that task to completed, I have created my grid structure as follows:

tasksGrid = new DataGrid();

tasksGrid.AutoGenerateColumns = false;

tasksGrid.DataKeyField = "ID";

tasksGrid.ID = "TasksGrid";

tasksGrid.DeleteCommand +=
  new DataGridCommandEventHandler(tasksGrid_SetComplete);

BoundColumn activeTasks =    new BoundColumn();

activeTasks.DataField = "Title";

activeTasks.HeaderText = "Active Tasks";

activeTasks.HeaderStyle.Font.Bold = true;

tasksGrid.Columns.Add(activeTasks);

ButtonColumn selectButton = new ButtonColumn();

selectButton.CommandName = "Delete";

selectButton.Text = "Done";

selectButton.ButtonType = ButtonColumnType.LinkButton;

tasksGrid.Columns.Add(selectButton);

Controls.Add(tasksGrid);

As you can see from the above code, I have created a DataGrid with two columns, one to show the Title of the task, and another to show a link button which fires the DataGrid‘s delete event handler. One thing to notice in the code is that I have set the DataKeyField to ID. This is because to uniquely identify any item in a List, you need to know the ID of that item. This is obtained from the ID field in the DataTable. I will store the ID obtained from the List and bind it to the datagrid using the DataKeyField attribute. When I get the DeleteCommand event for the grid, I will be getting the ItemIndex which will tell me which row in the grid received the DeleteCommand event. From that ItemIndex I can retrieve the List ID for that corresponding row by using the DataKeys collection of the grid.

int ID = (int)tasksGrid.DataKeys[e.Item.ItemIndex];

Thus I now have the ID of the corresponding List Item which I need to update. The next activity is to go and update the list with the new status value.

The code below shows the delete event handler, where I have written the code to set the status of the corresponding task to Completed.

private void tasksGrid_SetComplete(object source, DataGridCommandEventArgs e)

{
  int ID = (int)tasksGrid.DataKeys[e.Item.ItemIndex];
  SPListItem item = tasksList.GetItemById(ID);
  item["Status"] = "Completed";
  // Call update to save changes
  item.Update();
  BindData();

}

Any changes you have made to your List via various methods of the SPList class are not committed to the database until you call the Update() method. Hence make sure you call the Update() method to push the changes made to your List into the SharePoint database.

This is one of the many possible solutions to this problem. For the sake of clarity and ease of explanation, I did not choose a complex solution, but there is nothing preventing you from extending this further, for example by adding a checkbox instead of a Done link, and allowing for multiple status changes in one button click, or rendering drop down boxes instead of the check boxes, to select different status values. You can modify the code to show only the tasks which are assigned to the current user if need be.

This article has provided you with the basics on how to connect and use the SharePoint List API thru .NET code. Furthermore, I have seen many projects use database tables to store some data which could have easily been stored in a SharePoint List. This also enables you to provide easy data editing capabilities if you wish, via the standard List editing pages, or secure the List so that no one can modify it directly. Thus now you have another way to store data for your custom Web Parts where you have a need to store some data. The decision on whether to use SharePoint Lists or a database table would differ on a case to case basis, but SharePoint Lists do provide a quick and easy alternate way of data storage for your SharePoint applications.

Conclusion

The entire List API is well documented in the SDK documentation under the SPList class. It is worth having a look, as it provides a lot of functionality to further interact with SharePoint Lists. You can effectively use Lists as an effective data storage medium for your Web Parts. Also, as SharePoint Lists automatically get a standard data insertion and editing pages, end users can easily manipulate these Lists for entering various data in them, and you can write Web Parts to act on the data. This opens up lots of new possibilities for your solutions.