咨询：在.NET Core Entity Framework中无法导入OleDbConnection的问题（含Excel转SQL场景）

Hey there, let's work through this OleDbConnection issue you're hitting in .NET Core EF when migrating Excel data to SQL Server. I’ve dealt with similar frustrations before, so here’s a breakdown of solutions that should get you back on track:

1. 先搞定OleDb的基础依赖 #

OleDb isn’t included by default in .NET Core, so the first step is to add the right packages and drivers:

• Install the System.Data.OleDb NuGet package:
  • In Package Manager Console: Install-Package System.Data.OleDb
  • Or via .NET CLI: dotnet add package System.Data.OleDb
• Install the matching Microsoft Access Database Engine redistributable:
  • If you’re on 64-bit Windows/using 64-bit project settings, grab the 64-bit version
  • If your project targets x86 (common for older Excel setups), install the 32-bit version

  Note: You can’t have both 32 and 64-bit versions installed side-by-side—pick the one that matches your project’s platform target.

2. Fix the "cannot import OleDbConnection" compilation error #

If you still can’t reference OleDbConnection, check these quick fixes:

• Ensure your project targets .NET Core 3.1 or newer (OleDb support was fully stabilized starting here)
• Add the using directive at the top of your code file: using System.Data.OleDb;
• Double-check your project file to confirm the NuGet package is properly referenced:

  <PackageReference Include="System.Data.OleDb" Version="7.0.0" />
  

3. Correct Excel connection strings #

Even if the import works, bad connection strings will break your Excel data read. Use the right one for your file type:

• For .xlsx (Excel 2007+):

  string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=YourExcelFile.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES;'";
  

• For .xls (Excel 97-2003):

  string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=YourExcelFile.xls;Extended Properties='Excel 8.0;HDR=YES;'";
  

HDR=YES tells OleDb your first row is column headers; use HDR=NO if the first row is raw data.

4. Example: Migrate Excel data to SQL Server with EF Core #

Here’s a working snippet that combines OleDb for Excel reading and EF Core for SQL Server insertion:

using System.Data.OleDb;
using Microsoft.EntityFrameworkCore;

// Your EF Core DbContext
public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("YourSQLServerConnectionString");
    }
}

// Product entity matching your SQL Server table
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

// Migration logic
public async Task MigrateExcelToSql()
{
    string excelConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Products.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES;'";
    
    using (OleDbConnection excelConn = new OleDbConnection(excelConnString))
    {
        excelConn.Open();
        OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", excelConn);
        OleDbDataReader reader = cmd.ExecuteReader();

        using (var dbContext = new AppDbContext())
        {
            while (reader.Read())
            {
                var product = new Product
                {
                    Name = reader["ProductName"].ToString(),
                    Price = Convert.ToDecimal(reader["Price"])
                };
                dbContext.Products.Add(product);
            }
            await dbContext.SaveChangesAsync();
        }
    }
}

5. Alternative: Skip OleDb entirely with EPPlus #

If you run into driver hell or platform compatibility issues, use EPPlus to read Excel files directly (no OleDb required):

• Install the EPPlus NuGet package: Install-Package EPPlus or dotnet add package EPPlus
• Example snippet:

using OfficeOpenXml;
using System.IO;

public async Task MigrateWithEPPlus()
{
    // Set license context for non-commercial use
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
    
    using (var package = new ExcelPackage(new FileInfo("Products.xlsx")))
    {
        var worksheet = package.Workbook.Worksheets["Sheet1"];
        int totalRows = worksheet.Dimension.Rows;

        using (var dbContext = new AppDbContext())
        {
            // Start at row 2 to skip headers
            for (int row = 2; row <= totalRows; row++)
            {
                var product = new Product
                {
                    Name = worksheet.Cells[row, 1].Text,
                    Price = decimal.Parse(worksheet.Cells[row, 2].Text)
                };
                dbContext.Products.Add(product);
            }
            await dbContext.SaveChangesAsync();
        }
    }
}

内容的提问来源于stack exchange，提问作者Amit Verma