(a summary of the ADO dot) see what to Remember

1, database connection pool: In the case of a few simultaneous connections,

To open a link to a database guide 1W data Time-consuming

With

The time it takes to turn a data on and off a database connection

What is the difference between the two in fact? Isn't it a lot time-consuming to turn off itself? This is because the database connection pool exists

When you connect to the number of simultaneous open not many cases, you close a link, in fact, this link is not closed, but is. NET secretly hidden, of course, the number of hidden links

is limited, so when you lead a bar, open close a link in this way to guide the data when it is actually not really in "open close connection".

2, Batch data submission: SqlBulkCopy class to read the number attribution to such a text as an example

The first is to read the data, put it in a local large set of DataTable, and then throw the table into the database

DataTable table = new DataTable ();

Table. Columns.Add ("Telnum");//The name of the column within the table you define, whichever

Table. Columns.Add ("City");

Table. Columns.Add ("Teltype");

for (int i = 0;i < lines. Length; i + +)//lines is the collection that has been read from the open text, one line unit

{

Get the string that needs to be filled into the database

String line = Lines[i];

string[] STRs = line. Split ('/t '),//vs '/t ' for tab characters

String telnum = Strs[0];

String city = Strs[1]. Trim (' "');//Remove double quotes on both sides:" Beijing "

String teltype = Strs[2]. Trim (' "');

The data is then populated into the DataTable, but the DataRow object is needed here

DataRow row = table. NewRow ();

row["Telnum"] = telnum;//Be sure to create a table at the beginning. Columns adding columns

row["City" = City;

row["Teltype"] = Teltype;

and associate the row with the table.

Table. Rows.Add (row);

}

The above has already filled the data read from the text into the DataTable, then the table is thrown into the database.

using (SqlBulkCopy bulk = new SqlBulkCopy (CONNSTR))

{

Bulk. DestinationTableName = "T_telnum";//the table name of the data stored in the database

The first parameter is the column name that you define in the DataTable, and the second parameter is the name of the column defined in the database table.

Two columns of the table are associated (mappings!) ）

Bulk. Columnmappings.add ("Telnum", "Telnum");

Bulk. Columnmappings.add ("City", "Telarea");

Bulk. Columnmappings.add ("Teltype", "Teltype");

Bulk. WriteToServer (table);//early work is done, the DataTable is thrown into the database.

}

Not this class, 20 minutes. Use this class for 9 seconds ~~~~~~~ drunk

Personal understanding: First fill the data into the local data set DataTable, which is done locally, just read the string, and then throw the data into the database at once.

and directly read the data into the database, which involves the internal operation, the code volume is obviously huge.

The amount of data is the same, so I think that the time difference is so much, is spent in the database with each other generated a large amount of code. (I do not know so brokered not suitable, to oneself to see--)

(a summary of the ADO dot) see what to Remember