Tjassens SharePoint Blog

Customers always want to be in control. Thats something I learned during my time as a SharePoint consultant. Because SharePoint has unlimited posibilities most customers don’t listen to objections such as not using SharePoint lists as databases. Of course this was exactly what I had to do and I will show you how I did it.

First of all I used as starting point for this article that you have to know how to programmatically (or through the UI) create lists with lookup columns. For my customer I had to create 5 lists of which 1 had a lookup to another list which also had a lookup to another list.

Using a list as a database is not that bad…when talking about a simple list with a couple of columns and without a custom styling. But what if the customer wants to have a custom style and doesnt want data from one list, but 5 lists with multiple lookups? Which also groups the rows and columns? What to do then?

The answer is the dataForm WebPart. Creating a dataForm WebPart from code is easy enough.

dataFormWebPart = new DataFormWebPart();
dataFormWebPart.Title = "MyDataFormWebPart";
dataFormWebPart.ChromeType = PartChromeType.None;

With this you created a datafromwebpart with a custom title and a chrometype set to ‘none’. The wonderful part of the dataformwebpart is that styling is easy. Why i say easy, is because linking custom styling to the webpart is easy:

dataFormWebPart.XslLink = "myCustomXSL.xsl";

Creating a high quality and clear structured XSL is a whole other matter (which I will not discuss in this post).

Adding the dataSource to the dataFormWebPart is done by setting the property on the dataform webpart:

dataFormWebPart.DataSources.Add(dataSource);

To create a good datasource for the dataFormWebPart a lot more work has to be done. During my time spend with the dataformwebpart I had a lot of difficulties getting the datasource together. I eventually created a method to create a datasource based on a SPList.

Private SPDataSource GetDataSource(string dataSourceId, string webUrl, SPList list, string query, uint maximumRow)
{
SPDataSource dataSource = new SPDataSource();
dataSource.UseInternalName = true;
dataSource.ID = dataSourceId;
dataSource.DataSourceMode = SPDataSourceMode.List;
dataSource.List = list;
dataSource.SelectCommand = "" + query + "";
Parameter listIdParam = new Parameter("ListID");
listIdParam.DefaultValue = list.ID.ToString(
"B").ToUpper();
Parameter maximumRowsParam = new Parameter("MaximumRows");
maximumRowsParam.DefaultValue = maximumRow.ToString();
QueryStringParameter rootFolderParam = new QueryStringParameter("RootFolder", "RootFolder");
dataSource.SelectParameters.Add(listIdParam);
dataSource.SelectParameters.Add(maximumRowsParam);
dataSource.SelectParameters.Add(rootFolderParam);
dataSource.UpdateParameters.Add(listIdParam);
dataSource.DeleteParameters.Add(listIdParam);
dataSource.InsertParameters.Add(listIdParam);
return dataSource;
}

This method can then be used to create a SPdatasource of a list by doing the following. Below you’ll see that the maximum row and the query(executed on the list) can be added to create the datasource.

//List1 data source
SPList List1 = curWeb.Lists[Constants.Lists.List1_listName];
string queryList1 = "<Query><Where><IsNotNull><FieldRef Name='Title' /></IsNotNull></Where><OrderBy><FieldRef Name='ManagementAgendaOrder' Ascending='True' /></OrderBy></Query>";
uint maximumRowList1 = 100;
SPDataSource dataSourceList1 = GetDataSource(Constants.Lists.List1_listName, curWeb.Url, listList1, queryList1, maximumRowList1);

After creating the dataSources for all the lists which are needed in the webpart the next step is to put all these datasources together and add it to the dataformwebpart. This can be done by creating an aggregated datasource.

AggregateDataSource dataSource = new AggregateDataSource();
                dataSource.SeparateRoot = "true";
                dataSource.Sources.Add(dataSourceList1);
                dataSource.Sources.Add(dataSourceList2);
                dataSource.Sources.Add(dataSourceList3);
                dataSource.Sources.Add(dataSourceList4);
                dataSource.Sources.Add(dataSourceList5);
                dataSource.Aggregate = @"
                        <concat name=""source"">
                            <datasource name=""List1"" id=""0"" Type=""SPList""/>
                            <datasource name=""List2"" id=""1"" Type=""SPList""/>
                            <datasource name=""List3"" id=""2"" Type=""SPList""/>
                            <datasource name=""List4"" id=""3"" Type=""SPList""/>
                            <datasource name=""List5"" id=""4"" Type=""SPList""/>
                        </concat>";

dataFormWebPart.DataSources.Add(dataSource);

The last nice thing I found out is that its possible to pass variables related to the list or list item you are showing to the XSL. the property on the dataformwebpart related to this is parametersbinding. It is used like this (in this case I posted a pageurl of a detailpage to a list view):

dataFormWebPart.ParameterBindings = BuildDataFormParameters(PageUrl);

To build the parameters for passing the variable I simply created a dataform webpart in SharePoint Designer and looked at the parameters they passed along. This led me to the following method:

private string BuildDataFormParameters(string PageUrl)
{
StringBuilder parameters = new StringBuilder("");
parameters.Append("");
return parameters.ToString();
}

This way the variable PageUrl is easy to get in the XSL file by adding the following parameter:

<xsl:param name="PageUrl" />

Well thats all you need to put in there to create a good dataformwebpart. If you have any questions, feel free to ask me!