Wednesday, December 28, 2005

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.

1 comment:

  1. I use a final SELECT, but I return SCOPE_IDENTITY() instead of @@IDENTITY, because @@IDENTITY can be wrong if you have triggers.

    ReplyDelete

Note: Only a member of this blog may post a comment.