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 += "
//filter
if (!string.IsNullOrEmpty(this.camlQueryOpportunityStatus))
if (!string.IsNullOrEmpty(queryString)) //will need
{
queryString = "
}
else
queryString += "
//filter web part added
if (!string.IsNullOrEmpty(webpartFilter))
{
if (!string.IsNullOrEmpty(queryString)) //will need
{
queryString = "
}
else
queryString += "
}
if (!string.IsNullOrEmpty(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
}
}