如何在 .NET Core 6.0 中以更有效的方式从数据库 （SQL Server） 中的 Excel 文件导入数据
如果你是处理数据驱动应用程序的 .NET 开发人员，你可能遇到过将数据从 Excel 文件导入 SQL Server 数据库的需要。在这篇博客文章中，我们将探讨使用 .NET Core 6.0 高效完成此任务的两种方法。无论您是处理中等规模的数据集还是处理大量数据，我们都能满足您的需求。
在 .NET Core 6.0 中从 Excel 高效导入 SQL Server
将数据从 Excel 导入到 SQL Server 是各个领域的常见要求，包括金融、电子商务和数据分析。必须有强大的方法来有效地处理这个过程。在这篇文章中，我们将深入探讨两种方法：传统的 ADO.NET 方法和优化的 SQLBulkCopy 方法。在这篇博文中，我们将探讨这两种方法。
使用案例场景
在深入了解技术细节之前，让我们了解何时以及为什么可能需要将数据从 Excel 导入 SQL Server：
- 数据迁移：要将数据从存储在 Excel 文件中的旧系统迁移到新式 SQL Server 数据库。
- 数据集成：需要将 Excel 报表或外部源中的数据集成到 SQL Server 应用程序中，以便进行分析或报告。
- 批处理：应用程序会定期接收必须处理并存储在结构化数据库中的 Excel 数据文件。
现在，让我们探讨这两种方法。
方法 1：ADO.NET
步骤 1：安装 NuGet 包Step 1： Install NuGet packages
在开始之前，请确保将必要的 NuGet 包添加到 .NET Core 项目：Before you start， make sure to add the necessary NuGet packages to your .NET Core project：
Install-Package EPPlus
这将允许您使用 Excel 文件。
步骤 2：创建模型类
定义一个模型类来表示要从 Excel 文件导入的数据。例如：
public class Employee
{
public int EmployeeId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
// Add other properties as needed
}
步骤 3：从 Excel 读取数据
创建一个方法以从 Excel 文件中读取数据，并将其存储在模型对象列表中。下面是一个示例：
using OfficeOpenXml;
public List<Employee> ReadDataFromExcel(string filePath)
{
var employees = new List<Employee>();
using (var package = new ExcelPackage(new FileInfo(filePath)))
{
var worksheet = package.Workbook.Worksheets[0]; // Assuming the data is in the first worksheet
for (int row = 2; row <= worksheet.Dimension.End.Row; row++)
{
var employee = new Employee
{
EmployeeId = Convert.ToInt32(worksheet.Cells\[row, 1\].Value),
FirstName = worksheet.Cells[row, 2].Value.ToString(),
LastName = worksheet.Cells[row, 3].Value.ToString(),
// Map other properties accordingly
};
employees.Add(employee);
}
}
return employees;
}
步骤 4：连接到 SQL Server 并插入数据
使用库设置与 SQL Server 数据库的连接。请确保将连接字符串替换为你自己的数据库详细信息：System.Data.SqlClient
using System.Data;
using System.Data.SqlClient;
string connectionString = "Your_Connection_String_Here";
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
// Your SQL Server code goes here (e.g., INSERT statements to insert data into the database)
}
步骤 5：将数据插入 SQL Server：Step 5： Insert Data into SQL Server：
在 SQL Server 连接块中，可以使用 SQL 命令将数据插入数据库。下面是如何将数据插入 SQL Server 表的示例：
// Assuming you have a table named "Employee" with corresponding columns
foreach (var employee in employees)
{
string insertQuery = "INSERT INTO Employee (EmployeeId, FirstName, LastName) VALUES (@EmployeeId, @FirstName, @LastName)";
using (var cmd = new SqlCommand(insertQuery, connection))
{
cmd.Parameters.AddWithValue("@EmployeeId", employee.EmployeeId);
cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
cmd.Parameters.AddWithValue("@LastName", employee.LastName);
cmd.ExecuteNonQuery();
}
}
在上面的代码块中，“EmployeeID”是数据库和 employee 中的列。EmployeeID 是要传递要插入的 Excel 值，与表的列类似。
步骤6：错误处理和清理：
请务必在代码中添加错误处理和适当的清理（例如，关闭连接）以确保稳健性。
步骤 7：调用 Excel 读取和数据库插入方法：
最后，调用 ReadDataFromExcel 方法以从 Excel 文件中读取数据，并调用用于在应用程序逻辑中将数据插入 SQL Server 数据库的代码。
方法 2：使用 SqlBulkCopy 进行优化
使用 SqlBulkCopy 的好处
- **卓越的性能：**使用 SQLBulkCopy 最显著的优点之一是专为高性能批量数据插入而设计，使其比单个语句快得多，尤其是对于大型数据集。它最大限度地减少了与多个数据库往返相关的开销。INSERT
- **减少数据库往返次数：**使用 SQLBulkCopy，您可以执行到数据库服务器的单次往返以插入多行，而不是使用单独的 Insert 语句每行执行一次往返。这最大限度地减少了网络延迟和开销，从而加快了数据传输速度。
- 最小内存消耗：SQLBulkCopy 可有效地将数据从源（在本例中为 Excel 文件）流式传输到目标 SQL Server 数据库，而无需将整个数据集加载到内存中。这意味着它可以在不消耗过多内存的情况下处理非常大的数据集，从而节省内存。
- 事务支持：SQLBulkCopy 允许您将批量插入操作包装在事务中，从而确保数据一致性。如果大容量插入的任何部分失败，您可以回滚事务以保持数据完整性。
- 可扩展性： 在处理大规模数据导入时，可伸缩性至关重要。SQLBulkCopy 专为可伸缩性而构建，可以轻松处理数百万行。当您的应用程序需要增长和处理不断增加的数据量时，它是一个很好的选择。
- 列映射灵活性： 您可以轻松地将列从源数据 （Excel） 映射到目标数据库表，即使列名或顺序不同也是如此。这种灵活性简化了映射过程，尤其是当源架构和目标架构不完全匹配时。
- 轻松集成：SQLBulkCopy 与 ADO.NET 和 SQL Server 无缝集成，使其成为 .NET 开发人员的直接选择。您无需依赖第三方库或复杂的配置。
- **最少的代码：**与在循环中手动构造和执行多个插入语句相比，SQLBulkCopy 需要的代码更少。这样可以生成更简洁、更易于维护的代码。
- 一致性和可靠性： 由于其优化的设计和与 SQL Server 的集成，SQLBulkCopy 是数据导入的可靠选择。这是一种久经考验的方法，可以优雅地处理各种边缘情况。
- 社区和支持： 作为 ADO.NET 广泛使用的核心组件，SQLBulkCopy 受益于活跃的社区和广泛的文档。您可以找到大量资源和示例来帮助实施和故障排除。
步骤 1：安装 NuGet 包Step 1： Install NuGet packages
在开始之前，请确保将必要的 NuGet 包添加到 .NET Core 项目：Before you start， make sure to add the necessary NuGet packages to your .NET Core project：
Install-Package EPPlus
这将允许您使用 Excel 文件。
步骤 2：从 Excel 读取数据（无变化）
使用库从 Excel 文件中读取数据，就像在方法中一样EPPlus
步骤 3：连接到 SQL Server 并批量插入
使用 SQLBulkCopy 进行批量插入，而不是逐个插入记录。对于大型数据集，这要快得多。以下是修改代码的方法：
using System.Data;
using System.Data.SqlClient;
using OfficeOpenXml;
string connectionString = "Your_Connection_String_Here";
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
// Create a DataTable to hold your data
DataTable dataTable = new DataTable("Employee");
dataTable.Columns.Add("EmployeeId", typeof(int));
dataTable.Columns.Add("FirstName", typeof(string));
dataTable.Columns.Add("LastName", typeof(string));
// Add other columns as needed
// Fill the DataTable with data from Excel
foreach (var employee in employees)
{
dataTable.Rows.Add(employee.EmployeeId, employee.FirstName, employee.LastName /* Add other values */);
}
// Create the SqlBulkCopy object
using (var bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "Employee"; // Replace with your actual table name
bulkCopy.BatchSize = 1000; // Adjust the batch size as needed
// Optionally, map Excel columns to SQL table columns if they don't match exactly
bulkCopy.ColumnMappings.Add("EmployeeId", "EmployeeId");
bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
bulkCopy.ColumnMappings.Add("LastName", "LastName");
// Add mappings for other columns if needed
// Perform the bulk copy
bulkCopy.WriteToServer(dataTable);
}
}
BatchSize：此属性确定在单个批处理中发送到服务器的行数。您可以根据自己的要求和服务器容量调整此值。
步骤 4：错误处理和清理
确保处理异常，关闭 SQL 连接，并管理任何其他必要的清理任务。
说明
这两种方法都有其优点，您的选择应符合您的特定项目要求。对于具有灵活架构的小型数据集，ADO.NET 方法提供了简单性和适应性。然而，在处理大量数据时，优化的方法以其出色的性能、内存效率和可扩展性而大放异彩。SqlBulkCopy
考虑数据导入任务的性质和规模，并选择最适合您需求的方法。借助 .NET Core 6.0，你拥有高效处理数据导入的工具和灵活性，使应用程序能够利用 SQL Server 的强大功能进行数据分析和报告。