Lesson 12: Advance of Caml Query

using System;
using System.Diagnostics;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebPartPages;
using Microsoft.SharePoint.WebControls;
using System.ComponentModel;
using System.Xml.Serialization;
using Microsoft.SharePoint.Utilities;

namespace ProMoreOfCAML
{
    
    [DefaultProperty("Text"),
        ToolboxData("<{0}:MoreOfCAML runat=server></{0}:MoreOfCAML>"),
        XmlRoot(Namespace = "ProMoreOfCAML")]
    public class MoreOfCAML : WebPart
    {
        Literal literalError = null;
        GridView gridView = null;
        protected override void CreateChildControls()
        {
            literalError = new Literal();
            this.Controls.Add(literalError);
            gridView = new GridView();
            this.Controls.Add(gridView);
            try
            {
                Button buttonChoice = new Button();
                buttonChoice.Text = "Choice";
                buttonChoice.Click += new EventHandler(buttonChoice_Click);
                this.Controls.Add(buttonChoice);

                Button buttonLookup = new Button();
                buttonLookup.Text = "Lookup";
                buttonLookup.Click += new EventHandler(buttonLookup_Click);
                this.Controls.Add(buttonLookup);

                Button buttonLookupType = new Button();
                buttonLookupType.Text = "Lookup";
                buttonLookupType.Click += new EventHandler(buttonLookupType_Click);
                this.Controls.Add(buttonLookupType);
               
                Button buttonUser = new Button();
                buttonUser.Text = "User";
                buttonUser.Click += new EventHandler(buttonUser_Click);
                this.Controls.Add(buttonUser);

                Button buttonDate = new Button();
                buttonDate.Text = "Date";
                buttonDate.Click += new EventHandler(buttonDate_Click);
                this.Controls.Add(buttonDate);

                Button buttonYourDate = new Button();
                buttonYourDate.Text = "Your Date";
                buttonYourDate.Click += new EventHandler(buttonYourDate_Click);
                this.Controls.Add(buttonYourDate);

            }
            catch (Exception ex)
            {
               
                literalError.Text = ex.Message;
             
                
            }
           
        }
        void buttonYourDate_Click(object sender, EventArgs e)
        {

            string listName = "Tasks";
            string camlQuery = string.Format("<Where><Eq><FieldRef Name='DueDate'/><Value Type='DateTime'>{0}</Value></Eq></Where>", ConvertToISO8601Date("2010-06-06"));
            DataTable dataTable = GetDataTableFromList(listName, camlQuery, false, false);
            gridView.DataSource = dataTable;
            gridView.DataBind();
        }
        string ConvertToISO8601Date(string dateAsString)
        {
            string dateToConvert = dateAsString;
            DateTime date = DateTime.Parse(dateToConvert);
            dateToConvert = SPUtility.CreateISO8601DateTimeFromSystemDateTime(date);
            return dateToConvert;
        }
        void buttonDate_Click(object sender, EventArgs e)
        {

            string listName = "Tasks";
            string camlQuery = string.Format("<Where><Eq><FieldRef Name='Created'/><Value Type='DateTime'>{0}</Value></Eq></Where>", "<Today/>");
            DataTable dataTable = GetDataTableFromList(listName, camlQuery, false, false);
            gridView.DataSource = dataTable;
            gridView.DataBind();
        }
        void buttonUser_Click(object sender, EventArgs e)
        {

            string listName = "Tasks";
            string camlQuery = string.Format("<Where><Eq><FieldRef Name='Author'/><Value Type='User'>{0}</Value></Eq></Where>", @"HKGVIRTUAL\HKG");
            DataTable dataTable = GetDataTableFromList(listName, camlQuery, false, false);
            gridView.DataSource = dataTable;
            gridView.DataBind();
        }
        void buttonChoice_Click(object sender, EventArgs e)
        {
           
            string listName = "Tasks";
            string camlQuery = string.Format("<Where><Eq><FieldRef Name='Priority'/><Value Type='Choice'>{0}</Value></Eq></Where>", "(2) Normal");
            DataTable dataTable = GetDataTableFromList(listName, camlQuery, false, false);
            gridView.DataSource = dataTable;
            gridView.DataBind();
        }

        void buttonLookup_Click(object sender, EventArgs e)
        {

            string listName = "Tasks";
            string camlQuery = string.Format("<Where><Eq><FieldRef Name='Province' LookupId='TRUE'/><Value Type='Integer'>{0}</Value></Eq></Where>", 2);
            DataTable dataTable = GetDataTableFromList(listName, camlQuery, false, false);
            gridView.DataSource = dataTable;
            gridView.DataBind();
        }

        void buttonLookupType_Click(object sender, EventArgs e)
        {

            string listName = "Tasks";
            string camlQuery = string.Format("<Where><Eq><FieldRef Name='Province'/><Value Type='LookUp'>{0}</Value></Eq></Where>", "Ha Noi");
            DataTable dataTable = GetDataTableFromList(listName, camlQuery, false, false);
            gridView.DataSource = dataTable;
            gridView.DataBind();
        }

        #region GetDataTable
        DataTable GetDataTableFromList(string listName, string camlQuery, bool isDocument, bool isCalendar)
        {
            DataTable dataTable = null;
            try
            {

 

                SPSite spSite = SPControl.GetContextSite(Context);
                using (SPWeb spWeb = spSite.OpenWeb())
                {
                    SPList spList = spWeb.Lists[listName];
                    SPQuery spQuery = new SPQuery();
                    spQuery.ViewFields = "<FieldRef Name='Title'></FieldRef><FieldRef Name='Status'></FieldRef><FieldRef Name='Priority'/><FieldRef Name='Province'></FieldRef>";
                    spQuery.Query = camlQuery;
                    //literal.Text += spQuery.Query;
                    SPListItemCollection spListCollection = spList.GetItems(spQuery);
                    dataTable = spListCollection.GetDataTable();
                }
            }
            catch (Exception ex)
            {
                literalError.Text = ex.Message;

            }
            return dataTable;
        }
        DataTable GetDataTableFromCalendarList(string listName, string camlQuery, bool isDocument, bool isCalendar)
        {
            DataTable dataTable = null;
            try
            {

 

                SPSite spSite = SPControl.GetContextSite(Context);
                using (SPWeb spWeb = spSite.OpenWeb())
                {
                    SPList spList = spWeb.Lists[listName];
                    SPQuery spQuery = new SPQuery();
                    //spQuery.ViewFields = "<FieldRef Name='Title'></FieldRef><FieldRef Name='Author'></FieldRef><FieldRef Name='Location'></FieldRef>";
                    spQuery.Query = camlQuery;

                    if (isDocument)
                        spQuery.ViewAttributes = "Scope=\"Recursive\"";
                    else
                    {
                        if (isCalendar)
                        {
                            //Calendar
                            spQuery.CalendarDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
                            spQuery.ExpandRecurrence = true;
                        }
                    }
                    //literal.Text += spQuery.Query;
                    SPListItemCollection spListCollection = spList.GetItems(spQuery);
                    dataTable = spListCollection.GetDataTable();
                }
            }
            catch (Exception ex)
            {
                literalError.Text = ex.Message;

            }
            return dataTable;
        }
        DataTable GetDataTableFromDocument(string listName, string camlQuery, bool isDocument, bool isCalendar)
        {
            DataTable dataTable = null;
            try
            {

 

                SPSite spSite = SPControl.GetContextSite(Context);
                using (SPWeb spWeb = spSite.OpenWeb())
                {
                    SPList spList = spWeb.Lists[listName];
                    SPQuery spQuery = new SPQuery();
                    //spQuery.ViewFields = "<FieldRef Name='Title'></FieldRef><FieldRef Name='Author'></FieldRef><FieldRef Name='Location'></FieldRef>";
                    spQuery.Query = camlQuery;

                    if (isDocument)
                        spQuery.ViewAttributes = "Scope=\"Recursive\"";
                    else
                    {
                        if (isCalendar)
                        {
                            //Calendar
                            spQuery.CalendarDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
                            spQuery.ExpandRecurrence = true;
                        }
                    }
                    //literal.Text += spQuery.Query;
                    SPListItemCollection spListCollection = spList.GetItems(spQuery);
                    dataTable = spListCollection.GetDataTable();
                }
            }
            catch (Exception ex)
            {
                literalError.Text = ex.Message;

            }
            return dataTable;
        }
        #endregion
    }
}