How do I get back the identity column value genarated by SQL server in my .NET code

How do I get back the identity column value genarated by SQL server in my .NET code

• TheBlackNoodle

  I would suggest using the SqlCommand.ExecuteScalar method.
  
  Such as:
  
  SqlConnection conn = new SqlConnection("Server=(local);DataBase=northwind;Integrated Security = SSPI");
  
  conn.Open();
  
  SqlCommand comm = new SqlCommand("insert into EmployeesTest(EmployeeId, LastName, FirstName, Title) values(7, 'adsf', 'asdf', 'asdfa') select @@IDENTITY", conn);
  
  object obj = comm.ExecuteScalar();
  
  

• MikG

  Another option is to use your ExecuteNonQuery() method and return the identity as an output parameter. Theoretically this should be faster than ExecuteScalar, because the database avoids the overhead of having to return rows back.

  str = "Insert INTO TblAccount(Name) VALUES (@Name) Set @AccountNumber = SCOPE_IDENTITY()";

  SqlParameter[] sqlParams = new SqlParameters[2];

  sqlParams[0] = new SqlParameter("@Name", SqlDbType.Char);

  sqlParams[0].Value = "whatever";

  sqlParams[1] = new SqlParams("@AccountNumber",...);

  sqlParams[1].Direction = ParameterDirection.Output;

  ...

  cm.ExecuteNonQuery();

  int accountNumber = (int)sqlParams[1].Value; // assuming integer

  For more on SCOPE_IDENTITY() vs. @@IDENTITY you can read this:

  http://davidhayden.com/blog/dave/archive/2006/01/17/2736.aspx

  Regards,

  Dave

  
  
  

• bosstan1

  Thanks a lot, u re really great guys
  
  

• Epiana

  Except dont use @@IDENTITY, use SCOPE_IDENTITY. This is a common mistake, and most of the time there is little difference; however, in some cases (mainly involving triggers), @@IDENTITY will not give you back the id of the record you just inserted, but the id of a record that was inserted somewhere else in response to your insert.
  
  

• Anonymo3qetwf

  A previous poster said to use a command object like this to fetch the database-generated primary key:

  SqlCommand comm = new SqlCommand("insert into EmployeesTest(EmployeeId, LastName, FirstName, Title) values(7, 'adsf', 'asdf', 'asdfa') select @@IDENTITY", conn);

  I'm relatively new at this, so I've never seen a sql statement of the form "insert .... select @@identity" as a single statement. Is there somewhere I can go to read about this construct How does the statement specify what field to put the @@identity value in

  (Also in my recent sqlserver 2005 readings they suggest using "select IDENT_CURRENT" in preference to either "select SCOPE_IDENTITY" or "select @@identity".)

  
  
• How do I get back the identity column value genarated by SQL server in my .NET code