How to efficiently import 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 to import Sheet1 from 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 the 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);





///If the target table does not exist create


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 ();


}


//Using bcp to import 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 100;//the progress hint


bcp. DestinationTableName = sheetname;//target table


bcp. WriteToServer (ds. Tables[0]);


}


}


catch (Exception ex)


{


System.Windows.Forms.MessageBox.Show (ex. message);


}





}





//Progress show


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.

This document is compiled from: CSDN