时间戳

首页 > 解决方案 > 如何基于使用 ASP.NET Core 调用的存储过程插入具有多个外键记录的主键记录

sql-server - 如何基于使用 ASP.NET Core 调用的存储过程插入具有多个外键记录的主键记录

问题描述

我正在构建一个基于 ASP.NET Core 的内容/知识库系统。一个页面将显示一个带有多个下拉菜单的富文本编辑器（下拉菜单的数量不受限制，但很可能不超过 10 个）。

例如，富文本编辑器中的内容是关于在热带环境中的淤泥土中种植有机咖啡。在此示例中，将有 3 个下拉框：

  1. 作物是咖啡
  2. 气候属热带
  3. 土壤类型为淤泥

点击提交时，页面会将内容存储在内容表中，并在分类-内容表中添加三条记录，该表是分类值与内容之间的nm关系表。

在 SQL 中，我想使用单个事务来执行此操作，以确保全部插入或不插入。

我正在考虑创建一个临时表并首先在其中插入 3 个值。然后使用存储过程调用以添加内容并将临时表中的记录作为单个事务复制到分类内容表中。

这是因为我没有看到将 C# 中的值数组放入存储过程的选项。有什么建议么？

标签： sql-serverasp.net-corestored-procedures

解决方案

您可以使用表值参数，也可以使用 Begin、Commit 和 RollBack Transaction。您可以执行以下操作

CREATE TYPE [dbo].[Content] AS TABLE
(
    [Column1]                 VARCHAR (1)   NOT NULL,
    [Column2]                 VARCHAR (100) NOT NULL,
    .
    .
    .
    [ColumnN]                  VARCHAR (30)  NOT NULL
)

CREATE TYPE [dbo].[ContentClassification] AS TABLE
(
    [Column1]                 VARCHAR (1)   NOT NULL,
    [Column2]                 VARCHAR (100) NOT NULL,
    .
    .
    .
    [ColumnN]                  VARCHAR (30)  NOT NULL
)

CREATE PROCEDURE [dbo].[USP_CONTENT_INSERT]
    @Content as [Content] READONLY,
    @ContentClassification as [ContentClassification] READONLY,
    @ErrorStatus int Out

AS

BEGIN TRY

     BEGIN TRANSACTION

     -- Insert in to Content
      INSERT INTO [tbl_ContentTable]
      (     [Column1], [Column2],... [ColumnN] )
      SELECT  [Column1], [Column2],..[ColumnN] FROM @Content

      -- Insert in to ContentClassification
      INSERT INTO [tbl_ContentClassification]
      (     [Column1], [Column2],... [ColumnN] )
      SELECT  [Column1], [Column2],...[ColumnN] FROM @ContentClassification

     set  @OutStatus = 1;

     COMMIT TRANSACTION

END TRY
BEGIN CATCH
    IF(@@TRANCOUNT > 0)
        ROLLBACK TRANSACTION;
     set  @OutStatus = -1;
END CATCH

以上将确保为给定的父母插入所有受抚养人。如果发生错误，它将是一个回滚。

或者，您也可以在 C# 代码中使用连接对象的 BeginTransaction 对象执行相同操作。下面是来自 MSDN https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.begintransaction?view=netframework-4.8的代码片段

private static void ExecuteSqlTransaction(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                SqlTransaction transaction;

                // Start a local transaction.
                transaction = connection.BeginTransaction("SampleTransaction");

                // Must assign both transaction object and connection
                // to Command object for a pending local transaction
                command.Connection = connection;
                command.Transaction = transaction;

                try
                {
                    command.CommandText =
                        "Insert into tbl_ContentTable (Column1, Column2, ... ColumnN) VALUES (V1, V2,...VN)";
                    command.ExecuteNonQuery();
                    command.CommandText =
                         "Insert into tbl_ContentTable (Column1, Column2, ... ColumnN) VALUES (V1, V2,...VN)";
                    command.ExecuteNonQuery();

                    // Attempt to commit the transaction.
                    transaction.Commit();

                }
                catch (Exception ex)
                {
                    Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
                    Console.WriteLine("  Message: {0}", ex.Message);

                    // Attempt to roll back the transaction.
                    try
                    {
                        transaction.Rollback();
                    }
                    catch (Exception ex2)
                    {
                        // This catch block will handle any errors that may have occurred
                        // on the server that would cause the rollback to fail, such as
                        // a closed connection.
                        Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                        Console.WriteLine("  Message: {0}", ex2.Message);
                    }
                }
            }
        }

希望这可以帮助！

推荐阅读