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 //</SUMMARY>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)) , {  ; 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");   ; 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");   //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 "); } |