SharePoint lists can be a convenient mechanism to store data in a database table-ish manner without having to leave your comfy SharePoint home. However, before you get too comfy living in a SharePoint list data storage paradigm, there are just a few things I'd like to throw out there.

First of all, programmatically iterating through a SharePoint list is EXPENSIVE! Unlike the performance we are used to from SQL Server, lists with as "few" rows as 3,000 could take over twenty seconds to loop through. Unless there is an operation that you need to perform on every single row of the list, you should be very careful to eliminate frivolous enumerations.

Next, if enumeration is not an option, what do we do to get to our data? There are three methods that will keep our SharePoint code nice and trim. The first is to use an SPQuery object. Read up on these babies here.

The basic idea is that you define an actual XML-ish query in CAML (Collaborative Application Markup Language) and send it to the SPList.GetItems method. You get a resulting list that only has the items found in the query you specified. I have two problems with this approach.

First, the result is still a list! In order to process your query after using a technique to avoid iterating a list, you have to iterate a list. Now of course, you can specify an exact match query and be done with it...but the more calls to the SharePoint API you make, the longer your method will take.

Secondly, and I regret that I am unable to provide solid evidence against it, but I do not have full faith and trust in God and Microsoft that this method works every time! I tried it once, and it worked. I tried it again in a different situation (where, admittedly, I had gone on a column-renaming tangent) and it could not find the columns, unless I queried one that was out-of-the-box. In a third situation, it wouldn't work at all. I can offer no further guidance beyond my own experience, and it's also very possible that I was screwing something up. So proceed with your SPQuery at your own risk...

Another way to circumvent costly loops around a SharePoint list is using the "..byId" methods. These include: GetItemById and DeleteItemById. Since SharePoint lists keep an incremental integer ID column (like a primary key with an identity seeded for a database table), we can uniquely identify each row, and directly assess or delete it. Of course, the only caveat here is that you do need to know the ID when you start...

The final method I use in these situations is, in my opinion, the most elegant. This is the GetDataTable() function on the SPListItemCollection object. What this does is gets us off the cold hard SharePoint object model and into the familiar, warm embrace of ADO.NET. Once we get our hands on a DataTable object, getting at a SharePoint list's data becomes a much easier - not to mention more efficient - task. Here is the basic construct of this method that also takes advantage of the "...byId" functions above.

Here is what a common method that retrives data and deletes list items from a list would look like, combining some of the aforementioned techniques:

There is only one "gotcha" here. If a SharePoint list column has a space in its name, make sure you are careful when accessing data columns in your table via it. This caution is due to the fact that SharePoint replaces spaces with the following string: "_x0020_". Although I haven't tested this, you might notice that it is closely related to the URL encoding that replaces spaces with "%20." So my assumption would be that for each character that would be URL encoded with a percent symbol, SharePoint will replace that same character in a list column name with "_x00[insert encoding here]_". So be careful!