Difference between SPList.GetDataTable and SPList.GetItems(SPQuery).GetDataTable

Question

I've few questions as below:

1. Can some one please explain me the difference between SPList.GetDataTable and SPList.GetItems(SPQuery).GetDataTable methods?
2. Explain the meaning of the parameters for SPList.GetDataTable with proper examples
3. Also, is there a difference in performance between the APIs?

Answer 1

1) "GetDataTable" returns a datatable in C # while GetItems returns a SPListItemCollection.

One of the main differences is that "GetDataTable" converts objects in columns of a table and then you can not process them as objects of SharePoint.

An example of this are the "lookup" fields. You'll only get the text in the field and not the ID. If for some reason you require the ID, you have to use the flag "RetrieveLookupIdsOnly"

Links

• https://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splist.getdatatable.aspx
• https://msdn.microsoft.com/en-us/library/ms457534.aspx

2)

2.1) The first parameter is a SPQuery. Not much to explain here.

2.2) Flags: The second is SPListGetDataTableOptions

• None: No option. Use the default data type for column values.
• UseBooleanDataType: Return Boolean values ​​using the Boolean column data type.
• RetrieveLookupIdsOnly: Return the IDs for lookup columns rather than the Looked-up values.
• UseCalculatedDataType: Return Calculated values ​​using the column data type for the calculation.

Source: https://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splistgetdatatableoptions.aspx

RetrieveLookupIdsOnly can be very important. See point 1.

2.3) Position

If you are working with paging can be useful. Honestly I have not used.

3) Performance

This depends a bit on what you need to do. I'll tell you my experience in a case where "GetDataTable" made the difference for good performance.

Scenario:

Top 10 lists

Complex calculations:

• Sort lists in different ways
• Joins
• Several filters
• Mathematical calculations

Working approach

• Read all the lists with "GetDataTable"
• Worki with c# functions for datatables to make the complex part in memory

With this approach I got there only one SQL query for each list the rest of the work was done in memory. With excellent performance.

Sample code

public static DataTable leerLista(string listaURL, string listaView, string listaQuery)
{
    listaURL = listaURL.Replace("//", "/");
    using (SPSite oSiteCollection = new SPSite(SPContext.Current.Site.Url))
    {

        using (SPWeb mi_web = oSiteCollection.OpenWeb())
        {
            DataTable dt = new DataTable();
            SPList lista = mi_web.GetList(listaURL);
            SPQuery consulta = new SPQuery();

            consulta.ViewFields = listaView;
            consulta.Query = listaQuery;

            dt = lista.GetItems(consulta).GetDataTable();

            return dt;
        }
    }
}

And this is an excellent link to learn how to work with datatables: http://www.dotnetperls.com/datatable