What are all the steps to take to get the SQL Server CE to work on the web host machine

sql-server-ce-4

Edit: My question comes down to, what are all the steps I need to take to build a web app that uses SQL Server CE in medium trust?

I had copied my desktop version into my bin directory before I posted this, but I'm sure there are more steps to take, as that did not work.

Error: Possible file version mismatch detected between ADO.NET Provider and native binaries of SQL Server Compact which could result in an incorrect functionality. This could be due to the presence of multiple instances of SQL Server Compact of different versions or due to wrong binaries with same name as SQL Server Compact binaries. Please install SQL Server Compact binaries of matching version.

Web App: MVC 3 built with VS 2010

Everything works fine on my dev machine.

After reading this post, I got rid of my references and copied SqlServerCE.dll and SqlServerCe.Entity.dll to my bin directory.

That changed the error from

Could not load file or assembly 'System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

to the current one.

I copied SqlServerCE.dll from my desktop folder, so I could run under medium trust.

From my Web.config file:

  `       <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="3.0.0.0" />
        <assemblyIdentity name="System.Data.SqlServerCe" 
                          publicKeyToken="89845dcd8080cc91" culture="neutral"/>
        <bindingRedirect oldVersion="4.0.0.0-4.0.0.1" newVersion="4.0.0.1"/>

      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.4.0" />
      <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
    </DbProviderFactories>`

Edit: Thanks everyone.

I do have SqlServerCompact.4.0.8852.1 in my packages directory.

I removed SqlServerCE.dll and SqlServerCe.Entity.dll from my bin directory, as they are in my packages. I copied the Native Binaries into my bin (and they are included in the project), but I still get this message:

Possible file version mismatch detected between ADO.NET Provider and
native binaries of SQL Server Compact which could result in an
incorrect functionality. This could be due to the presence of multiple
instances of SQL Server Compact of different versions or due to wrong
binaries with same name as SQL Server Compact binaries. Please install
SQL Server Compact binaries of matching version.

I was unable to use the nuget package you suggested as I have a newer version of Entity Framework and it rolled back the install.

I'm ready to start over with a new project if anyone can recommend a setup of SQL Server CE that will work in Medium trust on a shared hosting server.

This is what I have been doing (and does not work) when I created my project.

a. Install-Package SqlServerCompact
b. Install-Package System.Web.Providers
c. Install-Package EntityFramework
d. Install-Package EntityFramework.SqlServerCompact

Best Answer

Check at your packages directory if your SqlCompact is this version: SqlServerCompact.4.0.8852.1

If yes, you need to copy the files inside packages\SqlServerCompact.4.0.8852.1\NativeBinaries because they are different.

But this version don´t run at medium trust, because they changed the SqlCompact version to 4.0.0.1 and only the 4.0.0.0 is authorized by default at .NET 4 config file. If you need a version that run at medium trust, try this one https://nuget.org/packages/EntityFramework.SqlServerCompact/4.1.8482.2

Related Topic