Select @@Identity inside 'Using' statement

Question

I am coding a Sql-Server-ce application in C#.

Recently I have been converting my code to use using statements, as they are much cleaner. In my code I have a GetLastInsertedID function which is very simple - it returns the last inserted ID. The working version is as follows:

    public static int GetLastInsertedID()
    {
        int key = 0;
        try
        {
            SqlCeCommand cmd = new SqlCeCommand("SELECT CONVERT(int, @@IDENTITY)", DbConnection.ceConnection);
            key = (int)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Could not get last inserted ID. " + ex.Message);
            key = 0;
        }
        return key;
    }

Below is the code that does NOT work once I wrap it in using statements:

    public static int GetLastInsertedID()
    {
        int key = 0;
        try
        {
            using (SqlCeConnection conn = new SqlCeConnection(DbConnection.compact))
            {
                conn.Open();
                using (SqlCeCommand cmd = new SqlCeCommand("SELECT CONVERT(int, @@IDENTITY)", conn))
                    key = (int)cmd.ExecuteScalar();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Could not get last inserted ID. " + ex.Message);
            key = 0;
        }
        return key;
    }

The error that I'm getting is specified cast is not valid. Although this error is usually self-explanatory, I cannot see why I would be getting it inside the second block of code, but not the first. This error occurs on the line key = (int)cmd.ExecuteScalar();.

What am I doing wrong with the second block of code?

Answer 1

First of all, @@Identity will return any last generated ID from anywhere in SQL Server. Most probably you need to use SCOPE_IDENTITY() instead.

This shows your actual problem and design issue - you need to keep Connection and Command separate. Connection embeds transaction and though SCOPE_IDENTITY() will work until connection is closed; Command can be created, used and disposed.

So you need method which accept connection and use it to obtain identity - something like this (didn't check it but think idea should be clear):

public static int GetLastInsertedID(DbConnection connection)
{
    try
    {
        string query = "SELECT CONVERT(int, SCOPE_IDENTITY())";
        using (SqlCeCommand cmd = new SqlCeCommand(query, conn)) {
            return (int)cmd.ExecuteScalar();
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Could not get last inserted ID. " + ex.Message);
        return 0;
    }
}

For working with connection you can create helper method like this:

public static SqlCeConnection OpenDefaultConnection()
{
    SqlCeConnection conn = new SqlCeConnection(DbConnection.compact);
    conn.Open();
    return conn;
}

And use it like this:

...
using (SqlCeConnection conn = OpenDefaultConnection()) {
    //... do smth
    int id = GetLastInsertedID(conn);
    //... do smth
}
...