Tuesday, January 3, 2012

Sample Code using DataTable and spsitedataquery.

using System;
using System.Data;
using Microsoft.SharePoint;
//using Microsoft.SharePoint.Library;
using Microsoft.SharePoint.Administration;
namespace ConsoleApplication2
{
    class ConsoleApplication2
    {
        static void Main(string[] args)
        {
            using (SPSite site = new SPSite("http://agmsm:9999/"))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    SPSiteDataQuery query = new SPSiteDataQuery();
                    // Get IDs for all fields used in the query.
                    string assignedToId = SPBuiltInFieldId.AssignedTo.ToString("B");
                    string taskDueDateId = SPBuiltInFieldId.TaskDueDate.ToString("B");
                    string titleId = SPBuiltInFieldId.Title.ToString("B");
                    string taskStatusId = SPBuiltInFieldId.TaskStatus.ToString("B");
                    string percentCompleteId = SPBuiltInFieldId.PercentComplete.ToString("B");
                    // Define the data selection.
                    string where = "<Where><Eq>";
                    where += "<FieldRef ID='" + assignedToId + "' />";
                    where += "<Value Type='Integer'><UserID/></Value>";
                    where += "</Eq></Where>";
                    // Define the sort order.
                    string orderBy = "<OrderBy>";
                    orderBy += "<FieldRef ID='" + taskDueDateId + "' />";
                    orderBy += "</OrderBy>";
                    // Set the query string.
                    query.Query = where + orderBy;
                    // Query task lists.
                    query.Lists = "<Lists ServerTemplate='107'/>";
                    // Specify the view fields.
                    query.ViewFields = "<FieldRef ID='" + titleId + "' />";
                    query.ViewFields += "<FieldRef ID='" + taskDueDateId + "' Nullable='TRUE' />";
                    query.ViewFields += "<FieldRef ID='" + taskStatusId + "' Nullable='TRUE' />";
                    query.ViewFields += "<FieldRef ID='" + percentCompleteId + "' Nullable='TRUE' />";
                    // Query all Web sites in this site collection.
                    query.Webs = "<Webs Scope='SiteCollection'>";
                    // Run the query.
                    DataTable results = web.GetSiteData(query);
                    // Print the results.
                    Console.WriteLine("{0, -10} {1, -30} {2, -30} {3}", "Date Due", "Task", "Status", "% Complete");
                    foreach (DataRow row in results.Rows)
                    {
                        // Extract column values from the data table.
                        string dueDate = (string)row[taskDueDateId];
                        string task = (string)row[titleId];
                        string status = (string)row[taskStatusId];
                        string percentComplete = (string)row[percentCompleteId];
                        // Convert the due date to a short date string.
                        DateTime dt;
                        bool hasDate = DateTime.TryParse(dueDate, out dt);
                        if (hasDate)
                            dueDate = dt.ToShortDateString();
                        else
                            dueDate = String.Empty;
                        // Convert the PercentComplete field value to a percentage.
                        decimal pct;
                        bool hasValue = decimal.TryParse(percentComplete, out pct);
                        if (hasValue)
                            percentComplete = pct.ToString("P0");
                        else
                            percentComplete = "0 %";
                        // Print a line.
                        Console.WriteLine("{0, -10} {1, -30} {2, -30} {3, 10}", dueDate, task, status, percentComplete);
                    }
                }
            }
            Console.ReadLine();
        }
    }
}

No comments:

Post a Comment