@@IDENTITY returns NULL when in transaction in SQL Compact Edition

Question

I have a SQL Server Compact Edition database with a table with 3 columns: Id, Field1 and Field2. Id is IDENTITY(1,1) NOT NULL.

const string SQL_INSERT = @"INSERT INTO Table(Field1, Field2) VALUES(@Param1, @Param2)";
const string SQL_SELECT_IDENTITY = @"SELECT @@IDENTITY AS ID";
SqlCeConnection cn = null;
SqlCeTransaction tr = null;
SqlCeCommand cm = null;

try
{
   cn = Utility.GetConnection();
   cn.Open();

   tr = cn.BeginTransaction();
   cm = new SqlCeCommand(SQL_INSERT, cn);
   cm.Transaction = tr;
   cm.Parameters.AddWithValue("@Param1", data.Data1);
   cm.Parameters.AddWithValue("@Param2", (int)data.Data2);
   cm.ExecuteNonQuery();

   cm = new SqlCeCommand(SQL_SELECT_IDENTITY, cn);
   object o = cm.ExecuteScalar();
   data.Id = Convert.ToInt32(o);
   // other operations
}

The problem is that cm.ExecuteScalar() returns null. If I remove the transaction, the query returns the appropriate value. Using SCOPE_IDENTITY() just fails because it's not supported in SQL Server CE.

How can I retrieve the inserted identity value? Is there any workaround to solve this problem?

Answer 1

I just realized the cause, the identity select also needs to use the transaction.

cm = new SqlCeCommand(SQL_SELECT_IDENTITY, cn);
// ADD THE TRANSACTION TO THE COMMAND
cm.Transaction = tr;
// ----------------------------------
object o = cm.ExecuteScalar();
data.Id = Convert.ToInt32(o);