Efficient import of 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 through the loop to splicing SQL, so not only error-prone and inefficient, System.Data.SqlClient.SqlBulkCopy for beginners is still relatively unfamiliar, this is the legendary high efficiency of BCP, 60,000 more data imported from Excel to SQL takes 4.5 seconds.

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 into SQL Server
            string connstring = "Server=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 properties=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);

               //create if the target table does not exist
                string strSQL = string. 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 ();
}
Import data with bcp
using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy (connectionString))
{
Bcp. Sqlrowscopied + = new System.Data.SqlClient.SqlRowsCopiedEventHandler (bcp_sqlrowscopied);
Bcp. BatchSize = 100;//number of rows per transmission
Bcp. Notifyafter = number of lines of 100;//progress prompt
Bcp. DestinationTableName = sheetname;//target table
Bcp. WriteToServer (ds. Tables[0]);
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show (ex. Message);
}

}

Progress display
void Bcp_sqlrowscopied (object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)
{
This. Text = E.rowscopied.tostring ();
This. Update ();
}
}
}

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

Transferred from: http://yrq205.blog.163.com/blog/static/47672140201162505927442/

(go) Efficiently import Excel into SQL Server