Scope Identity and You

Mike BerrymanI had a situation recently where I was doing an insert into a SQL table from some C# code and then immediately had to perform some operations on other object using the newly inserted entry’s Id.  Now, I’ve done this sort of thing in the past but it’s always been on a system where I could be relatively assured no one else would be touching the table I was using except through the system I was developing and therefore I could get the freshly inserted entry’s Id just by doing a simple…

SELECT MAX([Id]) FROM [Test_Table]

…which would get the largest Id from the table. Since the table was set up with an identity as the primary key (you DO set up your tables that way, don’t you?), the largest Id was always going to be the last entry inserted.  Kludgey, but it worked.

That is, until suddenly I couldn’t assume my code was the only thing touching the table anymore.  I found myself in exactly that situation, wherein I couldn’t be sure that just getting the MAX Id from the table would be the entry I had just inserted.  What if something else had inserted an entry into this table in the .001 seconds my code took between inserting the entry I wanted to get the Id for and actually getting the Max Id?

Thankfully, there is a nifty little function in SQL called SCOPE_IDENTITY().  In a nutshell, the SCOPE_IDENTITY() function will return the identity of the last inserted entry from your session.  That last part is important, since it guarantees that the Id returned will be the Id of the inserted entry as long as the function is called within the same session.

One pitfall to watch out for when using SCOPE_IDENTITY() from code is to be absolute sure your call to the SCOPE_IDENTITY() is done within the same scope as the insert or else you’ll get 0 for the Id.  When I first implemented SCOPE_IDENTITY(), I had my code set up very similar to the following:

using (SqlConnection conn = new SqlConnection(_connectionString))
{
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "INSERT INTO [Test_Table] ([Value1], [Value2], [Value3]) "
                        + "VALUES (@val1, @val2, @val3)"
        cmd.Parameters.Add(new SqlParameter("@val1", value1));
        cmd.Parameters.Add(new SqlParameter("@val2", value2));
        cmd.Parameters.Add(new SqlParameter("@val3", value3));
        conn.Open();

        int i = cmd.ExecuteNonQuery();
        if (i == 1)
        {
            SqlParameter p = new SqlParameter();
            p.ParameterName = "@InsertedID";
            p.Size = 4;
            p.Direction = System.Data.ParameterDirection.Output;
            cmd.CommandText = "SELECT @InsertedID = SCOPE_IDENTITY()";
            cmd.Parameters.Clear();
            cmd.Parameters.Add(p);
            cmd.ExecuteNonQuery();
            int id = -1;
            string pValue = p.Value != null ? p.Value.ToString() : string.Empty;
            int iHolder;
            if (int.TryParse(pValue, out iHolder))
                id = iHolder;
            return id;
        }
        else
        {
            return -1;
        }
    }
}

When I ran this code, I was always getting an inserted id of 0 even though I could plainly see the insert was succeeding.  Even though I knew I had to call SCOPE_IDENTITY() within the same scope as the insert, I had assumed incorrectly that that meant I needed to call SCOPE_IDENTITY() before I closed the connection.  As it turns out, this wasn’t enough.  I had to make my call to SCOPE_IDENTITY() with the same command text as my insert.  Adjusting the code to the following did the trick:

using (SqlConnection conn = new SqlConnection(_connectionString))
{
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "INSERT INTO [Test_Table] ([Value1], [Value2], [Value3]) "
                        + "VALUES (@val1, @val2, @val3); "
                        + "SELECT @InsertedID = SCOPE_IDENTITY();";
        cmd.Parameters.Add(new SqlParameter("@val1", value1));
        cmd.Parameters.Add(new SqlParameter("@val2", value2));
        cmd.Parameters.Add(new SqlParameter("@val3", value3));
        SqlParameter p = new SqlParameter();
        p.ParameterName = "@InsertedID";
        p.Size = 4;
        p.Direction = System.Data.ParameterDirection.Output;
        cmd.Parameters.Add(p);
        conn.Open();

        int i = cmd.ExecuteNonQuery();
        if (i == 1)
        {
            int id = -1;
            string pValue = p.Value != null ? p.Value.ToString() : string.Empty;
            int iHolder;
            if (int.TryParse(pValue, out iHolder))
                id = iHolder;
            return id;
        }
        else
        {
            return -1;
        }
    }
}

Edit: Steve Ryherd commented below that a good chunk of this code is unnecessary, and he is correct.  I had originally copied a chunk of code I had written for another project that had 3 output parameters and when I edited the chunk down so it’d be generic for this post, it didn’t even occur to me that output parameters weren’t necessary anymore.  For most scenarios, the chunk of code Steve posted in the comments will get you your inserted row’s identity.  Thanks Steve!

2 thoughts on “Scope Identity and You

  1. How much of this code is copy/paste without review? Lines 12-32 are superfluous because you can use cmd.ExecuteScalar to automatically return a resultset. So instead of assigning it to a output parameter you can do something like this:

    using (SqlConnection conn = new SqlConnection(_connectionString))
    {
    using (SqlCommand cmd = new SqlCommand())
    {
    cmd.Connection = conn;
    cmd.CommandText = “INSERT INTO [Test_Table] ([Value1], [Value2], [Value3]) ”
    + “VALUES (@val1, @val2, @val3); ”
    + “SELECT CAST(scope_identity() AS int); ”
    cmd.Parameters.Add(new SqlParameter(“@val1”, value1));
    cmd.Parameters.Add(new SqlParameter(“@val2”, value2));
    cmd.Parameters.Add(new SqlParameter(“@val3”, value3));

    Int32 lastInsertId = -1;
    try
    {
    conn.Open();
    lastInsertId = (Int32) cmd.ExecuteScalar();
    }
    catch (Exception ex)
    {
    /* handle errors */
    }
    return lastInsertId
    }
    }

    Like

  2. Hey Steve. Thanks for the comment!

    You’re right, of course. I had originally copied my code snippet from a chunk of code I had written for a client, but then removed anything client-specific so the code would reflect only what I was talking about. The original chunk of code had 3 output parameters – it completely slipped my mind when I edited the others out that I was only returning one value now, and that an ExecuteScalar() would be sufficient.

    Thanks for pointing it out! I’ve added a note to the blog to direct readers to your comment.

    Like

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