My career in technology

Autonumbering Event Handler

Earlier this month I finally got around to a follow-up post on the subject of custom auto-incremented IDs, with detailed screenshots and instructions for setting up an SPD workflow to provide an automated custom numbering scheme from an earlier post. That earlier post focused more on the calculated column code to generate the number pattern that met the requirements I had been given.

One of the reader comments on the first post mentioned that an event handler would be a more efficient way to handle the requirement. At the time that I created the solution, the idea was to create something user-servicable, and the lists involved were not expected to handle a large volume of new documents, so an event handler wasn’t the right solution at that time for that particular project.

I responded about the requirements, and no sooner had the response to the comment left my keyboard… Well, OK, not quite that immediately, but surprisingly close on the heels of that, came a new requirement for a much more ambitious project, for which a more robust solution was required. Enter the event receiver.

Requirements

The requirement was to add an ID to any new document added to multiple libraries on a particular SharePoint 2010 site, with scalability to use the same numbering for documents added to libraries on other sites and in other web applications. Hundreds of documents would be added to the libraries, often in large batches. Different business areas would add documents on their own schedules to their specified libraries, which means that multiple people could simultaneously be adding many items. No two items should be given the same number, and missing numbers from the sequence were not an issue, since it was expected that, over time, documents would be deleted as they ceased to be relevant.

In a brief trial using an SPD workflow on an upload of 100 documents, the workflows failed on start, confirming the need for a more robust solution.

Initially this was designed to use a SharePoint list to hold the number, just as in the previous solution. To make it “bulletproof”, we wanted to check out and check in the number holder, so that two documents being created simultaneously wouldn’t accidentally grab the same number. Since check out and check in are available on libraries and not lists, the “Unique Number List” was implemented as a library, using a dummy document and with the Title field holding the next unique integer. This worked well in conjunction with the event handler, except in one instance when I had manually reset the number after some testing and forgot to check the item back in. Oops.

Later, for a number of reasons (including my faux pas forgetting to check the Unique Number List document back in) we decided that we would rather use a SQL table instead of a SharePoint list to generate the unique numbers, so I have included that code as well. That implementation creates a new table row for each document and adds the name of the document to the row, for later reference as a lookup.

Finally, we also determined that a large number of documents would retain legacy IDs from an older system, and we did not want the event handler to assign numbers to these documents. We needed a way to turn off the event handler for specific lists for periods of time without interrupting the work of other users who would be uploading their own documents to other libraries. This was implemented by creating a SharePoint list called “Exclude ID Assignment”. This is a simple custom SharePoint list with a single field, the Title. When users are expected to upload documents with legacy IDs, they would add a list item with the exact name of the library where the items were being uploaded to. Any libraries whose names appear in the “Exclude ID Assignment” list would be excluded from the event handler, until the list item with the name of the list was deleted.

Creating the event receiver

To create the event handler, in Visual Studio 2010, create a new project (File >> New >> Project, or “Ctrl+Shift+N”), SharePoint 2010 Event Receiver. Give it a name (I went with “IDAssignmentEH” for this demo), click OK. Choose the site you want to use for debugging, and whether or not you want to do a sandbox or farm solution, then click Next.

Select List Item Events (since we want to handle the creation of items in a library), the event source should be Document Library (if you are writing the IDs to new documents, which is what my requirement was), and handle the following event: “An item was added”. (There is a gotcha for this, as you will see at the end of the post).

Let’s dig into the code, shall we?

First using a SharePoint list to store the unique IDs:

using System;
using System.Security.Permissions;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Security;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.Workflow;

namespace IDAssignmentEH.EventReceiver1
{
    /// <summary>
    /// List Item Events
    /// </summary>
    public class EventReceiver1 : SPItemEventReceiver
    {
       /// <summary>
       /// An item was added.
       /// </summary>
       public override void ItemAdded(SPItemEventProperties properties)
       {
               SPQuery query = new SPQuery();
               query.Query = string.Concat(
                                  "<Where><Eq>",
                                     "<FieldRef Name='Title'/>",
                                     "<Value Type='TEXT'>", properties.List.Title.ToString(), "</Value>",
                                  "</Eq></Where>");
               if (properties.Web.Lists["Exclude ID Assignment"].GetItems(query).Count == 0)
               {
                   SPListItem UniqueIDItem = properties.Web.Lists["Unique Number List"].Items[0];
                   while (UniqueIDItem.File.CheckOutType != SPFile.SPCheckOutType.None)
                   {
                       System.Threading.Thread.Sleep(250); // pause for 1/4 second;
                       UniqueIDItem = properties.Web.Lists["Unique Number List"].Items[0];
                   }

                   UniqueIDItem.File.CheckOut();
                   String sNextNumber = UniqueIDItem["Title"].ToString();
                   Double iNextNumber = Double.Parse(sNextNumber);
                   //customize your number here, e.g. sNextNumber = "YourPrefix" + sNextNumber;
                   try
                   {
                       properties.ListItem["UniqueID"] = sNextNumber;
                       properties.ListItem.Update();
                   }

                   catch
                   {
                   //error handling goes here
                   }

                   finally
                   {
                       iNextNumber++;
                       UniqueIDItem["Title"] = iNextNumber.ToString();
                       UniqueIDItem.Update();
                       UniqueIDItem.File.CheckIn("updated for new item");
                   }
               }

           base.ItemAdded(properties);
       }

    }
}

Lines 01-19 are the starting code provided by Visual Studio

Lines 20-27 – this section of the code checks to see if the library calling the event receiver is in the exclusion list. The code creates a CAML query looking for the name of the list calling the event receiver. If the item count returned from the query is zero, there is no list item with the name of the list, and it is safe for the event receiver to handle the event. Otherwise it skips everything and calls base.ItemAdded(properties). (This code is the same as the code in Lines 22-29 in the SQL version.)

In this version, the code then checks to see if the item in the Unique Number List is checked out (line 29). If it is checked out, it waits for 250 ms (a quarter of a second). In benchmarks in the test server, the event handler checked the item out, ran the process and checked the item back in within ~.07 seconds, and took less time on the production server. .250 seconds is more than adequate.

Here is a gotcha: If you don’t recheck the list item object inside the while statement (line 32), it will use the state of the object as it existed when you went into the while statement. You don’t enter the while statement unless the item happens to be checked out when the event handler runs, and if you don’t recheck the object, the CheckOutType never changes. In other words, you are stuck in the loop, since the code cannot detect a change in the CheckOutType.

Lines 36-38 get the number from the Unique Number List item, and set the number in a pair of variables (one as a string to create the unique ID, one as a number to increment).

In case something goes wrong, you want to make sure the Unique Number List item gets checked back in regardless. Remember that, in my requirements, a number missing from the sequence is OK, but you may have different requirements. The code to add the unique ID to the newly added item in the target library is placed in a try/catch block (lines 39-48), so that if something goes wrong with the update, the Unique Number List item is checked back in inside the finally block (lines 50-56). The check in comment is customizable; for instance, you could add the title of the document you are adding as part of the comment.

The last step in the override of ItemAdded is to call the base functions (line 59).

And now for the SQL version:

using System;
using System.Security.Permissions;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Security;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.Workflow;
using System.Data;
using System.Data.SqlClient;

namespace IDAssignmentEH.EventReceiver1
{
    /// <summary>
    /// List Item Events
    /// </summary>
    public class EventReceiver1 : SPItemEventReceiver
    {
       /// <summary>
       /// An item was added.
       /// </summary>
       public override void ItemAdded(SPItemEventProperties properties)
       {
               SPQuery query = new SPQuery();
               query.Query = string.Concat(
                                  "<Where><Eq>",
                                     "<FieldRef Name='Title'/>",
                                     "<Value Type='TEXT'>", properties.List.Title.ToString(), "</Value>",
                                  "</Eq></Where>");
               if (properties.Web.Lists["Exclude ID Assignment"].GetItems(query).Count == 0)
               {
                   SqlConnection Conn = new SqlConnection("server=SQLServerName,OptionalPortNumber;" +
                       "user id=UserName;password=Password;database=DBName;connection timeout=30");
                       //"Trusted_Connection=yes;database=DBName;connection timeout=30");
                   Conn.Open();

                   SqlDataReader myReader = null;
                   SqlCommand myCommand = new SqlCommand("INSERT INTO dbo.TableName (FileName) VALUES ('" + properties.ListItem.File.Name.ToString() + "');SELECT @@IDENTITY AS ID", Conn);
                   myReader = myCommand.ExecuteReader();

                   myReader.Read();
                   String sNextNumber = myReader["ID"].ToString();

                   Conn.Close();

                   //customize your number here, e.g. sNextNumber = "YourPrefix" + sNextNumber;
                   properties.ListItem["UniqueID"] = sNextNumber;
                   properties.ListItem.Update();
               }

           base.ItemAdded(properties);
       }

    }
}

Lines 01-21 are again the starting code, though this time with added references to System.Data and System.Data.SqlClient, which you will need if you are using the SQL table to store your unique number.

Lines 22-29 are identical to lines 20-27 in the other version of the code.

In this version of the code, lines 30-32 sets the SQL connection. Choose EITHER line 31 or 32 to make your connection to the database (not both).

The SQL command on line 36 inserts a new row in the table, with the value set to the file name in this example, but you can use another field if you choose. It also grabs the identity column value (guaranteed unique by the SQL server) for the SqlDataReader. The string variable sNextNumber is set to the value of the ID on line 40, and the connection is closed.

You can manipulate the string before line 45, where the UniqueID field is set to the value of the string.

GOTCHA!

Here is the big gotcha – If your target library, where you are storing the documents that are getting numbered, requires Check Out, you will get an error message unless you make this change. If you later implement Require Check Out without updating your code, you will get an error. If you make the change but don’t implement Require Check Out you won’t see any difference. In my book, that means go ahead and make this change for flexibility.

In the Elements.xml file, add the following line to the xml in the Receiver node:

<Synchronization>Synchronous</Synchronization>

If you don’t do this and Require Check Out is set to “Yes”, the ID will update correctly, but the web interface will display an error (“The item was updated by user“). Not the best experience for the end user, even though it does successfully generate the number. The reason for this is the ItemAdded is asynchronous by default (unlike the ItemAdding event). Changing it to synchronous removes the edit conflict.

Final Thoughts

I’ve had fun with these projects, as each of them has had different requirements to achieve similar goals, so each had presented a, dare I say, unique challenge to my creativity.

Hopefully these posts will help speed you to your solution. Please be mindful that your requirements may make some of the things I have had to do unnecessary, and may require extra creativity on your part.

As per my policy, the code is provided without warranty or any other guarantee, but that said, I want to be as helpful as I can, so if you run into issues or have questions, don’t hesitate to comment below.

More posts about SharePoint.

The Custom Unique Automatic ID Trilogy

Add a Unique Auto-Incrementing Column to a SharePoint List
Auto-Increment Workflow Screenshots
Autonumbering Event Handler

About these ads

Comments on: "Autonumbering Event Handler" (13)

  1. [...] a Unique Auto-Incrementing Column to a SharePoint List Auto-Increment Workflow Screenshots Autonumbering Event Handler Share this:Like this:LikeBe the first to like this [...]

  2. Arfat Ahmad said:

    Hi,
    I need a similar solution for my problem. I need to create an auto incrementing column in a Sharepoint List using visual studio, handled by an event handler.
    Can you please help me.

    Thanks & Regards
    Arfat Ahmad

  3. Richard Sigar said:

    I really enjoy this series and starting to imagine on how I can implement this in my site.
    First, I’m really a noob about Sharepoint and reading this article got me lost but also intrigue at the same time :).

    Currently my doc library is using a solution I found on codeplex, and what it does is auto fill the title field with the original uploaded document name. From then the user manually check the last number used and +1 it to number their uploaded document, manually.

    Can the 2 functions be implemented simultaneously without doing multiple workflow? Or can I merge them into a single workflow?

    About your code which using SQL table, how do I create the table for it?

    Pardon my noobness but if you could just point at where I can learn the basics to achieve my requirements I will be forever indebted to you :D

    ps: I’m using Sharepoint Foundation 2010 if that matter.

    Regards,
    Richard

    • I don’t know about the codeplex solution, so integration advice would be difficult to give.

      If it is a compiled code solution, you would need to get the source code and modify the workflow there and recompile the solution, if you wanted to do it in a single workflow.

      Otherwise, there is nothing wrong with using multiple workflows on a list, though I would be sure of timing them correctly so that one finished before the next starts. Though be careful about system preformance – if the list is getting a lot of traffic, multiple workflows can affect performance.

      For the SQL table, a CREATE TABLE sql statement would be a good bet, or use the wizard in SQL Management Studio by right clicking and choosing “New”. You’ll probably want your DBA to do that, or get a book and read up on SQL before doing anything on SQL Server.

      If you have more specific questions, I’ll be glad to try and answer them.

  4. Richard Sigar said:

    Thanks a lot for helping out. So basically I can create a new blank db and use it with the script? Cool, I will experiments on it, don’t worry I won’t do it on our production server :)

    It’s really great having you replying. You will be hearing from me again for sure..

  5. Richard Sigar said:

    Thanks a lot. You’ve been a huge help!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 877 other followers

%d bloggers like this: