How to connect to SQL Server CE database from ASP.NET Core Web API application

Question

I have connected to my SQL Server Compact Edition database from an ASP.NET console application. I am using the same thing and libraries to connect to SQL Server CE database file in my ASP.NET Core Web API application, but I get an exception.

My code:

public  class ClienDetailRepository: IClientDetailRepository
{
    private readonly string _connectionString;

    private static string DbFilePath = @"C:\Git\Forecaster\DB\THSTaxPro.sdf";
    private static string DbPassword = "*****";
    private static string DbMaxSize = "4090";

    public static string ConnectionString = @"Data Source=" + DbFilePath + ";Password=" + DbPassword + ";Max Database Size=" + DbMaxSize + "";

    public ClienDetailRepository(string connectionString)
    {
        _connectionString = connectionString;
        //_connectionString = ConnectionString;
    }
}

I am getting error on this line

  SqlCeConnection sqlconTHS = new SqlCeConnection(ConnectionString);

This is the error:

The type initializer for 'System.Data.SqlServerCe.SqlCeConnection' threw an exception

Answer 1

Do you use Windows? I tested on Windows, and your problem did not occur, you can refer to my steps below.

Copy the System.Data.SqlServerCe.dll file and the entire amd64 folder from the Microsoft SQL Server Compact Edition\v4.0\Private directory to the folder where the .csproj file created:

Install the Windows compatibility pack, to make any required Windows specific .NET APIs available.

Add a file reference to the System.Data.SqlServerCe.dll file in the root of your project, and your .csproj file should end up like this(I'm using .net6):

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

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

  <ItemGroup>
    <PackageReference Include="Microsoft.Windows.Compatibility" Version="6.0.3" />
  </ItemGroup>

    <ItemGroup>
        <None Remove="amd64\Microsoft.VC90.CRT\Microsoft.VC90.CRT.manifest" />
        <None Remove="amd64\Microsoft.VC90.CRT\msvcr90.dll" />
        <None Remove="amd64\Microsoft.VC90.CRT\README_ENU.txt" />
        <None Remove="amd64\sqlceca40.dll" />
        <None Remove="amd64\sqlcecompact40.dll" />
        <None Remove="amd64\sqlceer40EN.dll" />
        <None Remove="amd64\sqlceme40.dll" />
        <None Remove="amd64\sqlceqp40.dll" />
        <None Remove="amd64\sqlcese40.dll" />
        <None Remove="System.Data.SqlServerCe.dll" />
    </ItemGroup>

    <ItemGroup>
        <Content Include="amd64\Microsoft.VC90.CRT\Microsoft.VC90.CRT.manifest">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\Microsoft.VC90.CRT\msvcr90.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\Microsoft.VC90.CRT\README_ENU.txt">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\sqlceca40.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\sqlcecompact40.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\sqlceer40EN.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\sqlceme40.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\sqlceqp40.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="amd64\sqlcese40.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
        <Content Include="System.Data.SqlServerCe.dll">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </Content>
    </ItemGroup>

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

My Test Code:

string connectionString = "Data Source=THSTaxPro.sdf";

//using SqlCeEngine engine = new SqlCeEngine(connectionString);
//engine.CreateDatabase();

using SqlCeConnection connection = new SqlCeConnection(connectionString);
connection.Open();

using (SqlCeCommand command = connection.CreateCommand())
{
    //command.CommandText = "CREATE TABLE Blogs ( Id INT, Title NVARCHAR(256) )";

    //command.ExecuteNonQuery();

    command.CommandText = "SELECT * FROM Blogs";

    SqlCeDataReader reader = command.ExecuteReader();

    int Count = reader.FieldCount;
}

Of course, please make sure your connectionString is correct.

Test Result:

For more detail, you can refer to this link.