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
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