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.

2 comments:

  1. In the current project I'm developing, using the EntLib, I use this code:

    The Table on SQL Server:
    CREATE TABLE [dbo].[Utenti] (
    [nUid] [int] IDENTITY (1, 1) NOT NULL ,
    [uid] [smallint] NOT NULL ,
    [name] [userName] NULL ,
    [flgAttivo] [bit] NULL
    ) ON [PRIMARY]
    GO

    The Stored Procedure alway on SQL Server:
    CREATE procedure dbo.Utenti_Insert
    @uid smallint,
    @name userName,
    @flgAttivo bit,
    @nUid int OUTPUT
    As
    Set @nUid = 0
    INSERT INTO Utenti
    (uid,name,flgAttivo)
    VALUES
    (@uid,@name,@flgAttivo)
    Set @nUid = @@identity
    GO


    The C# code in the application (a Unit Test):


    DBCommandWrapper insertCommand = databaseTest.GetStoredProcCommandWrapper("Utenti_Insert");

    insertCommand.AddInParameter( "@uid", DbType.Int16, systemUserID);
    insertCommand.AddInParameter( "@name", DbType.String, Name);
    insertCommand.AddInParameter( "@flgAttivo", DbType.Boolean, FlagAttivo );
    insertCommand.AddOutParameter( "@nUid",DbType.Int32, 4);

    databaseTest.ExecuteNonQuery( insertCommand);
    int internalUserID = (int)insertCommand.GetParameterValue("@nUid");

    Bye and thanks for writing this blog I've found so many inspiration in it!

    Antonio

    ReplyDelete
  2. 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.