[. Net] Using sqlbulkcopy to Improve the Performance of imported data

Use sqlbulkcopy to Improve the Performance of imported data

Bulk insert can be used to import a large number of data to SQL Server, but the inserted files must be in the database machine or a shared folder accessible to the database (I don't know how to set up a shared folder, so that SQL server can access it)

Sqlbulkcopy is a class in. Net that provides the ability to import large amounts of data.

The basic usage is as follows:

Using (sqlbulkcopy BC = new sqlbulkcopy (sqlconn, sqlbulkcopyoptions. tablelock | sqlbulkcopyoptions. useinternaltransaction, null) {BC. bulkcopytimeout = 10*60; BC. batchsize = 10000; BC. destinationtablename = "DBO. destination "; BC. writetoserver (Reader); // reader is an instance of the class inherited from idatareader}

 

You can write code to implement the class inherited from idatareader. There are n more members to implement...

For example, fieldcount, read (), getvalue (int I), close (), etc.

The following is an example of reading a file:

 

// The number of columns returned for the record public int fieldcount {get {return 3 ;}// read the record. This method will automatically call Public bool read () {If (_ reader = NULL) _ reader = new streamreader (_ filepath); string line = _ reader. readline (); If (line! = NULL) {_ currentqueryitem = getrawdata (line); _ count ++; while (_ currentqueryitem = NULL) // if you read records that do not meet the conditions, read the next record {read () ;}return true ;}return false ;}// return the value of column I (item) in a record, this method will be automatically called // sqlbulkcopy should have a loop, from 0 to fieldcount-1, then call the getvalue (int I) method. I guess... Public object getvalue (int I) {If (_ currentqueryitem = NULL) return NULL; Switch (I) {// if the first column of the table in the database is an auto-increment field, the first column is ignored. That is to say, when this method is called, I only starts from 1, so case 0 is not required. It is estimated that. Net internally retrieves the schema of the target table and automatically determines which columns need to be imported from outside. If you are free, study this problem case 0: Return _ currentqueryitem. item1; Case 1: Return _ currentqueryitem. item2; Case 2: Return _ currentqueryitem. item3; default: Throw new indexoutofrangeexception () ;}// release the public void close () {dispose () ;}public void dispose () {If (_ reader! = NULL) _ reader. Close ();}

 

There are some other attributes whose methods need to be implemented by themselves. Of course, it doesn't matter if some attributes are not implemented. The above methods seem important.
Compared with sqldataread, you can determine which methods will be used.

After the experiment, it takes about 2 minutes to insert a single file into the database in one row. If sqlbulkcopy is used, it takes about 10 seconds. In addition, you can implement classes to specify the data to be processed, and you do not need to place files on the database machine. Good.

Forget to mention that the connction object used in sqlbulkcopy can only be sqlconnection. Both sqlbulkcopy. writetoserver (datarow]) and sqlbulkcopy. writetoserver (datatable) are supported.