Update ID on row inserted via stored procedure on DataAdapter InsertCommand

Question

Stored procedure code:

ALTER PROCEDURE [dbo].[procInsertComputer]
    @pName NVARCHAR(128),
    @pDomain NVARCHAR(25) = NULL,
    @pStatus NCHAR(2) = NULL,
    @pNotes NVarChar(2000) = NULL
AS
    SET NOCOUNT ON
    DECLARE @NewID [int]

    IF NOT EXISTS (SELECT 1 FROM tbComputers WHERE Name=@pName)
    BEGIN
        INSERT INTO tbComputers (Name, Domain, Status, Notes)
        VALUES (@pName, @pDomain, @pStatus, @pNotes)

        SET @NewID = SCOPE_IDENTITY()
    END
    ELSE
    BEGIN
        SET @NewID = -1
    END

RETURN @NewID

Insert trigger code:

ALTER TRIGGER [dbo].[tr_tbComputer_Insert] 
ON [dbo].[tbComputers] 
FOR INSERT 
AS
    INSERT INTO tbComputers_Audit(id, Name, Domain, Status, Notes, AuditAction)
        SELECT 
            id, Name, Domain, Status, Notes, 'I' 
        FROM 
            Inserted

CreateAdapter code:

public static SqlDataAdapter CreateAdapter(SqlConnection connection)
{
    if (connection == null)
    {
        throw new ArgumentNullException("connection");
    }

    var command = new SqlCommand("SELECT Name, Domain, Status, Notes FROM tbComputers", connection);

    var adapter = new SqlDataAdapter(command);

    adapter.RowUpdated += (sender, update) =>
    {
        if (update.Status != UpdateStatus.Continue ||
            update.StatementType != StatementType.Insert)
        {
            return;
        }

        var idCommand = new SqlCommand("SELECT SCOPE_IDENTITY()", connection);

        update.Row["Id"] = idCommand.ExecuteScalar();
    };

    var insertCommand = new SqlCommand("procInsertComputer", connection)
    {
        CommandType = CommandType.StoredProcedure
    };

    insertCommand.Parameters.Add("@pName", SqlDbType.VarChar, 128, "Name");
    insertCommand.Parameters.Add("@pDomain", SqlDbType.VarChar, 25, "Domain");
    insertCommand.Parameters.Add("@pStatus", SqlDbType.Char, 2, "Status");
    insertCommand.Parameters.Add("@pNotes", SqlDbType.VarChar, 2000, "Notes");

    adapter.InsertCommand = insertCommand;

    return adapter;
}

Test code:

// Create objects
var databaseConnection = "my connection string"
var adapter = CreateAdapter(databaseConnection)
var dataSet = new Dataset();

// Fill data
adapter.Fill(dataSet, "Table");

// Create new row
var newRow = dataSet.Tables[0].NewRow();
dataSet.Tables[0].Rows.Add(newRow);

// Populate columns
newRow.Name = "TestServer01"
newRow.Status = "BU"
newRow.Domain = "TestLab01.test.au"
newRow.Notes = "test creation via C#"

// Accept changes
adapter.Update(dataSet.Tables[0]);

Problem: the insert works perfectly and adds the new row to the table with it's own uniquely generated ID but the DataRow object i have in C# gets the ID of the triggers insert.

Question: is there an easy way to update the ID from the RETURN of the InsertCommand when the new DataRow is updated?

Research: many people on the answers I've found suggest SELECT SCOPE_IDENTITY() which got me closer as it's now updating the ID, it's just not updating to the correct ID.

@MarkC. Fixed, should have been `newRow` instead of `dataRow` :) — Bluecakes, May 14 '17 at 23:56
`SCOPE_IDENTITY()` inside the stored procedure and `SCOPE_IDENTITY()` you have in another query in C# are different identities with different scope. You return the right one as the procedure result, so read the procedure result from C# instead of calling `SCOPE_IDENTITY`. — GSerg, May 14 '17 at 23:59
@GSerg The adapter automatically uses the `InsertCommand` when i call `adapter.Update(dataSet.Tables[0]);` is there a way i can query the return value of the stored procedure or do i have to handle inserts manually? (and not use `InsertCommand`) — Bluecakes, May 15 '17 at 00:04
Return the values in output parameters or select them from the stored procedure, then `InsertCommand` will [pick it up](http://stackoverflow.com/a/137468/11683). Alternatively, have the `SqlCommandBuilder` do it for you ([1](http://stackoverflow.com/a/1631133/11683), [2](http://stackoverflow.com/a/2514144/11683)) — GSerg, May 15 '17 at 15:07

Update ID on row inserted via stored procedure on DataAdapter InsertCommand

0 Answers0