Practical tips for efficiently importing Excel into SQL Server

Most people know that OLE DB is used to read data to a dataset, but how to deal with a dataset after reading is strange. Many people use loops to splice SQL, which is not only error-prone but inefficient, System.Data.SqlClient.SqlBulkCopy is still unfamiliar to the novice, this is the legendary high efficiency of BCP, It only takes 4.5 seconds to import more than 60,000 data from Excel into SQL.

Using System; 
Using System.Data; 
Using System.Windows.Forms; 
Using System.Data.OleDb; 
namespace WindowsApplication2 {public partial class Form1:form {public Form1 () {InitializeComponent (); private void Button1_Click (object sender, EventArgs e) {//test, import Sheet1 in Excel to SQL Server string connstring = "Serv 
Er=localhost;uid=sa;pwd=sqlgis;database=master "; 
System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog (); if (FD. ShowDialog () = = DialogResult.OK) {transferdata (fd. 
FileName, "Sheet1", connstring); } public void TransferData (string excelfile, String sheetname, String connectionString) {DataSet ds = new DataSet ( 
); try {//Get all data string strconn = "Provider=Microsoft.Jet.OLEDB.4.0" + "data source=" + Excelfile + ";" + "Extended Prop 
Erties=excel 8.0; "; 
OleDbConnection conn = new OleDbConnection (strconn); Conn. 
Open (); 
String strexcel = ""; 
OleDbDataAdapter mycommand = null; Strexcel = string. 
Format ("select * from [{0}$]", sheetname); Mycommand = new OleDbDataAdapter (Strexcel, strconn); 

Mycommand.fill (ds, SheetName); Creates a string strSQL = string if the target table does not exist. 
Format ("If object_id (' {0} ') is null CREATE TABLE {0} (", sheetname); foreach (System.Data.DataColumn C in DS. Tables[0]. Columns) {strSQL = = string. 
Format ("[{0}] varchar (255),", c.columnname); 

} strSQL = Strsql.trim (', ') + ")"; 
using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection (connectionString)) { Sqlconn. 
Open (); System.Data.SqlClient.SqlCommand command = sqlconn. 
CreateCommand (); 
Command.commandtext = strSQL; Command. 
ExecuteNonQuery (); Sqlconn. 
Close ();  
////BCP imports data using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy (connectionString)) { Bcp. 
Sqlrowscopied + = new System.Data.SqlClient.SqlRowsCopiedEventHandler (bcp_sqlrowscopied); Bcp. BatchSize = 100;//The number of rows per transmission bcp. Notifyafter = number of rows of 100;//progress prompts bcp. DestinationTableName = sheetname;//destination table bcp. WriteToServer (ds. 
Tables[0]); }} CATCH (Exception ex) {System.Windows.Forms.MessageBox.Show (ex. 
message); }//Progress shows void Bcp_sqlrowscopied (object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e) {this. 
Text = E.rowscopied.tostring (); This. 
Update ();  } 


} 
}

The above transferdata can be used directly, if you want to think about it, you can use OLE DB to get the table structure of Excel, and add columnmappings to set the control field, The effect is exactly the same as the SQL Server DTS.