Select SCOPE_IDENTITY after insert with SqlCeCommand

Question

My program has an online and offline mode.

• Online it connects to a SQL Server instance using the C# SqlCommand classes.
• Offline it connects to a local .SDF file using the C# SqlCeCommand classes.

Now I want to do the following which works when in online mode

GetSqlCommand("insert into my_table (col1) values (@c1); SELECT SCOPE_IDENTITY()", conn))

So I insert a record a get back the ID of that new record with SELECT SCOPE_IDENTITY().

But when in offline mode using SQL Server CE, it throws an error. Is there a way to get back ID from the insert with SQL Server CE without running a separate query?

Answer 1

SQL Server CE doesn't support multiple queries in single command, try as below

SqlCeCommand  cmd = new SqlCeCommand("insert into my_table (col1) values (@c1)", conn);
//set paremeter values 
//execute insert 
cmd.ExecuteNonQuery();
//now change the sql statment to take identity 
cmd.CommandText = "SELECT @@IDENTITY";
int id = Convert.ToInt32(cmd.ExecuteScalar());

https://stackoverflow.com/a/6480017/2558060

Answer 2

Although Damith answer is right, Yes We cannot run multiple query with SQL Server CE.

I'm taking the example with same code.

    SqlCeCommand  cmd = new SqlCeCommand("insert into my_table (col1) values (@c1)", conn);
    //set paremeter values 
    //execute insert 
    cmd.ExecuteNonQuery();
    //now change the sql statment to take identity 
    ////////////////**CONNECTION SHOULD NOT BE CLOSED BETWEEN TWO COMMANDS**/////
    cmd.CommandText = "SELECT @@IDENTITY";
    int id = Convert.ToInt32(cmd.ExecuteScalar());

Note: Connection should be open between two commands, otherwise second query will fail.

Better you use this code in Transaction.

Note: SQL Server CE objects are not thread-safe, it may lead to Access Violation exception if instance of SqlCeConnection or SqlCeTransaction is shared across threads. It is recommended that each thread should use a separate connection, it should not be shared across multiple threads.

Answer 3

I think this is your answer: How do I use an INSERT statement's OUTPUT clause to get the identity value? but im not sure about SQL CE.. Give it a try.

EDIT: Then this answer is probably right: Inserting into SQL Server CE database file and return inserted id