Professional EDP – Commit Techniques

One of the core functions of EQuIS software is to commit new records to the EQuIS database. The commit process involves the EQuIS software passing the data to the Microsoft .NET Framework (e.g., System.Data.SqlClient), which then sends the data to the database (i.e., Microsoft SQL Server) where it is committed to the appropriate table in the database. As of the 7.20.2 Build, there are three different techniques that may be used when committing records to the database. Configurations settings (i.e., SkipBulkCopy and BatchInsert) may be used to control exactly which technique(s) are used by EQuIS. The following table explains each technique.

	Row-by-Row	SqlClient.SqlBulkCopy	BatchInsert
Description	EQuIS functionality that sends one row of data at a time to the database (each row is sent separately).	The native Microsoft .NET Framework SqlBulkCopy class for efficiently loading bulk data into SQL Server. All rows are sent as a batch and processed as a batch.	EQuIS functionality that sends a batch of rows to the database at one time, then processes them individually when they arrive at the database.
Introduced	Prior to EQuIS v6.0	Prior to EQuIS v6.0	Build 7.20.1; revised in Build 7.20.2
Advantages	•Supports all commit types •Supports English and non-English decimal separators •Enables progress/status while rows are committing	•Speed (fastest)	•Supports all commit types •Supports English and non-English decimal separators •Enables progress/status while rows are committing •Supports static or progressive batch size •Speed (faster than row-by-row)
Disadvantages	•Speed (slowest)	•Does not work with non-English decimal separators •Only works if all records in the batch are new records (existing records cause entire batch to fail) •Disable progress/status while rows are committing (may cause timeout for very large datasets) •Cannot control/configure batch size	•Speed (slower than SqlBulkCopy)

Because each technique has advantages, EQuIS supports a hybrid approach that can be controlled by specific configuration settings. Starting in the 7.20.2 Build, the logic is as follows (the same logic is repeated for each table as it is committed to the database):

1.Is SqlBulkCopy disabled for this table (see SkipBulkCopy)? If yes, go to Step 4; if no, go to Step 2.

2.Is a non-English decimal separator being used? If yes, go to Step 4; if no, go to Step 3.

3.Attempt the SqlBulkCopy operation – was it successful (i.e., no errors)? If yes, complete – proceed to next table; if no, go to Step 4.

4.Is BatchInsert enabled for this table (see BatchInsert)? If yes, go to Step 5; if no, go to Step 6.

5.Attempt the BatchInsert operation – was it successful (i.e., no errors)? If yes, complete – proceed to next table; if no, go to Step 6.

6.Use the row-by-row operation.

As noted in the steps above, the SqlBulkCopy operation will be attempted unless it is intentionally disabled; conversely, the BatchInsert operation will not be attempted unless it is intentionally enabled. The default functionality, without any additional configuration, will attempt SqlBulkCopy and then automatically fall back to the row-by-row technique.