Search code examples
c#sqlsql-serversql-server-ce

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

Solution

  • 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