How to import SQL Server efficiently from Excel in C #

SQL Server, which quickly inserts the Excel data read by OLE DB, is used by many people to stitch through the loops to make it both error-prone and inefficient, and the best way to do this is to use BCP, which is System.Data.SqlClient.SqlBulkCopy class to implement. Not only is it fast and the code is simple, the following test code imports a sheet of more than 60,000 data, including reads (slow to read all) in my development environment only about 10 seconds, while the real import process takes only 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)
{
Testing, importing 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 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);

Created 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.