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