DataTable BULK Insert Database

DataTable Bulk Insert Database

Recently in the Excel file import into the database, with the program to write, because of the large amount of data so slow, later adopted the SqlBulkCopy class, solve the problem of speed, I insert statement, sqldataadapter.update (dataset , tablename); SqlBulkCopy. WriteToServer (DataTable); the performance of the three methods is compared:

1. Generate the Test DataTable table, the table structure is as follows:

UniqueID (primary key, autogrow) | CompanyName | Companycode | Address | Owner | Memo

A total of 6 fields.

SqlConnection SqlConnection = new SqlConnection (connectionString);
SqlDataAdapter SqlDataAdapter = New SqlDataAdapter ("select * from Table_1 where 1=2", SqlConnection);
DataSet DataSet = new DataSet ();
SqlDataAdapter. Fill (DataSet, "Table_1");
DataTable datatable = DataSet. Tables[0];
Generate 20,000 Records

for (int i = 0; i < 20000; i++)
{
 DataRow datarow = DataTable. NewRow ();
 datarow["CompanyName"] = "CompanyName" +string. Format ("{0:0000}", i);
 datarow["Companycode"] = "Companycode" + string. Format ("{0:0000}", i);
 datarow["Address" = "address" + string. Format ("{0:0000}", i);
 datarow["owner"] = "owner" + string. Format ("{0:0000}", i);
 Datarow["Memo"] = "Memo" + String. Format ("{0:0000}", i);

DataTable. Rows.Add (DataRow);
}

2. Use the SqlCommand.ExecuteNonQuery () method to insert

foreach (DataRow DataRow in DataTable. Rows)
{
String sql = "INSERT into [table_1]

" VALUES (' "+ datarow[" CompanyName "]. ToString () + "'" +
using (SqlConnection sqlconn = new SqlConnection (connectionString))
{
       sqlconn. Open ();

SqlCommand SqlCommand = new SqlCommand (sql, sqlconn);
SqlCommand. ExecuteNonQuery ();
sqlconn. Close ();
}
}

Insert 20,000 record time: 00:00:29.7336000

3, use Sqldataadapter.update (dataset,tablename);

sqlcommand InsertCommand = new SqlCommand (" INSERT into [Table_1] ([Companyname],[companycode],[address],[owner],[memo]) "+
" VALUES (@CompanyName, @CompanyCode, @Address, @Owner, @Memo) ", New SqlConnection (connectionString));
insertcommand. Parameters.Add ("@CompanyName", SqlDbType.NChar, "CompanyName");
insertcommand. Parameters.Add ("@CompanyCode", SqlDbType.NChar, "Companycode");
insertcommand. Parameters.Add ("@Address", SqlDbType.NChar, 255, "Address");
insertcommand. Parameters.Add ("@Owner", SqlDbType.NChar, +, "Owner");
insertcommand. Parameters.Add ("@Memo", SqlDbType.NChar, 255, "Memo");
sqldataadapter. InsertCommand = InsertCommand;

SqlDataAdapter. Update (DataSet, "Table_1");

Insert 20,000 record time: 00:00:22.8938000

4, using Sqlbulkcopy.writetoserver (DataTable)(the day white to very say: This method only Sql-server and Oracle 11G later version can be used ...) Now I'm using 10G ... Residual thoughts ...)

SqlBulkCopy SqlBulkCopy = new SqlBulkCopy (connectionString, sqlbulkcopyoptions.useinternaltransaction);
SqlBulkCopy. DestinationTableName = "table_1";//the table name in the database

SqlBulkCopy. WriteToServer (DataSet. Tables[0]);

Insert 20,000 record time: 00:00:00.3276000

So the speed is SqlBulkCopy fastest, sqldataadapter.update () second, SqlCommand. ExecuteNonQuery () is the slowest.