Bulk INSERT 1 million data

To create a database:

--create databasecreate database bulktestdb;gouse bulktestdb;go--create tablecreate table bulktesttable (Id int Primary Key,username nvarchar (+), Pwd varchar (+)) go--create table Valuedcreate TYPE Bulkudt as Table  (Id int,   UserName nvarchar (+),   Pwd varchar (16))

First, plain SQL is inserted with insert:

            Stopwatch SW = new Stopwatch (); SqlConnection sqlconn = new SqlConnection (configurationmanager.connectionstrings["ConnStr"].            ConnectionString);//Connect database SqlCommand Sqlcomm = new SqlCommand (); Sqlcomm.commandtext = string. Format ("INSERT into bulktesttable (ID,USERNAME,PWD) VALUES (@p0, @p1, @p2)");//Parameterized SQL SqlComm.Parameters.Add ("@p0",            SqlDbType.Int);            SQLCOMM.PARAMETERS.ADD ("@p1", SqlDbType.NVarChar);            SQLCOMM.PARAMETERS.ADD ("@p2", SqlDbType.VarChar);            Sqlcomm.commandtype = CommandType.Text;            Sqlcomm.connection = sqlconn;            Sqlconn.open ();                try {//Loop insert 1 million data, insert 100,000 at a time, insert 10 times. for (int multiply = 0, multiply < multiply++) {for (int count = multiply * 10000 0; Count < (multiply + 1) * 100000; count++) {sqlcomm.parameters["@p0"]. Value = Count; sqlcomm.parameters["@p1"]. Value = string.                        Format ("user-{0}", Count * multiply); sqlcomm.parameters["@p2"]. Value = string.                        Format ("pwd-{0}", Count * multiply); Sw.                        Start ();                        Sqlcomm.executenonquery (); Sw.                    Stop (); }//Every 100,000 data is inserted, the time Console.WriteLine (string) for this insertion is displayed. Format ("Elapsed time is {0} Milliseconds", SW.                Elapsedmilliseconds));            }} catch (Exception ex) {throw ex;            } finally {sqlconn.close (); } console.readline ();

The second kind, with bulk;

The main idea of the bulk method is to insert the data in the table into the database by SqlBulkCopy the data in the table at the client, and then using the

public static void Bulktodb (DataTable dt) {SqlConnection sqlconn = new SqlConnection (Configurationmanager.conne ctionstrings["ConnStr"].    ConnectionString);    SqlBulkCopy bulkcopy = new SqlBulkCopy (sqlconn);    Bulkcopy.destinationtablename = "Bulktesttable"; bulkcopy.batchsize = dt.    Rows.Count; try {sqlconn.open (); if (dt! = null && dt.    Rows.Count! = 0) bulkcopy.writetoserver (DT);    } catch (Exception ex) {throw ex;        } finally {sqlconn.close ();    if (bulkcopy! = null) bulkcopy.close ();    }}public static DataTable GetTableSchema () {datatable dt = new DataTable (); Dt. Columns.addrange (New datacolumn[]{new DataColumn ("Id", typeof (int)), New DataColumn ("UserName", typeof (String    ), New DataColumn ("Pwd", typeof (String))}); return DT;}    static void Main (string[] args) {Stopwatch sw = new Stopwatch (); for (int multiply = 0; multiply < multiply++) {DataTable dt = Bulk.gettAbleschema (); for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++) {DataRow r = dt.            NewRow ();            R[0] = count; R[1] = string.            Format ("user-{0}", Count * multiply); R[2] = string.            Format ("pwd-{0}", Count * multiply); Dt.        Rows.Add (R); } SW.        Start ();        BULK.BULKTODB (DT); Sw.        Stop (); Console.WriteLine (String. Format ("Elapsed time is {0} Milliseconds", SW.    Elapsedmilliseconds)); } console.readline ();}

Thirdly, table-valued parameter method;

The table-valued parameter is a new SQL Server 2008 feature, referred to as TVPs. For friends who are unfamiliar with table-valued parameters, you can refer to the latest book online, which is 5s faster than bulk.

public static void Tablevaluedtodb (DataTable dt) {SqlConnection sqlconn = new SqlConnection (ConfigurationManager. connectionstrings["ConnStr"].    ConnectionString); Const string tsqlstatement = "INSERT into bulktesttable (id,username,pwd)" + "select NC. Id, NC. Username,nc.    PWD "+" from @NewBulkTestTvp as NC ";    SqlCommand cmd = new SqlCommand (tsqlstatement, sqlconn); SqlParameter catparam = cmd.    Parameters.addwithvalue ("@NewBulkTestTvp", DT);    Catparam.sqldbtype = sqldbtype.structured;    The name of the table-valued parameter is Bulkudt, which is in the SQL that created the test environment above. Catparam.typename = "dbo."    Bulkudt ";      try {sqlconn.open (); if (dt! = null && dt. Rows.Count! = 0) {cmd.      ExecuteNonQuery ();    }} catch (Exception ex) {throw ex;    } finally {sqlconn.close ();    }}public static DataTable GetTableSchema () {datatable dt = new DataTable (); Dt. Columns.addrange (New datacolumn[]{new DataColumn ("Id", typeof (int)), New DataColumn ("UsErname ", typeof (String)), New DataColumn (" Pwd ", typeof (String))}); return DT;}    static void Main (string[] args) {Stopwatch sw = new Stopwatch ();        for (int multiply = 0; multiply < multiply++) {DataTable dt = Tablevalued.gettableschema (); for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++) {DataRow R = d            T.newrow ();            R[0] = count; R[1] = string.            Format ("user-{0}", Count * multiply); R[2] = string.            Format ("pwd-{0}", Count * multiply); Dt.        Rows.Add (R); } SW.        Start ();        TABLEVALUED.TABLEVALUEDTODB (DT); Sw.        Stop (); Console.WriteLine (String. Format ("Elapsed time is {0} Milliseconds", SW.    Elapsedmilliseconds)); } console.readline ();}

Bulk INSERT 1 million data