What is the recommended way to query data on a Sharepoint site?

Question

Say if I need to retrieve list items for batch processing, how would I be recommended to retrieve the data? Maybe Powershell or a SQL Server query?

Answer 1

Definitely don't want to go the SQL query route because you can easily create an ineffecient query if you don't understand the underlying data model...also possible that you'll develop a query that breaks or becomes inefficient after a CU or SP is applied.

CAML queries are very efficient and depending on the scope of your query you have a lot of options.

1. SPList.GetItems with a SPQuery object will let you execute a CAML query against the items in a specific list.
2. SPSiteDataQuery lets you execute a CAML query across a site, allowing you to specify lists to include in the query by type or name.

Either method can be used via .NET or PowerShell to return a list of items that can be iterated through for batch processing.

EDIT: I should also mention that an invaluable tool when working with CAML is the u2u CAML Query Builder. I'm not aware of a SP2010-specific version, but I have never had any problems running the SP2007 version on 2010: http://www.u2u.net/Tools/wincamlquerybuilder/CamlQueryBuilder.aspx

Answer 2

You can query data from SharePoint using various approaches as mentioned below.

• SPList.GetItems with a SPQuery object that will return you a SPListItemCollection object

• SPList.Items.GetDataTable. You can assign a DataView to the DataTable that you receive and filter based on your requirements.

• SharePoint also has OOB webservice known as Lists.asmx which you can use to fetch data from SharePoint lists.

So any of these approaches you can fetch data from SharePoint. Below are a couple of links that can help you in detail on these approaches.

http://sharepointmagazine.net/articles/writing-caml-queries-for-retrieving-list-items-from-a-sharepoint-list

http://msdn.microsoft.com/en-us/library/lists.lists.getlistitems.aspx

http://www.a2zdotnet.com/View.aspx?Id=100

Hope it helps.

Regards, Geetanjali

Answer 3

For SharePoint 2010, I'd recommend using the Client Object Model. It allows for development without having to resort to installing SharePoint on the developer's PC (or developing ON the SharePoint server).

http://msdn.microsoft.com/en-us/library/ee857094.aspx

Answer 4

Well, surely don't forgot about Search option. Time to time it might be appropriate solution as well.