Comparison of three methods for batch writing DataTable data to the database, and three methods for datatable data

Comparison of three methods for batch writing DataTable data to the database, and three methods for datatable data

Comparison of three methods for batch writing DataTable data to the database
Tags: it classification: C #
1) insert loop insert;
2) sqldataadapter. update (dataset, tablename );
3) sqlbulkcopy. WriteToServer (datatable );

1. Generate a datatable table for testing. The table structure is as follows:
UniqueID (primary key, auto-increment) | CompanyName | CompanyCode | Address | Owner | Memo
There are 6 fields in total.
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 20000 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 );
}
Copy code
2. Use sqlcommand.exe cutenonquery () to insert data.
Foreach (DataRow datarow in datatable. Rows)
{
String SQL = "INSERT INTO [Table_1] ([CompanyName], [CompanyCode], [Address], [Owner],
) "+
"VALUES ('" + datarow ["CompanyName"]. ToString () + "'" +
", '" + Datarow ["CompanyCode"]. ToString () + "'" +
", '" + Datarow ["Address"]. ToString () + "'" +
", '" + Datarow ["Owner"]. ToString () + "'" +
", '" + Datarow ["Memo"]. ToString () + "')";
Using (SqlConnection sqlconn = new SqlConnection (connectionString ))
{
Sqlconn. Open (); SqlCommand sqlcommand = new SqlCommand (SQL, sqlconn );
Sqlcommand. ExecuteNonQuery ();
Sqlconn. Close ();
}
}
Copy code
Insert 20000 records at: 00: 00: 29.7336000

3. Use sqldataadapter. update (dataset, tablename );
SqlCommand insertcommand = new SqlCommand ("insert into [Table_1] ([CompanyName], [CompanyCode], [Address], [Owner],
) "+
"VALUES (@ CompanyName, @ CompanyCode, @ Address, @ Owner, @ Memo)", new SqlConnection (connectionString ));
Insertcommand. Parameters. Add ("@ CompanyName", SqlDbType. NChar, 50, "CompanyName ");
Insertcommand. Parameters. Add ("@ CompanyCode", SqlDbType. NChar, 25, "CompanyCode ");
Insertcommand. Parameters. Add ("@ Address", SqlDbType. NChar, 255, "Address ");
Insertcommand. Parameters. Add ("@ Owner", SqlDbType. NChar, 25, "Owner ");
Insertcommand. Parameters. Add ("@ Memo", SqlDbType. NChar, 255, "Memo ");
Sqldataadapter. InsertCommand = insertcommand;
Sqldataadapter. Update (dataset, "Table_1 ");
Copy code
Insert 20000 records at: 00: 00: 22.8938000

4. Use sqlbulkcopy. writetoserver (datatable)
SqlBulkCopy sqlbulkcopy = new SqlBulkCopy (connectionString, SqlBulkCopyOptions. UseInternalTransaction );
Sqlbulkcopy. DestinationTableName = "Table_1"; // name of the table in the database
Sqlbulkcopy. WriteToServer (dataset. Tables [0]);
Copy code
Insert 20000 records at: 00: 00: 00.3276000

Therefore, the speed is sqlbulkcopy, followed by sqldataadapter. update (), and sqlcommand. ExecuteNonQuery.