插入sql数据库时处理大量数据

Question

在我的代码中，用户可以上传一个excel文档，希望其中包含电话联系人列表。作为开发人员，我应阅读excel文件，将其转换为dataTable并将其插入数据库。问题是某些客户拥有大量的联系人，例如说5000个和更多的联系人，而当我尝试将这种数据量插入数据库时​​，它崩溃了，并给了我一个超时异常。避免这种异常的最佳方法是什么？它们的任何代码都可以减少insert语句的时间，从而使用户不必等待太久？

代码

public SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
public void Insert(string InsertQuery)
{
    SqlDataAdapter adp = new SqlDataAdapter();
    adp.InsertCommand = new SqlCommand(InsertQuery, connection);
    if (connection.State == System.Data.ConnectionState.Closed)
    {
        connection.Open();
    }
    adp.InsertCommand.ExecuteNonQuery();
    connection.Close();
}

protected void submit_Click(object sender, EventArgs e) 
{
    string UploadFolder = "Savedfiles/";
    if (Upload.HasFile) {
        string fileName = Upload.PostedFile.FileName;
        string path=Server.MapPath(UploadFolder+fileName);
        Upload.SaveAs(path);
        Msg.Text = "successfully uploaded";
        DataTable ValuesDt = new DataTable();
        ValuesDt = ConvertExcelFileToDataTable(path);
        Session["valuesdt"] = ValuesDt;
        Excel_grd.DataSource = ValuesDt;
        Excel_grd.DataBind();


    }
}

protected void SendToServer_Click(object sender, EventArgs e)
{
    DataTable Values = Session["valuesdt"] as DataTable ;
    if(Values.Rows.Count>0)
    {
        DataTable dv = Values.DefaultView.ToTable(true, "Mobile1", "Mobile2", "Tel", "Category");
        double Mobile1,Mobile2,Tel;string Category="";
        for (int i = 0; i < Values.Rows.Count; i++)
       {
            Mobile1 =Values.Rows[i]["Mobile1"].ToString()==""?0: double.Parse(Values.Rows[i]["Mobile1"].ToString());
            Mobile2 = Values.Rows[i]["Mobile2"].ToString() == "" ? 0 : double.Parse(Values.Rows[i]["Mobile2"].ToString());
            Tel = Values.Rows[i]["Tel"].ToString() == "" ? 0 : double.Parse(Values.Rows[i]["Tel"].ToString());

           Category = Values.Rows[i]["Category"].ToString();
           Insert("INSERT INTO client(Mobile1,Mobile2,Tel,Category) VALUES(" + Mobile1 + "," + Mobile2 + "," + Tel + ",'" + Category + "')");
           Msg.Text = "Submitied successfully to the server ";
       }



    }

}

Answer 1

您可以尝试SqlBulkCopy将数据表插入数据库表

像这样

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.KeepIdentity))
{
    bulkCopy.DestinationTableName = DestTableName;
    string[] DtColumnName = YourDataTableColumns;
    foreach (string dbcol in DbColumnName)//To map Column of Datatable to that of DataBase tabele
    {
        foreach (string dtcol in DtColumnName)
        {
            if (dbcol.ToLower() == dtcol.ToLower())
            {
                SqlBulkCopyColumnMapping mapID = new SqlBulkCopyColumnMapping(dtcol, dbcol);
                bulkCopy.ColumnMappings.Add(mapID);
                break;
            }
        }
    }
    bulkCopy.WriteToServer(YourDataTableName.CreateDataReader());
    bulkCopy.Close();
}

有关更多信息，请参见http://msdn.microsoft.com/zh-
cn/library/system.data.sqlclient.sqlbulkcopy.aspx