如何将批量记录作为事务更有效地保存到SQL Server？ [英] How to save bulk records as transaction to SQL server more efficiently?

问题描述

我正在研究C＃MVC应用程序。在此应用程序中，用户正在从EXCEL电子表格上传数据，并且数据显示在网格中。

I am working on a C# MVC application. In this application user is uploading data from EXCEL spreadsheet and data is showing to grid.

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

After it has been showing to grid, user hit 'validate data' button. Application needs to perform UI (data length, empty field, data formats, etc.) validation and additionally SQL validations are also required for eg. record should not already exists already, any constraints, etc.

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

After validation data is displayed to user for any errors associated with each row, so that he\she can make corrections in pasted data and then save data as a transaction to SQL server database.

我想这样做的一种方法是循环C＃代码中的数据，并通过使用return语句调用一些存储过程来对每一行执行验证，然后存储相同的内容数据可能存储在数据集中，然后在网格中显示给用户。然后，当他提交时，在事务中循环执行插入语句。

One way I am thinking to do this is loop the data in the C# code and perform the validations for each row by calling some stored procedure with return statements then store the same data probably in a dataset and then display to user in the grid. Then when he Submits, perform insert statements in a loop in a transaction.

问题是我正在考虑的方法会使数据库命中次数翻倍。

The problem is that the approach which I am thinking about will double the number of database hits.

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

So if there are 100 rows in the grid, it will entail 200 database hits.

我正在寻找告知是否还有其他有效的方法。

I am seeking advise if there is another efficient way to do this.

推荐答案

这是我的方法：

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

You can validate all your UI side validation on client side for example length etc. So that you don't need to travel to application as well as database server.

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

For data operation here is the approach which I have implemented many times.

1. 创建必须具有的表类型所有需要处理的列。

1. Create a table type which must have all the columns which you need to process.

使用该表类型变量作为存储参数作为输入参数，可以一次性传递n个行因此，您无需在c＃处循环就可以多次访问数据库。

Use that table type variable into stored procedure as input parameter where you can pass n-number of rows in one go so that you do not need to loop at c# to hit database multiple times.

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

User merge statement inside the stored procedure if record is not matching you can insert it and if matching you can update it if needed. You can also do this action inside the transaction.

希望这对您有所帮助。

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

EDIT 1: Based on your comment for database level validation.

出现两种错误数据库方面。
1.数据本身不会采用sql表定义所期望的格式，例如数据类型转换失败。

There would be two types of error at database side. 1. Data itself won't be in the format as is expected by sql table definition like data type conversion failed.

1. DDL级别错误，例如数据长度超过或受到外键约束等。

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

I would recommend that do all possible validation of data you can code at c# level. Like length of data based on your destination column. Type of data before calling to stored procedure and you can filter such records at c# level. At this level you can do maximum validations.

一旦将数据传递到sql server，就可以使用在SQL Server中尝试并捕获实现失败行的逻辑。将失败的行保留在临时表中，您可以稍后返回并插入所有成功的行。

Once you pass the data to sql server you can use try and catch in SQL server where you can implement logic of failed rows. Keep failed rows in temp table which you can return later and insert all successful.

编辑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

这篇关于如何将批量记录作为事务更有效地保存到SQL Server？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！