CODEWITHSUNDEEP
c#sql-server-ce
Yván Ecarri
Yván Ecarri

Reputation: 1739

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

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?

Upvotes: 2

Views: 3302

Answers (2)

Yván Ecarri
Yván Ecarri

Reputation: 1739

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);

Upvotes: 3

Cesar Duran
Cesar Duran

Reputation: 377

The way it worked for me is by replacing:

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

with this:

cm.CommandText = "SELECT @@IDENTITY";
var id = cm.ExecuteScalar();

Also I removed (not needed):

tr = cn.BeginTransaction();cm.Transaction = tr;

Upvotes: 0

Related Questions