Inserting a row and retrieving identity of new row in SQL Server CE

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).

What do you mean by "doesn't seem work" [sic]? What happens? Do you get any error message? — Guffa, Apr 06 '11 at 06:46

score 8 · Accepted Answer · answered Apr 06 '11 at 06:39

8

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

answered Apr 06 '11 at 06:39

ErikEJ

40,951
5
75
115

1

Surprised if a doc error can be repeated at least 3 times for SQLCE [3.5 SP1](http://technet.microsoft.com/en-us/library/bb896140(SQL.100).aspx), [3.5 SP2](http://technet.microsoft.com/en-us/library/bb896140(SQL.105).aspx), and [4.0](http://technet.microsoft.com/en-us/library/bb896140(SQL.110).aspx)? I've got really confused. – Masood Khaari Nov 20 '13 at 11:22

mariocase84 · Answer 2 · 2011-04-06T07:14:20.470

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;

edited Apr 06 '11 at 07:14

answered Apr 06 '11 at 07:03

mariocase84

436
2
3

Are you sure about that? I'm using DbCommand.ExecuteScalar() and it returns the new records identity fine. It just needs to be a separate command, not a batched one. – Brad Robinson Apr 06 '11 at 11:43
2

SQL Server Compact Edtion only supports a single statement per command. [link](http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/1d1d3267-dc29-470b-bb20-00487a39bc87/) – mariocase84 Apr 06 '11 at 13:41
1

Also, it seems that you MUST "SELECT @@IDENTITY" against the same open connection that you have run "INSERT". – Agent007 Dec 12 '13 at 08:32

score 0 · Answer 3 · answered Oct 10 '16 at 15:29

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;
}

Inserting a row and retrieving identity of new row in SQL Server CE

3 Answers3

Related