SharePoint CSOM: Get list as a table

The following scripts transform SharePoint list items to a data table.

These are a work in progress, for the most part one of the following should work.

To complete these we would have to correctly parse each possible value type.

Get List Items as DataTable

// Transforms a ListItemCollection to a data table
// Light parsing of values
DataTable GetTable(ListItemCollection items)
{
    var table = new DataTable();

    foreach (ListItem li in items)
    {
        var row = table.NewRow();

        foreach (var kv in li.FieldValues)
        {
            var key = kv.Key;

            if (!table.Columns.Contains(key))
            {
                table.Columns.Add(key);
            }

            var value1 = "";
            if (kv.Value != null)
            {
                value1 = kv.Value.ToString();
            }
			
            if (kv.Value is FieldLookupValue)
            {
                value1 = (kv.Value as FieldLookupValue).ToString();
            }
            else if (kv.Value is FieldUrlValue)
            {
                value1 = string.Format("<a href='{0}'>{1}</a>", (kv.Value as FieldUrlValue).Url, (kv.Value as FieldUrlValue).Description);
            }
            else if (kv.Value is string[])
            {
            	value1 = kv.Value.RawPrint();
            	
            }

            row[key] = value1;
        }

        table.Rows.Add(row);
    }

    table.AcceptChanges();

    return table;
}

Get List Items as DataTable of Strings

// Transform a ListItemCollection to a DataTable
// Every Value is evaluated as a string
DataTable GetTableOfStrings(ListItemCollection items)
{
    var table = new DataTable();

    foreach (ListItem li in items)
    {
        var row = table.NewRow();

        foreach (var kv in li.FieldValuesAsText.FieldValues)
        {
            var key = kv.Key;

            if (!table.Columns.Contains(key))
            {
                table.Columns.Add(key);
            }

            var value1 = "";
            if (kv.Value != null)
            {
                value1 = kv.Value.ToString();
            }
            row[key] = value1;
        }

        table.Rows.Add(row);
    }

    table.AcceptChanges();

    return table;
}

Get List Items as DataTable (XmlSerialization)

// XML Serialiation of values
// Useful to know value type
// Very slow
DataTable GetTableOfSerializedValues(ListItemCollection items)
{
	var table = new DataTable();

    foreach (ListItem li in items)
    {
        var row = table.NewRow();

        foreach (var kv in li.FieldValues)
        {
            var key = kv.Key;

            if (!table.Columns.Contains(key))
            {
                table.Columns.Add(key);
            }

            var value1 = "";
            if (kv.Value != null)
            {
                value1 = kv.Value.RawPrint();
            }

            row[key] = value1;
        }

        table.Rows.Add(row);
    }

    table.AcceptChanges();

    return table;
}

Try these scripts with the Instruction Workbench: Get SharePoint List Data