Inserting a row and retrieving identity of new row

Question

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 with a single invocation of a DbCommand object in C#... it doesn't seem work or I've got something wrong.

I've read that Compact Edition doesn't support executing multiple statements in a batch... but I also found this:

If you want to run multiple queries simultaneously, you must include a new line character for each statement and a semicolon at the end of each statement.

Source: http://technet.microsoft.com/en-us/library/bb896140(SQL.110).aspx

So does it work or not... and if so what am I missing?

(I realise I can execute two commands and that works fine, but I wonder if I'm missing something).

Answer 1

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

Answer 2

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

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

Answer 3

You must define a output parameter to get identity value

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