Monday, August 4, 2008

SPSiteDataQuery

One day you will need to find content in many subsites. You will need to use the SPSiteDataQuery, cos sharepoint, naturally, won't do this with built in functionality. And then the multi-value farce will end the easy way (multi-value columns break sharepoint).

So here is a web part that queries lists on sub-sites for a particular content type.

You will need this post then.

Check out this .cs with all the goodies. Uses caml, creates a dataset, adds other data in, uses xslt to render, etc.



#region Imports

using System;
using System.Web;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls.WebParts;

using System.Runtime.InteropServices;
using System.Configuration;
using System.ComponentModel;
using System.Collections.Generic;

using System.Xml;
using System.Xml.Xsl;
using System.Xml.Serialization;

using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;

using AGA.MOSS.Common;

using System.Reflection;
using System.IO;
using System.Globalization;
using System.Text;
using Microsoft.SharePoint.WebPartPages.Communication;
using System.Collections;
using Microsoft.SharePoint.WebPartPages;


#endregion

namespace AGA.MOSS.Corporate.WebUI.ContentQuery
{
[Guid("AF31E240-BD93-4a55-B240-D533B2953735")]
public sealed class CamlWebPartSiteCollectionContentType : System.Web.UI.WebControls.WebParts.WebPart, INamingContainer, IWebPartField //, ITransformableFilterValues
{
[ConnectionConsumer("Web Part Consumer")]
public void GetWPConnectedProviderInterface(IWebPartField connectProvider)
{
FieldCallback callback = new FieldCallback(ReceiveField);
connectProvider.GetFieldValue(callback);

//string xxx = ((Microsoft.SharePoint.WebPartPages.TransformableFilterValuesToFieldTransformer)(connectProvider)).Transform .ParameterValues[0];
}

public void ReceiveField(object objField)
{
if (objField != null)
{
webpartFilter = Convert.ToString(objField);
}
}

#region Variables

private bool debug = false;

private string camlQueryOpportunityStatus = string.Empty;
private string orderBy = string.Empty;
private bool ascending = false;
private string contentType = string.Empty;
private string webpartFilter = string.Empty;
private string xsltUrl = string.Empty;
private string libraryURL = string.Empty;
private int itemLimit = 0;

private ArrayList connectionParameters = new ArrayList();

private string[] xsltParams = new string[3];

const string OPPORTUNITYCOMPANYNAME = "Company_x0020_Name0";
const string OPPORTUNITYCOUNTRY = "Opportunity_x0020_Country0";
const string OPPORTUNITYSTATUS = "Opportunity_x0020_Status0";
const string OPPORTUNITYRANK = "Opportunity_x0020_Rank0";
const string PROJECTTEAM = "Project_x0020_Team0";

const string RANKIMAGESFOLDER = @"/SiteImages/Rank%20Icons/";
const string COMPANYSITEURL = "CompanySiteURL";
const string OPPORTUNITYCOUNTRYNEWCOL = "OpportunityCountry";
const string PROJECTTEAMNEWCOL = "ProjectTeam";

#endregion

#region Properties

[Browsable(true),
Category("Query"),
DefaultValue(""),
WebBrowsable(true),
WebDisplayName("Show XML, this will show debug data"),
Personalizable(PersonalizationScope.Shared),
Description("Show debug XML")]
public bool Debug
{
get
{
return this.debug;
}
set
{
this.debug = value;
}
}


[Browsable(true),
Category("Query"),
DefaultValue(""),
WebBrowsable(true),
WebDisplayName("Opportunity Status, blank for all"),
Personalizable(PersonalizationScope.Shared),
Description("Enter the status to filter opportunities by.")]
public string Query
{
get
{
return this.camlQueryOpportunityStatus;
}
set
{
this.camlQueryOpportunityStatus = value;
}
}

[Browsable(true),
Category("Query"),
DefaultValue(""),
WebBrowsable(true),
WebDisplayName("Order By Field, blank for none"),
Personalizable(PersonalizationScope.Shared),
Description("Order results by specified field.")]
public string OrderBy
{
get
{
return this.orderBy;
}
set
{
this.orderBy = value;
}
}

[Browsable(true),
Category("Query"),
DefaultValue(""),
WebBrowsable(true),
WebDisplayName("Ascending"),
Personalizable(PersonalizationScope.Shared),
Description("Orders the results in ascending or descending order.")]
public bool Ascending
{
get
{
return this.ascending;
}
set
{
this.ascending = value;
}
}

[Browsable(true),
Category("Query"),
DefaultValue("Opportunities Information"),
WebBrowsable(true),
WebDisplayName("Content Type Filter, blank for all"),
Personalizable(PersonalizationScope.Shared),
Description("The content type to limit by")]
public string ContentType
{
get
{
return this.contentType;
}
set
{
this.contentType = value;
}
}

[Browsable(true),
Category("Query"),
DefaultValue(10),
WebBrowsable(true),
WebDisplayName("'Item limit, 0 for all"),
Personalizable(PersonalizationScope.Shared),
Description("Specifies whether the maximum number of items to display.")]
public int ItemLimit
{
get
{
return this.itemLimit;
}
set
{
this.itemLimit = value;
}
}

//[Browsable(true),
//Category("Query"),
//DefaultValue(""),
//WebBrowsable(true),
//WebDisplayName("Site Collection Url"),
//Personalizable(PersonalizationScope.Shared),
//Description("Url of a site collection")]
//public string SiteCollectionUrl
//{
// get
// {
// return this.siteCollectionUrl;
// }
// set
// {
// this.siteCollectionUrl = value.Trim();

// if (this.siteCollectionUrl.Length > 0)
// {
// this.siteCollectionUrl = this.siteCollectionUrl.Replace(" ", "%20");

// if (this.siteCollectionUrl.ToLower().EndsWith(".aspx"))
// {
// //remove everything from last "/"
// int lastSlash = this.siteCollectionUrl.LastIndexOf(@"/");
// this.siteCollectionUrl = this.siteCollectionUrl.Substring(0, lastSlash + 1); //+1 leaves a slash
// }
// }
// }
//}

[Browsable(true),
Category("Query"),
DefaultValue(""),
WebBrowsable(true),
WebDisplayName("Xslt Url"),
Personalizable(PersonalizationScope.Shared),
Description("Url to the Xslt-file which renders the HTML.")]
public string XsltUrl
{
get
{
return this.xsltUrl;
}
set
{
this.xsltUrl = value.Trim();
}
}

[Browsable(true),
Category("Parameters"),
DefaultValue(""),
WebBrowsable(true),
WebDisplayName("Parameter 1"),
Personalizable(PersonalizationScope.Shared),
Description("Parameter to send to xslt.")]
public string Param1
{
get
{
return this.xsltParams[0];
}
set
{
this.xsltParams[0] = value;
}
}

[Browsable(true),
Category("Parameters"),
DefaultValue(""),
WebBrowsable(true),
WebDisplayName("Parameter 2"),
Personalizable(PersonalizationScope.Shared),
Description("Parameter to send to xslt.")]
public string Param2
{
get
{
return this.xsltParams[1];
}
set
{
this.xsltParams[1] = value;
}
}

[Browsable(true),
Category("Parameters"),
DefaultValue(""),
WebBrowsable(true),
WebDisplayName("Parameter 3"),
Personalizable(PersonalizationScope.Shared),
Description("Parameter to send to xslt.")]
public string Param3
{
get
{
return this.xsltParams[2];
}
set
{
this.xsltParams[2] = value;
}
}

#endregion

#region Constructor

public CamlWebPartSiteCollectionContentType()
{

}

#endregion

#region RenderContents()

protected override void RenderContents(HtmlTextWriter output)
{
// Check if the user has entered the required URL's
try
{
try
{
XmlDocument docXml = new XmlDocument();

docXml = GetXMlDocFromSiteQuery(output); //this gets the base rows, with it we will iterate threw the list values

if (docXml != null)
{
DebugWriteOut(output, docXml);
TransformXMLAndOutputTheResults(output, docXml);
}
else
output.Write("No results found.");
}
catch (System.Net.WebException ex)
{
output.WriteLine("Cannot access the view of the list. Are you authorized?
. Exception: " + ex.Message);
}
catch (Exception exception)
{
output.WriteLine("Error occurred: " + exception.Message + "
");
output.WriteLine("Details: " + exception.ToString() + "
" + exception.StackTrace);
}
}
catch (Exception exception)
{
output.WriteLine("Cannot access the view of the list. Are you authorized?
");
output.WriteLine("Details: " + exception.Message);
}
base.RenderContents(output);
}

private void TransformXMLAndOutputTheResults(HtmlTextWriter output, XmlDocument docXml)
{
string html = "";

if (!string.IsNullOrEmpty(this.xsltUrl))
{
html = TransformXml(docXml);
if (html != string.Empty)
output.Write(html);
}
else
output.Write(docXml.InnerXml); //no transform
}

private XmlDocument GetXMlDocFromSiteQuery(HtmlTextWriter output)
{
DataSet dataSetOfSiteResults = new DataSet();
DataTable dataTableOfSiteResults;
XmlDocument docXml = new XmlDocument();

SPSiteDataQuery q = BuildMainRowQuery();

using (SPSite spSite = new SPSite(SPContext.Current.Site.Url))
{
using (SPWeb spWeb = spSite.OpenWeb())
{
dataTableOfSiteResults = spWeb.GetSiteData(q);
}
}
dataTableOfSiteResults.TableName = "SiteContentTable";

dataSetOfSiteResults.Tables.Add(dataTableOfSiteResults);
dataSetOfSiteResults.DataSetName = "SiteContentDataSet";

if (dataTableOfSiteResults.Rows.Count > 0)
AddMultiLookupsAndUpdateRank(dataSetOfSiteResults);

DataSet sortedDataSet = new DataSet("SiteContentDataSet");
sortedDataSet.Tables.Add(dataTableOfSiteResults.DefaultView.ToTable());

docXml.LoadXml(sortedDataSet.GetXml());

if (this.debug)
output.WriteLine(SPEncode.HtmlEncode(q.ViewFields + q.Webs + q.Query + q.Lists));

return docXml;
}

private void AddMultiLookupsAndUpdateRank(DataSet dataSetOfSiteResults)
{
dataSetOfSiteResults.Tables["SiteContentTable"].Columns.Add(COMPANYSITEURL, System.Type.GetType("System.String"));
dataSetOfSiteResults.Tables["SiteContentTable"].Columns.Add(OPPORTUNITYCOUNTRYNEWCOL, System.Type.GetType("System.String"));
dataSetOfSiteResults.Tables["SiteContentTable"].Columns.Add(PROJECTTEAMNEWCOL, System.Type.GetType("System.String"));

//loop threw all the datatable records
foreach (DataRow dataRow in dataSetOfSiteResults.Tables["SiteContentTable"].Rows)
{
try
{
Guid webId = new Guid(Convert.ToString(dataRow["WebId"]));

using (SPWeb spWeb = SPContext.Current.Site.OpenWeb(webId))
{
//add the url for the country name, canountr name is an anchor
dataRow[COMPANYSITEURL] = spWeb.Url;

Guid listId = new Guid(Convert.ToString(dataRow["ListId"]));
SPList spList = spWeb.Lists[listId];

try
{
dataRow[OPPORTUNITYRANK] = FormatRankString(dataRow[OPPORTUNITYRANK].ToString());
}
catch (Exception) { dataRow[OPPORTUNITYRANK] = FormatRankString(""); }

foreach (SPListItem spListitem in spList.Items)
{
if (spListitem.ID == Convert.ToInt32(dataRow["ID"])) //skip none matching rows
{
try
{
string opportunityCountry = GetTextValuesFromMultiLookupString(spListitem[OPPORTUNITYCOUNTRY].ToString());
dataRow[OPPORTUNITYCOUNTRYNEWCOL] = opportunityCountry;
}
catch (ArgumentException)
{
throw new ArgumentException("Cannot find " + OPPORTUNITYCOUNTRY + " column (or 'Opportunity Country' in List:" + spList.Title);
}
try
{
string projectTeam = GetTextValuesFromMultiLookupString(spListitem[PROJECTTEAM].ToString());
dataRow[PROJECTTEAMNEWCOL] = projectTeam;
}
catch (ArgumentException)
{
throw new ArgumentException("Cannot find " + PROJECTTEAM + " column (or 'Opportunity Country' in List:" + spList.Title);
}
}
}
}
}
catch(Exception)
{
}
}
//sort the view
if (string.IsNullOrEmpty(orderBy))
dataSetOfSiteResults.Tables["SiteContentTable"].DefaultView.Sort = "WebId, ListId";
else
dataSetOfSiteResults.Tables["SiteContentTable"].DefaultView.Sort = OrderByClause();
}

private string FormatRankString(string dataColumnValue)
{
//either web or site
string webUrl = string.IsNullOrEmpty(SPContext.Current.Web.ParentWeb.Url) ? SPContext.Current.Web.Url : SPContext.Current.Web.ParentWeb.Url;
//add the link to the pic depending on where the pic is
if (dataColumnValue == "")
return webUrl + RANKIMAGESFOLDER + "NoRank" + ".jpg";

int index = ((string)dataColumnValue).IndexOf(";");

if(index == -1) //text not multiple value
return webUrl + RANKIMAGESFOLDER + ((string)dataColumnValue) + ".jpg";

if (index == 0)
return webUrl + RANKIMAGESFOLDER + "NoRank" + ".jpg";
else
{
string Rank = ((string)dataColumnValue).Substring(0, index); //finds number in string then converts to number...
return webUrl + RANKIMAGESFOLDER + Rank + ".jpg";
}
}

private string GetTextValuesFromMultiLookupString(string stringToSplit)
{
if (stringToSplit.IndexOf("#") < 1)
return stringToSplit;

string[] stringArray = stringToSplit.Split(new char[] { '#' });
string outPutString = string.Empty; //stringbuilder not better

for (int i = 1; i <= stringArray.GetUpperBound(0); i++) //skip first string
if ((i % 2) == 0)
continue;
else
outPutString += " " + stringArray[i];

return outPutString;
}

private SPSiteDataQuery BuildMainRowQuery()
{
SPSiteDataQuery spSiteDataQuery = new SPSiteDataQuery();

//view fields will include the ones in the filters
spSiteDataQuery.ViewFields = "";

spSiteDataQuery.Lists = "";

spSiteDataQuery.Webs = "";
spSiteDataQuery.Query = BuildCAMLQuery();

if (this.itemLimit > 0)
spSiteDataQuery.RowLimit = Convert.ToUInt32(this.itemLimit);

return spSiteDataQuery;
}

private string BuildCAMLQuery()
{
string queryString = "";

//content type filter
if (!string.IsNullOrEmpty(this.contentType))
queryString += "" + this.contentType.Trim() + "";

//filter
if (!string.IsNullOrEmpty(this.camlQueryOpportunityStatus))
if (!string.IsNullOrEmpty(queryString)) //will need if multiple
{
queryString = "" + queryString + "" + camlQueryOpportunityStatus.Trim() + "" + "";
}
else
queryString += "" + camlQueryOpportunityStatus.Trim() + "";

//filter web part added
if (!string.IsNullOrEmpty(webpartFilter))
{
if (!string.IsNullOrEmpty(queryString)) //will need if multiple
{
queryString = "" + queryString + "" + webpartFilter.Trim() + "" + "";
}
else
queryString += "" + webpartFilter.Trim() + "";
}

if (!string.IsNullOrEmpty(queryString))
queryString = @"" + queryString + "";

return queryString;
}

private string OrderByClause()
{
string orderByClause = this.orderBy + ", WebId, ListId"; //SQL sintax

if (!this.ascending)
orderByClause += " ASC";
else
orderByClause += " DESC";

return orderByClause;
}

private string TransformXml(XmlDocument docXml)
{
string xslPath = MakeFullPath();
XsltArgumentList argsXsl = this.GetXsltArguments();

string html = XMLUtil.TransformXml(docXml, xslPath, argsXsl, base.GetType().Assembly.Evidence, false);
return html;
}

private string MakeFullPath()
{
string xslPath = this.xsltUrl;
if (SPUrlUtility.IsUrlRelative(this.xsltUrl))
{
//Need to add the current server to it
xslPath = SPContext.Current.Site.MakeFullUrl(this.xsltUrl);
}
return xslPath;
}

private void DebugWriteOut(HtmlTextWriter output, XmlDocument docXml)
{
if (this.debug)
output.WriteLine(SPEncode.HtmlEncode(docXml.OuterXml));
}

//private bool canContinue(HtmlTextWriter output)
//{
// // Validation Checks
// if (string.IsNullOrEmpty(this.contentType))
// {
// output.Write("Please configure the Content Type field.");
// return false;
// }

// return true;
//}

#endregion

#region GetXsltDocument()

private XmlDocument GetXsltDocument()
{
XmlDocument docXsl = new XmlDocument();
if (this.xsltUrl.Length > 0)
{
string xml = FileUtil.LoadDocument(this.xsltUrl, this.Context);
docXsl.LoadXml(xml);
}
else
{
Stream manifestResourceStream = Assembly.GetExecutingAssembly().GetManifestResourceStream(base.GetType(), "StandardList.xslt");
docXsl.Load(manifestResourceStream);
}

return docXsl;
}

#endregion

#region GetXsltArguments()

private XsltArgumentList GetXsltArguments()
{
SPWeb contextWeb = SPContext.GetContext(this.Context).Web;

XsltArgumentList args = new XsltArgumentList();
args.AddParam("CurrentSiteUrl", "", contextWeb.Url + "/");
args.AddParam("CurrentPage", "", this.Page.Request.Url.AbsoluteUri);
args.AddParam("lcid", "", contextWeb.Language.ToString());
args.AddParam("DocumentLibraryURL", "", this.libraryURL);

for (int i = 0; i < this.xsltParams.Length; i++)
{
string parameter = (this.xsltParams[i] != null) ? this.xsltParams[i] : string.Empty;
args.AddParam("param" + ((i + 1)).ToString(CultureInfo.InvariantCulture), "", parameter);
}

return args;
}

#endregion

#region IWebPartField Members

public void GetFieldValue(FieldCallback callback)
{
throw new Exception("The method or operation is not implemented.");
}

public PropertyDescriptor Schema
{
get { throw new Exception("The method or operation is not implemented."); }
}

#endregion
}
}