How Can I get Identitiy field from database while inserting new row in sql server 2005 compact e

How Can I get Identitiy field from database while inserting new row in sql server 2005 compact edition.

Ex:

I am inserting row in a table through SqlQuery ("insert into ....") in which one of the field is of type Identity which generates number automatically. I want that number to pick up that number and used it in child table....

Any solution for it.

In the standard sql server edition you would use scope_identity() but for compact edition I only find @.@.identity

http://msdn2.microsoft.com/en-us/library/ms174021.aspx

Directly after a successful insert select @.@.identity and read its value to get the ID of the inserted row.

|||

this is ok when we have to select the identity filed individually.... my question is

if i fire a sql query [insert into tb1(...) values(...)] through executenonquery this will insert a record in table but i would like to take a value of identity field just inserted with this query. As this value will be inserted in child table..

I hope u r getting me..

Thankx

|||As Andreas said, just do a "SELECT @.@.IDENTITY" immediately after the insert, and you will get the value inserted.

|||

You can prefetch that information using the INFORMATION_SCHEMA:

QA: How do I get IDENTITY information on a SQL Mobile database?

The technique is simple: you query the INFORMATION_SCHEMA and calculate the next IDENTITY based on the values you retrieved.

You can also use a different technique through SqlCeResultSet:

How to retrieve the last inserted IDENTITY in .NET CF

|||One alternate way, instead of using an autoincrementing integer as your identity field, use a GUID. Set the data type to uniqueidentifier, set it as the identity. Then use System.Guid.NewGuid() to create the primary key prior to inserting it into the database. That way you know the primary key before it's ever written out, and saves you from having to dig around the database to determine it.

Arcane
|||

Dear ArcaneCode,

Thankyou for your kind suggestion. I think this is more better then using identity field

Once Again Thanx

|||Using System.Guid.NewGuid() on mobile devices is slow. Please read Peter Foot's comments on this here.