Wednesday, August 12, 2009

How do I tell what the next list item ID is?

By Roi Kolbinger - SharePoint Consultant
KWizCom Professional Services –
http://www.kwizcom.com/


If you have ever worked with Event Handlers on an item you probably came across the same problem that was bothering me…

I created a ListItem and used the event handler ItemAdding (for a synchronic event). I tried to find out what my ID was but the value I got was zero.

This problem will not occur on ItemAdded (of a non-synchronic event) because the item is created separately and it has its own ID.

If you use the code below on ItemAdding,you will get a zero value on listItem ID.

SPList list = web.Lists[properties.ListId];
SPListItem listItem = list.Items[properties.ListItem.UniqueId];
int itemId = listItem.ID; // itemId always zero on ItemAdding event


To discover the ID (and avoid getting the zero value) of my synchronic event I wrote this code:

int itemId = list.Items[list.ItemCount - 1].ID + 1;

This code will work well as long as you do not delete the last item and do not add more than one item simultaneously. If you do the sum you will get will be incorrect.

I searched for a SharePoint API that would give me the next item ID but found none. It seemed no one had tackled this problem….

After extensive searching I found a solution, brilliant in its simplicity. This is the code:

///
/// Get the next available item id from a list
///

/// < name="site">site
/// < name="listId">listId
/// <>
public static int NextItemID(SPSite site, Guid listId(
{
int id = -1;
Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(delegate()
{
if (site.WebApplication.ContentDatabases.Count > 0(
{
string DBConnString = site.WebApplication.ContentDatabases[0].DatabaseConnectionString;
SqlConnection con = new SqlConnection(DBConnString);
try
{
con.Open();
SqlCommand com = con.CreateCommand();
com.CommandText = String.Format("select tp_NextAvailableId from AllLists where tp_ID = '{0}'", listId.ToString());
id = (int)com.ExecuteScalar();
}
finally
{
con.Close();
}
}
});
return id;
}

...
int itemId = NextItemID(new SPSite(properties.SiteId), properties.ListId);


As you can see, the solution is to connect to the MOSS database and read the ID AllList table. All the information was right there, we simply had to access it.

Now you know how to get the next ID of the ListItem and (hopefully!) Event Handlers will no longer give you any trouble!...

Credits:
The solution detailed in this article was found at:
http://suguk.org/forums/permalink/6226/13513/ShowThread.aspx

5 comments:

Murugesa Pandian said...

You are intended to give the solution anyhow.But As we are developer often forget to follow the "Best Practices" for SharePoint as it goes "You can't not directly / or indirectly(through code) deal the Content Databases.
If you are manage to get the SQL credentials on your production server,then it good to go.

Murugesa Pandian said...

As a best practice policy defined by Microsoft,you cannot get things done through this approach as you are directly dealing with content database.If you are managed to get SQL credential for the production,then good to go.

Shai Petel said...

Hi Murugesa,
This post is 4 years old, this SQL command is not even available in SP2010 or SP2013, it was only there for SP2003/SP2007, and back then running this read only query was considered OK ( I have personally confirmed it back then with MS at that time ).

In any case this is sooo outdated you can't even use it now - but yes, you are correct developers should not access the content data base.

Sorry I did not understand your comment about SQL credential. In this code Roi was using RunWithElevatedPrivileges to get higher level access.

Tripwire said...

Did you ever consider grabbing the list item with the most recent Created date and adding one to its ID?

That should gracefully handle the issue of deleted items and also be usable via client-side scripting.

On very busy lists, however, there's still the possibility of concurrency with other users.

Shai Petel said...

Hey Paul, I'm afraid that's not accurate.

First off, the created date is not indexed, so querying and sorting based on it on large lists is an issue.

Now, if you delete an item - it's ID won't be re-used again, so it will be skipped - in which case you will get the wrong item ID.

Also, if you import, restore from recycle bin, or add items using a feature - their IDs might not be the last one, and in some cases they will be the latest one created.

This is why we didn't end up using that. I agree it is a very simplistic solution but it is not very solid or guaranteed.