Excel imports SQL Server database large data volume, can control quantity per second

Database code Use [test]go /****** object:  Table [dbo]. [table_1]    Script date:11/07/2017 17:27:29 ******/set ansi_nulls ongo set quoted_identifier ONGO  SET ansi_padding ongo create TABLE [dbo]. [Table_1] ([ID] [varchar] (+) Null,[nodeid] [varchar] (+) Null,[t_b_nodeid] [varchar] (+) null,[nodetype] [varchar] (+) NULL , [Authoritycode] [varchar] (+) null,[nodename] [varchar] (+) null,http://www.mamicode.com/ info-detail-2080904.html [varchar] (+) null,[actionname] [varchar] (+) Null,[nodetag] [varchar] (+) Null,[orderno ] [varchar] (+) null,[createtime] [varchar] (+) null,[updatetime] [varchar] (+) Null,[createuserid] [varchar] (100) Null,[updateuserid] [varchar] (+) Null,[isdel] [varchar] (+) NULL) on [Primary] go set ansi_padding offgo& nbsp c# ///<summary>       //Excel import sql       //&LT;/SUMMARY&GT;PUBL IC void Exceltosqltext ()         {       &NBSP;    //block copy to DB from excel           //by xijun,     &NBSP ;      //step 1 Create an Excel file  C:\Inetpub\wwwroot\test.xls, fill cell (+) with "Data", Cell ( ) with ' name '            //step 2 CREATE table named "Data" with 2 column ("Data", "name") in Your db           //there the code below:            Datetim e T1 = datetime.now;            Response.Write ("<br>start time:" + t1. ToString ());            String excelfile = @ "e:\\test2.xlsx";        &NB Sp  //imex=1: Mixed mode hdr=yes; Do you want the first row to be a column header             String excelconnectionstring = @ "provider=microsoft.ace.oledb.12.0 ;D ata source= "+ excelfile +"; "+" Extended properties=\ "Excel 12.0; Hdr=yes;imex=1;\ "";             using (OleDbConnection excelconnection = new OleDbConnection (excelconnectionstring))         & nbsp   {                 Excelconnection.open ();        & nbsp      //getting source data               //non-empty read in     &N Bsp           OleDbCommand command = new OleDbCommand ("SELECT * from [sheet1$]", excelconnection); &nb Sp              //datasheets                //dataset DS = new DataSet ();               //oledbdataadapter myData = new OleDbDataAdapter ( "SELECT * FROM [sheet1$]", excelconnection);               //mydata.fill (ds, "She et1$ ");//Fill data                 //Initialize SqlBulkCopy object    & nbsp;           using (OleDbDataReader dr = command. ExecuteReader ())                 {                    var TEST = (IDataReader) dr;                   //C opy data to destination                    String sqlconnectionstring = @ "Data source=.; Database=test;user id=sa;pwd= ";                    using (SqlBulkCopy bu Lkcopy = new SqlBulkCopy (sqlconnectionstring))              ,       {&nbsp ;                       Bulkcopy.destinationtablename = "Table_1"; &nb Sp                      //   //Add only one column, then other repositories are implicitly empty. BULKCOPY.COLUMNMAPPINGS.ADD ("Data source column name", "Insert data table column name"); &NBSp                       BULKCOPY.COLUMNMAPPINGS.ADD ("id", "id"); &nbsp ;                       BULKCOPY.COLUMNMAPPINGS.ADD ("NodeId", "NodeId ");                        BULKCOPY.COLUMNMAPPINGS.ADD (" T_b_no Deid "," T_b_nodeid ");                         bulkcopy.co Lumnmappings.add ("NodeType", "NodeType");                      &NBS P BULKCOPY.COLUMNMAPPINGS.ADD ("Authoritycode", "Authoritycode");                & nbsp       BULKCOPY.COLUMNMAPPINGS.ADD ("NodeName", "NodeName");                        BULKCOPY.COLUMNMAPPINGS.ADD ("Pageurl", "Pageurl");        & nbsp              BULKCOPY.COLUMNMAPPINGS.ADD ("ActionName", "ActionName");        & nbsp               BULKCOPY.COLUMNMAPPINGS.ADD ("Nodetag", "OrderNo");      & nbsp                 BULKCOPY.COLUMNMAPPINGS.ADD ("Createtime", "Createtime");  & nbsp                     BULKCOPY.COLUMNMAPPINGS.ADD ("UpdateTime", "Updateti Me ");                        BULKCOPY.COLUMNMAPPINGS.ADD (" Crea Teuserid "," Createuserid ");                        bulkcopy.co Lumnmappings.add ("Updateuserid", "Updateuserid");                    &N Bsp   BULKCOPY.COLUMNMAPPINGS.ADD ("Isdel", "Isdel");                     &nbsp  //BCP. BatchSize = 100;//number of lines per transmission                        //  &nbs P Bcp. Notifyafter = 100;//The number of lines of the progress prompt                         BULKCOPY.BA Tchsize = 100;                        Bulkcopy.notifyafter = 1 00;                        Bulkcopy.writetoserver (IDataReader ) DR);                   }            &NBS P  }               //closing connection          &NB Sp     Excelconnection.close ();           }             DateTime t2 = datetime.now;            Response.Write ("<br>end Time:"+ T2. ToString ());            Response.Write ("<br>use Time:" + (TimeSpan) (T2-T1)). Milliseconds.tostring () + "Milliseconds");            Response.Write ("<br>inser Record count:3307 ");       }

Excel imports SQL Server database large data volume, can control quantity per second