Convert a SharePoint List into a DataTable

Sometimes it an be handy to bulk convert a SharePoint List into a DataTable. This can be particularly handy when doing bulk comparisons, as you can perform an SPQuery equivalent without having to repetitively access the SharePoint List.

You can really expedite the process is you are able to establish a primary key on the DataTable, however you need to have processes in place to ensure that the the source column in the Sharepoint List contains only distinct values.

In one of my applications I use this process to update / read values across 20 or 30 lists simultaneously. The end result is that I am able to troll through just under 1 million records a minute reducing a SPQuery process that used to take half hour down to seconds.

Be warned however that there are no record locks here, so the DataTable represents the Sharepoint List at that point-in-time – if records are changed “underneath” the process they wont be included.

private static DataTable ConvertSPListToDataTable(SPList oList)
{
    DataTable dt = new DataTable();

    try
    {
        dt = oList.Items.GetDataTable();
        foreach(DataColumn c in dt.Columns)
             c.ColumnName = System.Xml.XmlConvert.DecodeName(c.ColumnName);
        return (dt);

    }
    catch
    {
        return (dt);
    }
}

Leave a Reply