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