CODEWITHSUNDEEP
sql-servert-sqlado.net
Jonathan Wood
Jonathan Wood

Reputation: 67195

Returning ID of new row when ID is uniqueidentifier

I have created a SQL Server table that uses uniqueidentifier as the primary key. I set the Default Value or Binding to newid(). (I would like to set the Identity Specification for this column, but that isn't supported for uniqueidentifier types.)

I'm then using ADO.NET to add a row to this table.

SqlComment command = new SqlCommand("INSERT INTO [User] (Name) VALUES (@name); 
                                     SELECT SCOPE_IDENTITY()", Connection);
command.Parameters.AddWithValue("@name", "Joe Smoe");
Guid userId = (Guid)command.ExecuteScalar();

However, the last line fails because ExecuteScaler() returns null. It appears that, since a uniqueidentifier cannot be the table's identity, SCOPE_IDENTITY() returns null (as does @@IDENTITY).

Okay, so is there another way to retrieve the newly added ID using ADO.NET?

Upvotes: 1

Views: 5246

Answers (2)

Gregory A Beamer
Gregory A Beamer

Reputation: 17010

Scope_Identity() focuses on an IDENTITY field, so it will never yield anything. You need to output from INSERTED instead. Even though this page is not focused on your particular problem, it should give you some clues:

Return ID on INSERT?

My normal direction is a stored procedure, but you can chain commands, as you have done. The stored procedure makes things a bit easier, as you can create an output parameter for the procedure, but outputting a value works fine.

EDITED to show specific example:

Assume the following table:

CREATE TABLE [dbo].[MyTable]
(
    [Id] [uniqueidentifier] PRIMARY KEY NOT NULL DEFAULT NEWID(),
    [Name] [varchar](50) NOT NULL,
)

The following program will output the new GUID created from NewID():

class Program
{
    static void Main(string[] args)
    {

        var connString = ConfigurationManager.ConnectionStrings["testDB"].ToString();
        var cmdString = "INSERT INTO MyTable (Name) OUTPUT Inserted.Id VALUES ('Name')";

        var connection = new SqlConnection(connString);
        var command = new SqlCommand(cmdString, connection);

        Guid outputValue;

        try
        {
            connection.Open();
            //Convert to Guid here instead
            Console.WriteLine(command.ExecuteScalar().ToString());
        }
        finally
        {
            connection.Dispose();
        }

        Console.Read();

    }
}

Upvotes: 1

M.Ali
M.Ali

Reputation: 69504

SCOPE_IDENTITY() is only used for Identity value, for guid values you would need to use the OUTPUT clause with a table variable. 

DECLARE @NewGuid TABLE(NewValue UNIQUEIDENTIFIER);

INSERT INTO [User] (Name)
OUTPUT inserted.pk_ColName INTO  @NewGuid(NewValue)
VALUES (@name); 

SELECT * FROM @NewGuid  --<-- here you will have the new GUID Value

C# code would look something like....

string cmd = "DECLARE @NewGuid TABLE(NewValue UNIQUEIDENTIFIER);
                INSERT INTO [User] (Name)
                OUTPUT inserted.pk_ColName INTO  @NewGuid(NewValue)
                VALUES (@name); 
                SELECT @newID = NewValue FROM @NewGuid;"


SqlCommand command = new SqlCommand(cmd, Connection);
cmd.Parameters.AddWithValue("@name", "Joe Smoe");
cmd.Parameters.Add("@newID", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output;
Guid userId = (Guid)cmd.ExecuteScalar();

Personally I would put the whole thing in a stored procedure.

Upvotes: 12

Related Questions