How to connect ASP.NET Core Web API with SQL Server CE 3.5 database

Question

I added System.Data.SqlServerCe.dll v3.5 in my ASP.NET Core Web API project, but I'm getting error while connecting to SQL Server CE database.

This is my code:

 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;
            }
            



        }

This is the error I am getting:

Answer 1

SQL CE is out of support. But there is a way you can use SQL CE 3.5 in asp.net core project.

Step1. Go to CE3.5 installation folder. Copy all .dll files except system.data.sqlserverce.entity to a new folder named amd64 ,paste this folder to project root.

Step2. from private folder copy system.data.sqlserverce.dll to project root

You project root is like

Step3. open .csproj add the following reference

    <ItemGroup>

        <Content Include="amd64\sqlceca35.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\sqlcecompact35.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\sqlceer35EN.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\sqlceme35.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\sqlceoledb35.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>  
        <Content Include="amd64\sqlceqp35.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\sqlcese35.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="System.Data.SqlServerCe.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
    </ItemGroup>

    <ItemGroup>
        <Reference Include="System.Data.SqlServerCe">
            <HintPath>System.Data.SqlServerCe.dll</HintPath>
        </Reference>
    </ItemGroup>

Step4.install nuget packageMicrosoft.Windows.Compatibility latest version.

Now restart your project, SqlCeConnection will work.

All .csproj

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net6.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Windows.Compatibility" Version="7.0.1" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="6.2.3" />
  </ItemGroup>

    <ItemGroup>

        <Content Include="amd64\sqlceca35.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\sqlcecompact35.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\sqlceer35EN.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\sqlceme35.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\sqlceoledb35.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>  
        <Content Include="amd64\sqlceqp35.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\sqlcese35.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="System.Data.SqlServerCe.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
    </ItemGroup>

    <ItemGroup>
        <Reference Include="System.Data.SqlServerCe">
            <HintPath>System.Data.SqlServerCe.dll</HintPath>
        </Reference>
    </ItemGroup>
</Project>