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).
That looks like a doc error, it is the other way around. You can only execute a single statement per ExecuteNonQuery call.