如何更有效地将批量记录作为事务保存到SQL server

Question

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

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

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

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

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

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

如果有其他有效的方法，我正在寻求建议。

Answer 1

这是我的方法：

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

对于数据操作，这里是我已经实现了很多次的方法。

1. 创建一个表类型，该类型必须包含需要处理的所有列。

2. 在存储过程中使用该表类型变量作为输入参数，您可以一次性传递n行，这样就不需要在c#处循环多次访问数据库。

3. 如果记录不匹配，则可以在存储过程中插入用户合并语句；如果匹配，则根据需要进行更新。您也可以在事务中执行此操作。

希望这对你有所帮助。

编辑1:基于数据库级别验证的注释。

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

2. DDL级别错误，如数据长度超过或外键约束等

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

一旦您将数据传递到sql server，您就可以在sql server中使用try-and-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','[e-mail]','12345',GETDATE(),GETDATE(),1 UNION
SELECT 2,'James','Streak','[e-mail]','12345',GETDATE(),GETDATE(),1 
EXEC uspInsertUsers @user_Details