Inserting A Row And Retrieving Identity Of New Row In Sql Server Ce

I'm trying to insert a row and get back the new row's identity with something like this: INSERT INTO blah....; SELECT @@IDENTITY as NewID; I'm trying to execute both statements wi

Solution 1:

That looks like a doc error, it is the other way around. You can only execute a single statement per ExecuteNonQuery call.

Solution 2:

You must define a output parameter to get identity value

sqlCommandCommand= Conn.CreateCommand();
Command.CommandText = "insert into MyTable(Value1,Value2) values(MyValue,MyValue)"
Command.ExecuteNonQuery();
Command.CommandText = "select @ID = @@IDENTITY"SqlParameterID=newSqlParameter("@ID", SqlDbType.Int);
ID.Direction = ParameterDirection.Output;
Command.Parameters.Add(ID);
Command.ExecuteNonQuery();
intNewID= (int)ID.Value;

Solution 3:

After a lot of errors with sql-server-ce such as Direction couldn't be Output I got this to work.

publicstaticlongGetIdentity(this IDbConnection connection)
{
    var cmd = connection.CreateCommand();
    cmd.CommandText = "SELECT @@IDENTITY";
    cmd.CommandType = CommandType.Text;
    var id = cmd.ExecuteScalar();
    return (long)(decimal)id;
}