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.
