如果在将记录从Excel复制到SQL Server时遇到性能缓慢的问题,可以尝试以下解决方法:
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "YourDestinationTable";
bulkCopy.WriteToServer(yourDataTable);
}
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder();
for (int i = 0; i < yourDataTable.Rows.Count; i++)
{
sb.Append("INSERT INTO YourDestinationTable (Column1, Column2, Column3) VALUES (");
sb.Append("'" + yourDataTable.Rows[i]["Column1"].ToString() + "', ");
sb.Append("'" + yourDataTable.Rows[i]["Column2"].ToString() + "', ");
sb.Append("'" + yourDataTable.Rows[i]["Column3"].ToString() + "')");
if (i % 1000 == 0 || i == yourDataTable.Rows.Count - 1)
{
command.CommandText = sb.ToString();
command.ExecuteNonQuery();
sb.Clear();
}
else
{
sb.Append(";");
}
}
}
}
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
// 遍历Excel的每一行
while (reader.Read())
{
// 读取Excel每一列的值
var column1Value = reader.GetValue(0);
var column2Value = reader.GetValue(1);
var column3Value = reader.GetValue(2);
// 将值插入到SQL Server
}
}
}
以上是几种解决ASP.NET将记录从Excel复制到SQL Server时性能缓慢问题的方法,你可以根据自己的具体情况选择适合的解决方案。