SharePoint List GetDataTable Extension Methods

In SharePoint there's a SPList.Items.GetDataTable() method returning a DataTable with all list items. However the DataTable doesn't include all hidden fields. This may cause problems if you need to access some of those hidden fields. For example in one of our SharePoint solutions, we have custom caching mechanism relying on SPListItems' last Modified time stored in the hidden Modified field.

To resolve the problem I wrote some extension methods, and all SharePoint list data, no matter hidden or not, will be returned with a simple call of SPList.GetDataTableWithHiddenFields().

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.SharePoint;

public static class SPListExtension
{
    private const uint SPQueryItemLimit = 2000;

    /// <summary>
    /// Get a .NET type for SPField
    /// </summary>
    private static Type GetFieldType(SPField field)
    {
        Type type = Type.GetType("System.String");

        SPFieldType fieldType = field.Type;
        switch (fieldType)
        {
            case SPFieldType.Integer:
            case SPFieldType.Counter:
                type = Type.GetType("System.Int32");
                break;
            case SPFieldType.Text:
            case SPFieldType.Note:
            case SPFieldType.Choice:
            case SPFieldType.Lookup:
            case SPFieldType.Boolean:
                type = Type.GetType("System.String");
                break;
            //case SPFieldType.Boolean:
            //    type = Type.GetType("System.Boolean");
            //    break;
            case SPFieldType.DateTime:
                type = Type.GetType("System.DateTime");
                break;
            case SPFieldType.Number:
            case SPFieldType.Currency:
                type = Type.GetType("System.Double");
                break;
            default:
                type = Type.GetType("System.String");
                break;
        }
        return type;
    }

    /// <summary>
    /// Remove IDs from lookup fields
    /// "1;#CODE1" => "CODE1"
    /// Skip for LookupMulti fields
    /// </summary>
    public static string TryGetLookupCode(string value)
    {
        string reval = value;
        if (!string.IsNullOrEmpty(value))
        {
            string[] valueSplit = value.Split(new string[] { ";#" }, 
                     StringSplitOptions.RemoveEmptyEntries);
            int number;
            if (valueSplit.Length == 2 && int.TryParse(valueSplit[0], out number) && number >= 0)
            {
                return valueSplit[1];
            }
        }
        return reval;
    }

    /// <summary>
    /// Extension method for generating DataTable based on SPList schema
    /// </summary>
    public static DataTable GetDataTableSchema(this SPList list)
    {
        string[] exludedColumns = new string[] { };
        return GetDataTableSchema(list, exludedColumns);
    }

    /// <summary>
    /// Dynamically create a DataTable based on SPList schema
    /// </summary>
    public static DataTable GetDataTableSchema(this SPList list, string[] excludedColumns)
    {
        bool hasExcludedColumns = (excludedColumns != null && excludedColumns.Length > 0);
        DataTable dataTable = new DataTable(list.Title);
        foreach (SPField field in list.Fields)
        {
            Type fieldType = GetFieldType(field);
            if (!hasExcludedColumns || !excludedColumns.Contains(field.InternalName, 
                StringComparer.InvariantCultureIgnoreCase))
            {
                dataTable.Columns.Add(field.InternalName, fieldType);
            }
        }
        return dataTable;
    }

    /// <summary>
    /// Extension method to get DataTable from SPList with hidden fields
    /// </summary>
    public static DataTable GetDataTableWithHiddenFields(this SPList list)
    {
        string[] exludedColumns = new string[] { };
        return GetDataTableWithHiddenFields(list, exludedColumns);
    }

    /// <summary>
    /// SPList.Items.GetDataTable doesn't include hidden fields
    /// This extension method returns all fields' data
    /// </summary>
    public static DataTable GetDataTableWithHiddenFields(this SPList list, string[] excludedColumns)
    {
        if (list.ItemCount == 0)
            return null;

        bool hasExcludedColumns = excludedColumns != null && excludedColumns.Length > 0;
        DataTable dataTable = new DataTable(list.Title);
        Dictionary<string, SPFieldType> fieldDict = new Dictionary<string, SPFieldType>();
        List<SPListItemCollection> allListItems = new List<SPListItemCollection>();

        // Generate DataTable 
        foreach (SPField field in list.Fields)
        {
            if (!hasExcludedColumns || !excludedColumns.Contains(field.InternalName, 
                StringComparer.InvariantCultureIgnoreCase))
            {
                fieldDict.Add(field.InternalName, field.Type);
                Type type = GetFieldType(field);
                dataTable.Columns.Add(field.InternalName, type);
            }
        }

        // Query data
        SPQuery spQuery = new SPQuery();
        spQuery.RowLimit = SPQueryItemLimit;
        spQuery.ViewAttributes = "Scope=\"Recursive\"";
        do  // Get all list items 
        {
            SPListItemCollection itemCollection = list.GetItems(spQuery);
            if (itemCollection != null && itemCollection.Count > 0)
                allListItems.Add(itemCollection);
            spQuery.ListItemCollectionPosition = itemCollection.ListItemCollectionPosition;
        } while (spQuery.ListItemCollectionPosition != null);

        // Populate DataTable with query results
        Type stringType = Type.GetType("System.String");
        foreach (SPListItemCollection listItems in allListItems)
        {
            foreach (SPListItem item in listItems)
            {
                DataRow dr = dataTable.NewRow();
                foreach (DataColumn column in dataTable.Columns)
                {
                    string columnName = column.ColumnName;
                    if (!Convert.IsDBNull(item[columnName]) && item[columnName] != null)
                    {
                        SPFieldType fieldType = fieldDict[columnName];
                        if (fieldType == SPFieldType.Boolean)
                        {
                            dr[columnName] = Convert.ToBoolean(item[columnName]) ? "1" : "0";
                        }
                        else if (fieldType == SPFieldType.Lookup || fieldType == SPFieldType.Invalid)
                        {
                            dr[columnName] = TryGetLookupCode(item[columnName].ToString());
                        }
                        else if (column.DataType == stringType)
                        {
                            dr[columnName] = item[columnName].ToString();
                        }
                        else
                        {
                            dr[columnName] = item[columnName];
                        }
                    }
                }
                dataTable.Rows.Add(dr);
            }
        }
        return dataTable;
    }
}

Note: the optional "excludedColumns" parameter defines the fields that you don't want to include in the DataTable.