Unexpectedly large volume import data SqlBulkCopy class, mysql large volume import data

Unexpectedly large volume import data SqlBulkCopy class, mysql large volume import data

Because we need to perform a small number attribution query function, because the SqlDataAdapter class is used to import external telephone attribution data (text files). There is not much data, and only 40 thousand has multiple entries, there is only one table. the phoneBook table uses DataTable and SqlDataAdapter. It has been tested for three times, with an average of 18 seconds, but I think it is too slow, baidu went to [SQL Server batch import data] and found the SqlBulkCopy artifact, which was imported within one second.

SqlBulkCopy is used to transfer large amounts of data in a database. It is usually used for updates between the new and old databases. The key point is that, even if the table structure is different, you can also establish a ing between the table fields or field locations to import the required data to the target database.

The following code Tests 1 million data records. It takes about 8 seconds for several tests.

/// <Summary> /// use the SqlBulkCopy class to update data in batches. // </summary> public static void SqlBulkCopyDemo () {String connStr = ConfigurationManager. connectionStrings ["connStr"]. toString (); // obtain the table structure and data DataTable student = new DataTable () from the database; SqlDataAdapter adapter = new SqlDataAdapter ("select * from student", connStr); adapter. fill (student); // Add data to the table DataRow dr; Random r = new Random (); Stopwatch st = new Stopwa Tch (); for (int I = 0; I <1000000; I ++) // 1 million data records {// columns in the data table: name, no, age, sex dr = student. newRow (); dr [0] = "Xiaohua" + I; dr [1] = 108 + I; dr [2] = r. next (9, 40); // This is because the data table has constraints. dr [3] = (I % 2 = 0? "Male": "female"); // student is also restricted. rows. add (dr);} SqlBulkCopy bulk = new SqlBulkCopy (connStr); bulk. destinationTableName = "student"; // set the target table. Here is the student table bulk in the database. columnMappings. add (0, 0); // create a buling relationship bulk. columnMappings. add (1, 1); bulk. columnMappings. add (2, 2); bulk. columnMappings. add (3, 3); st. start (); // Start timing bulk. writeToServer (student. getChanges (); st. stop (); // end the timer Console. writeLine ("data inserted successfully, time consumed:" + st. elapsedMilliseconds + "millisecond ");}

The test result is as follows:

 

1. SqlBulkCopy class Constructor

Conn indicates a SqlConnection object.

ConnStr indicates the database connection string

There are also several unfamiliar objects: SqlBulkCopyOptions and SqlTransaction

1.1 SqlBulkCopyOptions class

This class is an enumeration type:

Object	Value	Remarks
Default	0
KeepIdentity	1	Reserve the source ID value. If this parameter is not specified, the target assigns an id value.
CheckConstraints	2	Check constraints while inserting data. By default, constraints are not checked.
TableLock	4	Obtain the update locks in batches during the bulk copy operation. If not specified, the row lock is used.
KeepNulls	8	Retain null values in the target table regardless of the default value. If not specified, the null value will be replaced by the default value (if applicable)
FireTriggers	16	If this parameter is specified, the server triggers the insert trigger for the row inserted into the database.
UseInternalTransaction	32	If specified, the batch copy operation is performed in the transaction. If this option is specified and the System. Data. SqlClient. SqlTransaction object is provided for the constructor, System. ArgumentException (parameter exception) occurs ). Because two transactions conflict.

1.2. SqlTransaction class

This class is a transaction class and a seal class that implements the DbTransaction abstract class.

 

 

 

2. Common attributes of the SqlBulkCopy class

Attribute name	Function	Remarks
BatchSize	Set or obtain the number of rows to be updated to the server (that is, the target table)	The value is int,
BulkCopyTimeout	Set or obtain the timeout value	The default value is 30 seconds. If it is set to 0, there is no limit to wait, The value is int, in seconds.
DestinationTableName	Set or obtain the name of the target table on the server	That is, the target table for batch update, The value belongs to the String type.
EnableStreaming	Sets or obtains whether IDataReader object data can be transmitted.	True is supported, The value belongs to the bool type.
Policyafter	Sets or obtains the number of rows to be processed before a notification event is generated.	The default value is 0, The value belongs to the int type,
ColumnMappings	Obtains the ing between columns in the data source and the columns in the target table.	The returned value is SqlBulkCopyColumnMappingCollection.

2.1 The SqlBulkCopyColumnMappingCollection type in the table is a ing collection class, which is a set of ing relationships between the columns in the target table and those in the source table.

This class is a sealed class and cannot be inherited. It implements a CollectionBase abstract class.

SqlBulkCopyColumnMappingCollection does not provide a constructor, and we do not need to go to the newat object. It mainly uses several of its overloaded Add () methods.

Add () has five overload methods:

• SqlBulkCopyColumnMapping Add (SqlBulkCopyColumnMapping bulkCopyColumnMapping );
• SqlBulkCopyColumnMapping Add (string sourceColumn, string destinationColumn );
• SqlBulkCopyColumnMapping Add (int sourceColumnIndex, string destinationColumn );
• SqlBulkCopyColumnMapping Add (string sourceColumn, int destinationColumnIndex );
• SqlBulkCopyColumnMapping Add (int sourceColumnIndex, int destinationColumnIndex );

The four methods are similar. They are all corresponding column names or column locations.

The first method is to add a constructed SqlBulkCopyColumnMapping object,

He also has a set of common methods:

Method Name	Function	Remarks
Clear ();	Clear mappings in a set
Contains (SqlBulkCopyColumnMapping value );	Determines whether the specified ing is included.
IndexOf (SqlBulkCopyColumnMapping value );	Returns the location of the specified ing.
Remove (SqlBulkCopyColumnMapping value );	Removes the specified ing.
RemoveAt (int index );	Removes the ing between specified locations.
Insert (int index, SqlBulkCopyColumnMapping value );	Insert ing relationships at specified locations
CopyTo (SqlBulkCopyColumnMapping [] array, int index );	Copy the ing from the specified position to the specified array	Position in the set specified by index, Instead of the badge in the array

 

3. Common SqlBulkCopy Methods

• WriteToServer. This method is reloaded four times to write data to the destination table.

WriteToServer (DataRow [] rows );	Write all elements of the DataRow array to the target table.
WriteToServer (DataTable table );	Write all rows in the DataTable table to the target table.
WriteToServer (IDataReader reader );	Writes data from the specified IDataReader object to the target table.
WriteToServer (DataTable table, DataRowState rowState );	Write all rows in the specified status in the DataTable to the target table.

[For the DataRowState row in the preceding table, refer to the AcceptChanges () method of the able in this blog and the RowState attribute of DataRow]

This class also provides eight asynchronous writing methods. I have not fully understood them yet, so I will not put them on.

Since there can be write operations, this class should be similar to a stream. It also has a Close () method to Close the SqlBulkCopy instance.