Select Scope_identity After Insert With Sqlcecommand

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# Sql

Solution 1:

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

SqlCeCommandcmd=newSqlCeCommand("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";
intid= Convert.ToInt32(cmd.ExecuteScalar());

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

Solution 2:

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

Solution 3:

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.