C # One way to read Excel OLE DB read (1 million)--fast BULK INSERT in SQL

Main application Table Type

1 Create table Bulktesttable (2Id nvarchar ( +), 3UserName nvarchar ( +), 4PWD nvarchar ( +)5 )6 Go7 CREATE TYPE Bulkudt as TABLE8(Id nvarchar ( +), 9UserName nvarchar ( +), TenPWD nvarchar ( +) )

View Code

C # side reads Excel

    <summary>///Read data in Excel///</summary>//<param name= "Strexcelpath" >< /param>//<param name= "TableName" ></param>///<returns></returns> Publi                 C DataTable Getexceltablebyoledb (String strexcelpath, String tableName) {try {                 DataTable dtexcel = new DataTable ();                 Data table DataSet ds = new DataSet ();                 Gets the file name extension string strextension = System.IO.Path.GetExtension (Strexcelpath);                 String strFileName = System.IO.Path.GetFileName (Strexcelpath);                 Excel's connection OleDbConnection objconn = null; Switch (strextension) {case ". xls": objconn = new Oledbconnec tion ("Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Strexcelpath + ";" + "Extended properties=\" Excel 8.0; Hdr=no;imex=1;\ "");                     Break Case ". xlsx": objconn = new OleDbConnection ("Provider=microsoft.ace.oledb.12.0;data source=" + str Excelpath + ";" + "Extended properties=\" Excel 12.0;                         Hdr=no;imex=1;\ "");                     Break                         Default:objconn = null;                 Break                 } if (objconn = = null) {return null;                 } objconn.open (); Gets the information for all sheet tables in Excel//system.data.datatable schematable = objconn.getoledbschematable (System.Data.OleDb.                 OleDbSchemaGuid.Tables, NULL); Gets the first sheet table name//String tableName1 = Schematable.rows[0][2] in Excel. ToString ().                 Trim ();                 String strSQL = "SELECT * FROM [" + TableName + "$]"; Gets the information in Excel specified sheet table OleDbCommand objcmd = new OleDbCommand (strSQL, objconn);                 OleDbDataAdapter myData = new OleDbDataAdapter (strSQL, objconn);                 Mydata.fill (ds, TableName);//Fill Data objconn.close (); Dtexcel is the information stored in the specified table in the Excel file Dtexcel = ds.                 Tables[tablename];             return dtexcel; } catch (Exception ex) {MessageBox.Show (ex.                 Message);             return null; }                    }

C # side inserts into the SQL table

 <summary>//import MSSQL///</summary>//<param name= "?" ></param>//<returns></returns> public int exceltomssql (string tablename,datatable DT            ) {int count = 0; String connstr = "Data source=;initial catalog=; Persist Security info=true; User id=;            Password= ";             SqlConnection sqlconn = new SqlConnection (CONNSTR); 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) {count = cmd.                ExecuteNonQuery ();            }} catch (Exception ex) {throw ex;            } finally {sqlconn.close ();        } return count; }

　　

C # One way to read Excel OLE DB read (1 million)--fast BULK INSERT in SQL