怎样快速提升excel导入数据库的速度

Question

protected void Button1_Click(object sender, EventArgs e)
{
string fianlfilename = upload();
show(fianlfilename);
string strSQL = "";
DataTable dt=show(fianlfilename);
for (int i = 0; i <dt.Rows.Count; i++)
{
strSQL +="insert into test1 values('" + dt.Rows[i][0].ToString () + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "')";
}
INSERT(strSQL);
}
private string upload()
{
HttpPostedFile file = FileUpload1.PostedFile;
string filename = file.FileName;//文件名+文件路径
filename = System.IO.Path.GetFileName(filename);//提取filename中的文件名（不带路径）
string serverfilepath=Server.MapPath(".")+"//wenjian//";//获得服务器保存的文件夹路径
string fianlfilename=serverfilepath+filename;
file.SaveAs(fianlfilename);
return fianlfilename;
}
private DataTable show(string strpath)
{
string strConn = "provider=Microsoft.JET.OLEDB.4.0;Data Source='"+strpath+"';Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
//string strConn = "Provider=Microsolf.JET.OLEDB.4.0;Data Source='D:\123.xlsx';Extended Properties=Excel 8.0;";
//string strConn = "Provider=Microsolf.ACE.OLEDB.12.0;Data Source='D:\Book1.xls';Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();

    string strSQL = "select * from[Sheet1$]";
    OleDbDataAdapter da = new OleDbDataAdapter(strSQL, conn);
    DataTable dt = new DataTable();
    da.Fill(dt);
    conn.Close();
    GridView1.DataSource = dt;
    GridView1.DataBind();
    return dt;

}
private void INSERT(string strSQL)
{
    string strConn = "data source=DESKTOP-JAUDH78\\QWERT;initial catalog=KAOQINXITONG;uid=sa;password=123456;";
    SqlConnection conn = new SqlConnection(strConn);
    conn.Open();

    SqlCommand cmd = new SqlCommand(strSQL,conn);
    cmd.ExecuteNonQuery();
    conn.Close();

}

}

Answer 1

用sql server的bulkcopy，这样拷贝的时候忽略任何事务、约束、触发器，所以快。