c# - 如何更有效地将批量记录作为事务保存到 SQL Server？

问题描述

我正在开发一个 C# MVC 应用程序。在此应用程序中，用户正在从 EXCEL 电子表格上传数据，并且数据正在显示到网格中。

在它显示到网格后，用户点击“验证数据”按钮。应用程序需要执行 UI（数据长度、空字段、数据格式等）验证，此外还需要 SQL 验证，例如。记录不应该已经存在，任何约束等。

在验证数据后，会向用户显示与每一行相关的任何错误，以便他/她可以对粘贴的数据进行更正，然后将数据作为事务保存到 SQL Server 数据库。

我正在考虑这样做的一种方法是在 C# 代码中循环数据，并通过调用带有返回语句的一些存储过程来执行每一行的验证，然后可能将相同的数据存储在数据集中，然后在网格中显示给用户。然后当他提交时，在事务中循环执行插入语句。

问题是我正在考虑的方法将使数据库命中次数增加一倍。

因此，如果网格中有 100 行，它将需要 200 次数据库命中。

如果有另一种有效的方法可以做到这一点，我正在寻求建议。

标签： c#sql-servertransactionsbulkinsertbulk-load

这是我的方法：

您可以在客户端验证所有 UI 端验证，例如长度等。这样您就无需前往应用程序和数据库服务器。

对于数据操作，这里是我多次实施的方法。

创建一个必须包含您需要处理的所有列的表类型。
在存储过程中使用该表类型变量作为输入参数，您可以在其中一次性传递 n 行，这样您就不需要在 c# 处循环以多次访问数据库。
如果记录不匹配，则存储过程中的用户合并语句可以插入它，如果匹配，则可以根据需要更新它。您也可以在事务中执行此操作。

希望这会帮助你。

编辑 1：根据您对数据库级别验证的评论。

数据库端会有两种类型的错误。1.数据本身不会是sql表定义所期望的格式，比如数据类型转换失败。

DDL 级别错误，例如数据长度超出或外键约束等。

我建议对可以在 c# 级别编码的数据进行所有可能的验证。就像基于您的目标列的数据长度一样。调用存储过程之前的数据类型，您可以在 c# 级别过滤此类记录。在此级别，您可以进行最大程度的验证。

将数据传递到 sql server 后，您可以在 SQL server 中使用try 和 catch来实现失败行的逻辑。将失败的行保留在临时表中，您可以稍后返回并成功插入所有行。

编辑2：这是可能的代码。

CREATE TABLE Users
(
    Idx             BIGINT IDENTITY(1,1),
    UserID          UNIQUEIDENTIFIER,
    FirstName       VARCHAR(100),
    LastName        VARCHAR(100),
    Email           VARCHAR(100),
    UserPassword    VARCHAR(100),
    InsertDate      DATETIME,
    UpDateDate      DATETIME,
    IsActive        BIT,
 CONSTRAINT [Users_PK] PRIMARY KEY CLUSTERED 
(
    [UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

CREATE TYPE UT_Users AS TABLE  
(  
    Idx             INT,
    FirstName       VARCHAR(100),
    LastName        VARCHAR(100),
    Email           VARCHAR(100),
    UserPassword    VARCHAR(100),
    InsertDate      DATETIME,
    UpDateDate      DATETIME,
    IsActive        BIT
) 
GO

CREATE PROCEDURE uspInsertUsers(@user_Details [UT_Users]) READONLY
AS  
BEGIN  
    DECLARE @Counter INT=1
    DECLARE @TotalRows INT=0

    SELECT @TotalRows = COUNT(1) FROM @user_Details

    WHILE @TotalRows>@Counter
        BEGIN  
            TRY
                BEGIN
                    INSERT INTO dbo.Users  
                    SELECT * FROM @user_Details WHERE @Counter = Idx
                END
            CATCH
                BEGIN
                    --write code for catching the error as per your need. Store row in temp tables and return the temp table at the end
                END
            SET @Counter = @Counter+1
        END  
END
GO
DECLARE @user_Details AS [UT_Users];

INSERT @user_Details
SELECT 1,'Rahul','Neekhra','rahul@india.com','12345',GETDATE(),GETDATE(),1 UNION
SELECT 2,'James','Streak','streak@usa.com','12345',GETDATE(),GETDATE(),1 

EXEC uspInsertUsers @user_Details

c# - 如何更有效地将批量记录作为事务保存到 SQL Server？

问题描述

解决方案

推荐阅读