将行添加到数据库中，获取 id 并填充第二个表

本文关键字：id 填充 第二个 获取 添加 数据库 | 更新日期: 2023-09-27 18:36:11

我不擅长.NET，但正在学习（至少在努力！;)）。但是，我正在处理的这段代码让我感到困惑。我想做的是在名为 Comment 的 SQL Server 2008 数据库表中插入一行，然后使用此插入行的 id 用新数据行填充第二个表 （CommentOtherAuthor）。基本上，一条评论可以有多个作者。

代码如下：

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;
    }

关键行是：

sqlHelper.CreateParameter("@commentid", 123)

目前，我只是将注释的 id 硬编码为 123，但实际上我需要它是刚刚插入注释表中的记录的 id。

我只是真的不明白如何在不做新的评论的情况下从表中抓取最后一个插入 

SELECT TOP 1 id FROM Comment ORDER BY id DESC

这对我来说并不是最好的方法。

谁能建议如何让它工作？

非常感谢！

将行添加到数据库中，获取 id 并填充第二个表

SELECT TOP 1 id ...查询很可能在负载下的系统中不会为您提供正确的结果。如果您有 20 或 50 个客户端同时插入评论，当您再次查询表格时，您很有可能会得到别人的id......

我认为这样做的最好方法是：

  • 将OUTPUT子句添加到原始插入项并捕获新插入的ID
  • 将该 ID 用于第二次插入

大致如下：

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

使用这种方法，您的c.Id值现在应该是新插入的ID - 在下一个插入语句中使用它！（注意：现在，您可能总是得到一个1 - 受您的语句影响的行数......

此方法假定您的表Comment具有一列类型为 INT IDENTITY 的列，当您在其中插入新行时，将自动设置该列。

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!
        );
}

假设您使用的是Microsoft SQL Server，则可以设计表注释，以便列 Id 将属性 Identity 设置为 true。这样，每次将行插入表中时，数据库将生成并自动递增 id。

您必须在 SQL 请求中使用以下行：

输出 INSERTED.Id

以便在执行请求时将此 ID 返回到您的 C# 代码。

相关文章：