What is the best way to query SharePoint data?

Should I just use ADO.NET to directly access the underlying data in the database or does .NET have a better way to do this?

I am currently using SharePoint 2007开发者_StackOverflow社区 but we are about to move to 2010. I am using Visual Studio 2010.

I would highly recommend using the SharePoint objects for accessing data (either SP* classes, or using SharePoint web services, LINQ to SharePoint etc).

Don't directly access the underlying database using pure ADO.NET. The main reason is that, by using the supported (official) way of accessing data, you lessen the risk of causing problems. For example, when changing data in a table, you don't know if there is a mandatory requirement for another table in the database to be updated also. The SharePoint objects will take care of that for you.

Plus, I think Microsoft will not support issues with your SharePoint code if it's directly accessing the database and there is a risk your code may not execute after any hot fix or SP release as Microsoft may change the Database structure any time .

Are you talking about custom database or Sharepoint database?

You should never touch directly sharepoint database since it will put your farm in unsupported state.

If you want to access, custom database, consider using Entity Framework: http://msdn.microsoft.com/en-us/library/aa697427(VS.80).aspx

This depends on context. Answering the following questions will help you make the best choice to move forward.

What type of data?

List data from one site? Use webservices

Aggregating content across site collections? Use the server object model

What am I trying to do with the data?

Display a summary in a simple webpart? Consider using the CQWP(Content Query WebPart) or DVWP(Data View Webpart)

What is my timeline to deliver this solution?

If you haven't done SharePoint custom code deployments before and you are on a tight deadline try hard to stick to the web services api.

You should definitely not access the SharePoint content databases directly.

There is a search web service you can use to get items or the list web services allow you to pull data out easily enough. If you are hosting your code within SharePoint, use the SharePoint object model. It is simple enough to get what you need.

As the others have said, do not touch the database directly. Microsoft won't support it, and even if they did, the database structure is not documented and more complicated than it seems to be when you first open it.

There are a lot of objects in the SharePoint object model. However, a few of them are especially useful if you are trying to query a list. I'll give you a short list of what I think you should start with. Once you are comfortable with these and what they represent in SharePoint, you'll find the others in no time (either through what the functions you use return or through MSDN).

1. SPList

Probably the type you'll end up dealing with the most. A SPList represents any kind of SharePoint list. That is a document library, a survey, a picture library, or any sort of custom list you could of made. Almost everything in SharePoint is a list, from the User Information List (which is used for authentication) to a blog (which is a list of blog entries). The items inside a list are SPListItem

2. SPWeb and SPSite

Now this is a bit confusing but a SPSite represents a Site Collection and a SPWeb represents a single Site. Say you have a SharePoint site on hXXp://SharePointSite.com well the Site Collection (SPSite) would be hXXp://SharePointSite.com and a SPWeb would be hXXp://SharePointSite.com/blog or hXXp://SharePointSite.com/about Basically, a Site Collection is a collection of SPWeb, which are a collection of SPList (and other things you won't care about). A SPList always lives inside a web.

3. SPQuery

This is SharePoint's SQL. Though there is a way to do LINQ 2 SharePoint in 2010 (and even in 2007 but I wouldn't bother with it if you're about to move to 2010 anyway), you'll still have to use this sometimes. You use SPQueries anytime you want to extract items from a list. Sometimes, looping on all items from a list : foreach(item in SPList.Items) is fine, but when the lists get bigger you'll want to query them with SPQuery (same as with SQL / ADO really)

Putting it all together in an exemple. Say you had a list of people somewhere on your site and you wanted to get who was 18 years old. Your code would like like this :

    using(SPSite site = new SPSite("http://sharepointsite.com"))
    {
        using (SPWeb web = site.RootWeb)
        {
            SPList peopleList = web.Lists["People"];
            SPQuery query = new SPQuery();
            query.Query = 
                    "<Where>" +
                        "<Eq>" +
                            "<FieldRef Name='Age'/><Value Type='Number'>18</Value>" +
                        "</Eq>" +
                    "</Where>";

            SPListItemCollection items = peopleList.GetItems(query);

            foreach(SPListItem item in items)
            {
                //item here represents a person who's 18 years old.
            }
        }
    }

The ugly language (or markup) used to make the SPQuery is called CAML. You can google for help when you need it but to get you started you can read this article which explains a lot. This is a tool that I use to help me build queries : U2U CAML Builder

By the way, although it is possible to query the lists via web services, I recommend against it. If you can run your code on a machine that has SharePoint installed, then the object model is much better / more intuitive than the web services, which are only there as a substitute when SharePoint is not installed. Only use them if you have to. The data that they return is in XML and the formatting is a pain. There aren't as many functions available either.