How to get the next available primary key from a SqlCe DB ?

Question

How can I get the next available primary key from my table? The primary key is a numeric. I'm using C#.

Answer 1

*my answer is based on knowledge of MS SQL, not CE

Well, the wrong way is to take the max of the key column and add 1 to it... and then pray it doesn't get taken by some other process or program also inserting records at the same time as you :D

I assume you're trying to create the next key for an insert? If so, you should use an 'identity' field, which will update itself without your intervention. Otherwise:

Is SCOPE_IDENTITY() available? Use it right after your previous insert (has to be the same scope) to get the id that was inserted. That's just for finding out what id was put in, you really shouldn't be putting ids in yourself.

IDENT_CURRENT(‘tablename’) will give you the last id used, regardless of scope (your current batch of SQL commands).

@@IDENTITY will give you the last id inserted for your current db connection... use with extreme care.

more detail