Add row into database, get id and populate second table

I want to insert a row into a SQL Server 2008 database table called Comment, then use the id of this inserted row to populate a second table (CommentOtherAuthor) with new rows of data. Basically, a comment can have multiple authors.

Here's the code:

public static Comment MakeNew(int parentNodeId, string firstname, string surname, string occupation, string affiliation, string title, string email, bool publishemail, bool competinginterests, string competingintereststext, string[] otherfirstname, string[] othersurname, string[] otheroccupation, string[] otheraffiliation, string[] otheremail, bool approved, bool spam, DateTime created, string commentText, int statusId)
{
        var c = new Comment
            {
                ParentNodeId = parentNodeId,
                FirstName = firstname,
                Surname = surname,
                Occupation = occupation,
                Affiliation = affiliation,
                Title = title,
                Email = email,
                PublishEmail = publishemail,
                CompetingInterests = competinginterests,
                CompetingInterestsText = competingintereststext,
                OtherFirstName = otherfirstname,
                OtherSurname = othersurname,
                OtherOccupation = otheroccupation,
                OtherAffiliation = otheraffiliation,
                OtherEmail = otheremail,
                Approved = approved,
                Spam = spam,
                Created = created,
                CommenText = commentText,
                StatusId = statusId
            };

        var sqlHelper = DataLayerHelper.CreateSqlHelper(umbraco.GlobalSettings.DbDSN);

        c.Id = sqlHelper.ExecuteScalar<int>(
            @"insert into Comment(mainid,nodeid,firstname,surname,occupation,affiliation,title,email,publishemail,competinginterests,competingintereststext,comment,approved,spam,created,statusid) 
                values(@mainid,@nodeid,@firstname,@surname,@occupation,@affiliation,@title,@email,@publishemail,@competinginterests,@competingintereststext,@comment,@approved,@spam,@created,@statusid)",
            sqlHelper.CreateParameter("@mainid", -1),
            sqlHelper.CreateParameter("@nodeid", c.ParentNodeId),
            sqlHelper.CreateParameter("@firstname", c.FirstName),
            sqlHelper.CreateParameter("@surname", c.Surname),
            sqlHelper.CreateParameter("@occupation", c.Occupation),
            sqlHelper.CreateParameter("@affiliation", c.Affiliation),
            sqlHelper.CreateParameter("@title", c.Title),
            sqlHelper.CreateParameter("@email", c.Email),
            sqlHelper.CreateParameter("@publishemail", c.PublishEmail),
            sqlHelper.CreateParameter("@competinginterests", c.CompetingInterests),
            sqlHelper.CreateParameter("@competingintereststext", c.CompetingInterestsText),
            sqlHelper.CreateParameter("@comment", c.CommenText),
            sqlHelper.CreateParameter("@approved", c.Approved),
            sqlHelper.CreateParameter("@spam", c.Spam),
            sqlHelper.CreateParameter("@created", c.Created),
            sqlHelper.CreateParameter("@statusid", c.StatusId));

        c.OnCommentCreated(EventArgs.Empty);

        for (int x = 0; x < otherfirstname.Length; x++)
        {
            sqlHelper.ExecuteScalar<int>(
                @"insert into CommentOtherAuthor(firstname,surname,occupation,affiliation,email,commentid) values(@firstname,@surname,@occupation,@affiliation,@email,@commentid)",
                sqlHelper.CreateParameter("@firstname", otherfirstname[x]),
                sqlHelper.CreateParameter("@surname", othersurname[x]),
                sqlHelper.CreateParameter("@occupation", otheroccupation[x]),
                sqlHelper.CreateParameter("@affiliation", otheraffiliation[x]),
                sqlHelper.CreateParameter("@email", otheremail[x]),
                sqlHelper.CreateParameter("@commentid", 123)
            );
        }

        if (c.Spam)
        {
            c.OnCommentSpam(EventArgs.Empty);
        }

        if (c.Approved)
        {
            c.OnCommentApproved(EventArgs.Empty);
        }

        return c;
    }

The key line is:

sqlHelper.CreateParameter("@commentid", 123)

At the moment, I'm just hard-coding the id for the comment as 123, but really I need it to be the id of the record just inserted into the comment table.

I just don't really understand how to grab the last insert from the table Comment without doing a new

SELECT TOP 1 id FROM Comment ORDER BY id DESC

which doesn't strike me as the best way to do this.

Can anyone suggest how to get this working?

Many thanks!

Solution

That SELECT TOP 1 id ... query most likely wouldn't give you the proper results anyway in a system under load. If you have 20 or 50 clients inserting comments at the same time, by the time you query the table again, chances are very high you would be getting someone else's id ...

The best way I see to do this would be:

add an OUTPUT clause to your original insert and capture the newly inserted ID
use that ID for your second insert

Something along the lines of:

c.Id = sqlHelper.ExecuteScalar<int>(
        @"insert into Comment(......) 
          output Inserted.ID                
          values(.............)",

Using this approach, your c.Id value should now be the newly inserted ID - use that in your next insert statement! (note: right now, you're probably always getting a 1 back - the number of rows affected by your statement ...)

This approach assumes your table Comment has a column of type INT IDENTITY that will be automatically set when you insert a new row into it.

for (int x = 0; x < otherfirstname.Length; x++)
{
        sqlHelper.ExecuteScalar<int>(
            @"insert into CommentOtherAuthor(.....) values(.....)",
            sqlHelper.CreateParameter("@firstname", otherfirstname[x]),
            sqlHelper.CreateParameter("@surname", othersurname[x]),
            sqlHelper.CreateParameter("@occupation", otheroccupation[x]),
            sqlHelper.CreateParameter("@affiliation", otheraffiliation[x]),
            sqlHelper.CreateParameter("@email", otheremail[x]),
            sqlHelper.CreateParameter("@commentid", c.Id)  <<=== use that value you got back!
        );
}