Returning an Id from SQL Server

I found myself in a familiar situation recently:

• Create an object instance that represents a row in a database.
  
• The table must maintain an identity column that the users use to query the table.
  
• The instance will be created on the client side, but the id must come from the database.
  
• You may not use a GUID, because the customer refuses to use them.
  

I'm not sure exactly how I came upon this idea, but I thought it was interesting. It's either cool, absolutely horrid, or both.

Assume this table:

CREATE TABLE [dbo].[bars] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [bar] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

This C# can be used to insert and return the identity:

SqlConnection connection = new SqlConnection("initial catalog=foo; data source=blah; uid=me; pwd=right");
SqlCommand command = new SqlCommand("insert into bars values ('some value') select @@identity",connection);
connection.Open();
int identity = int.Parse(command.ExecuteScalar().ToString());
connection.Close();

Drop me a line if you've successfully (or unsuccessfully) done something similar.