How To Save Bulk Records As Transaction To Sql Server More Efficiently?

I am working on a C# MVC application. In this application user is uploading data from EXCEL spreadsheet and data is showing to grid. After it has been showing to grid, user hit '

Solution 1:

Here is my approach:

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. Create a table type which must have all the columns which you need to process.

2. 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.

3. 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.

Hope this will help you.

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

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.

2. DDL level error like data length exceeding or foreign key constrains etc.

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.

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.

EDIT 2: Here is the possible code.

CREATETABLE Users
(
    Idx             BIGINTIDENTITY(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 ASTABLE  
(  
    Idx             INT,
    FirstName       VARCHAR(100),
    LastName        VARCHAR(100),
    Email           VARCHAR(100),
    UserPassword    VARCHAR(100),
    InsertDate      DATETIME,
    UpDateDate      DATETIME,
    IsActive        BIT
) 
GO

CREATEPROCEDURE uspInsertUsers(@user_Details [UT_Users]) READONLY
ASBEGINDECLARE@CounterINT=1DECLARE@TotalRowsINT=0SELECT@TotalRows=COUNT(1) FROM@user_Details

    WHILE @TotalRows>@CounterBEGIN  
            TRY
                BEGININSERTINTO 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 endENDSET@Counter=@Counter+1ENDEND
GO
DECLARE@user_Details AS [UT_Users];

INSERT@user_Details
SELECT1,'Rahul','Neekhra','rahul@india.com','12345',GETDATE(),GETDATE(),1UNIONSELECT2,'James','Streak','streak@usa.com','12345',GETDATE(),GETDATE(),1EXEC uspInsertUsers @user_Details