ASP.NET Core Web API连接SQL Server CE 3.5数据库报错求助

问题背景 #

在ASP.NET Core Web API项目中添加了System.Data.SqlServerCe.dll v3.5，但连接SQL Server CE数据库时触发错误，相关实现代码如下：

private static string DbFilePath = @"C:\Git\Forecaster\DB\THSTaxPro.sdf";
private static string DbPassword = "123567";
private static string DbMaxSize = "4090";
public static string ConnectionString = @"Data Source=" + DbFilePath + ";Password=" + DbPassword + ";Max Database Size=" + DbMaxSize + "";

[Route("thsAdditionalincome")]
public async Task<IEnumerable<AdditionalIncome>> thsAdditionalincome(string accountid)
{
    try
    {
        using (var conn = new SqlCeConnection(_connectionString))
        {
            var query = "select irs_account_id,income_alimony,income_alimony_taxpayer,income_alimony_spouse,income_child_support ,  income_child_support_taxpayer,income_child_support_spouse ,income_net_business, income_net_business_taxpayer, income_net_business_spouse , income_net_rental , income_net_rental_taxpayer , income_net_rental_spouse ,   income_pension ,  income_pension_taxpayer ,income_pension_spouse, income_interest_dividends , income_interest_dividends_taxpayer,income_interest_dividends_spouse, income_social_security,income_social_security_taxpayer,income_social_security_spouse,income_other_1,income_other_1_taxpayer,income_other_2,income_other_2_taxpayer,income_other_2_spouse from t_financial_analysis_individual  where IsDeleted=0 and irs_account_id=@accountid";
            var parameters = new DynamicParameters();
            parameters.Add("@accountid", accountid, DbType.String);

            conn.Open();

            var results = await conn.QueryAsync<AdditionalIncome>(query, param: new { accountid });

            return results.ToList();
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

核心问题分析 #

1. 版本兼容性问题：SQL Server CE 3.5是为.NET Framework开发的组件，完全不支持ASP.NET Core/.NET Core框架，这是导致连接失败最常见的根本原因。
2. 代码变量不一致：代码中定义了静态连接字符串字段ConnectionString，但实例化SqlCeConnection时使用的是未定义的_connectionString，会导致连接字符串为空或无效。
3. 依赖文件缺失：SQL Server CE 3.5运行依赖多个原生DLL文件（如sqlceme35.dll、sqlcese35.dll等），如果这些文件未随项目部署，会触发加载失败错误。

解决方案 #

1. 升级到SQL Server CE 4.0（推荐） #

SQL Server CE 4.0提供了对.NET Core的兼容支持，可以通过NuGet安装官方包Microsoft.SqlServer.Compact，替换现有v3.5的引用，从根源解决版本兼容问题。

2. 修正代码变量错误 #

如果坚持使用v3.5版本，先修正连接字符串变量的不一致问题：

// 修正为正确的静态字段名
using (var conn = new SqlCeConnection(ConnectionString))

3. 补充原生依赖文件 #

将SQL Server CE 3.5对应架构（x86/x64）的原生DLL文件复制到项目输出目录，并设置文件属性为“复制到输出目录：始终复制”，确保运行时能加载到依赖。

4. 切换到.NET Framework项目 #

若业务场景允许，将Web API项目改为基于.NET Framework的类型，SQL Server CE 3.5可完美兼容该环境。

优化后代码示例 #

private static string DbFilePath = @"C:\Git\Forecaster\DB\THSTaxPro.sdf";
private static string DbPassword = "123567";
private static string DbMaxSize = "4090";
// 使用字符串插值简化连接字符串拼接
public static string ConnectionString = $"Data Source={DbFilePath};Password={DbPassword};Max Database Size={DbMaxSize}";

[Route("thsAdditionalincome")]
public async Task<IEnumerable<AdditionalIncome>> thsAdditionalincome(string accountid)
{
    try
    {
        using (var conn = new SqlCeConnection(ConnectionString))
        {
            var query = @"select irs_account_id,income_alimony,income_alimony_taxpayer,income_alimony_spouse,income_child_support,
                          income_child_support_taxpayer,income_child_support_spouse,income_net_business,income_net_business_taxpayer,
                          income_net_business_spouse,income_net_rental,income_net_rental_taxpayer,income_net_rental_spouse,
                          income_pension,income_pension_taxpayer,income_pension_spouse,income_interest_dividends,
                          income_interest_dividends_taxpayer,income_interest_dividends_spouse,income_social_security,
                          income_social_security_taxpayer,income_social_security_spouse,income_other_1,income_other_1_taxpayer,
                          income_other_2,income_other_2_taxpayer,income_other_2_spouse
                          from t_financial_analysis_individual
                          where IsDeleted=0 and irs_account_id=@accountid";
            
            await conn.OpenAsync();
            // Dapper支持直接传入匿名参数，无需手动创建DynamicParameters
            var results = await conn.QueryAsync<AdditionalIncome>(query, new { accountid });

            return results.ToList();
        }
    }
    catch (Exception ex)
    {
        // 建议添加日志记录，便于排查问题
        // _logger.LogError(ex, "查询额外收入数据失败");
        throw;
    }
}

内容的提问来源于stack exchange，提问作者Ravindra Singh