Can't get sql server compact 3.5 / 4 to work with ASP .NET MVC 2

Question

I'm using Visual Studio 2008 Pro.

I'm probably missing something very obvious here, but I've been trying to get the CTP for Sql Server compact 4 to work in my asp.net mvc application. I can find next to no instruction on how to set this up or a working example application. My goal is a private install so I can just include it in my web app without having to do sql server setup on my domain hosting. This is really just me shooting the breeze and trying to figure this out. I don't plan to host a market or anything with this.

So, I've copied all the dll's that install in the base 4.0 direction (c:\Program Files\Sql Server compact\v4.0) to a lib folder in my application. I've set the copy to output direction option to 'Copy if Newer'. I then reference the System.Data.SqlServerCE dll and set 'Copy Local' to True.

I created an sdf file via Sql Studio Express. An important note is that I did not see an option for creating a CE 4.0 version of this file, so it was created using CE 3.5. I create a few tables, add a few rows to those tables, copy the *.sdf file to my App_Data directory. It's worth mentioning that, from inside VS 2008, this file never appears in my project, but it does exist in the physical location of the App_Data directory. I'm not sure why this is.

Next, I just try making a basic connection to my sdf file via:

SqlCeConnection conn = new SqlCeConnection("DataSource=rpg.sdf");

This yields the error below:

Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8402. Install the correct version of SQL Server Compact. Refer to KB article 974247 for more details.

I figure from here, I'd just try getting Sql CE 3.5 to work. I upgrade my local installation of Sql CE 3.5 to sp2. I copy the dlls at the base location (c:\Program Files\Sql Server compact\v3.5), including removing and readding the version of the System.Data.SqlServerCE dll from my project references.

The curious thing here is when I right click and look at the properties of the referenced SqlServerCE dll, it always says it's version 4.0.0.1.

Guys, I really could use some direction here. I have searched stack overflow, the help docs, books online, and googled. I really haven't found anything that takes this from the very top for either CE 3.5 or 4.0 and tells me exactly what dll's to add, where to put them, how to reference them, how to add the .sdf file to my project, connect to it, and query from it. I did come across a few mentions of an IBuySpy portal sample app that was supposed to use Sql CE 3.5, but can't actually navigate the msdn download maze to get to it. Ideally, I want to setup a private deploy for CE 4.0.

I'm all ears. Suggestions, points, whatever would be highly appreciated. Thank you!

YES I DID SEE THE KB. IT DIDN'T HELP

See it here: http://support.microsoft.com/kb/974247

RESULTS FROM CORFLAG

Okay, tried that and these are my results: C:\Development\Mvc2MessingAround\Mvc2MessingAround\bin\Lib>corflags System.Data. SqlServerCe.dll Microsoft (R) .NET Framework CorFlags Conversion Tool. Version 3.5.21022.8 Copyright (c) Microsoft Corporation. All rights reserved.

Version   : v2.0.50727
CLR Header: 2.5
PE        : PE32
CorFlags  : 9
ILONLY    : 1
32BIT     : 0
Signed    : 1

I would have sworn I installed the x86 version of both versions of Sql CE (3.5/4). The installer might have gotten confused somehow because my processor is 64bit capable, but i'm running Windows xp sp 3 32 bit. The results seem to indicate it's 64 bit. Is that the case?

ADDED DETAILS

To date the configurations below have been tried on 2 machines. Both are Windows xp sp3 32 bit with a 64 bit capable processor. The development environment on both is VS 2008 Pro. The results on machine 2 come after a fresh install of the Sql CE 4 Ctp.

CONFIGURATION #1

myapp\bin\
     System.Data.SqlServerCe.dll

myapp\bin\private
    amd64
    x86
    
myapp\bin\private\x86
    sqlceca40.dll
    sqlcecompact40.dll
    sqlceer40EN.dll
    sqlceme40.dll
    sqlceqp40.dll
    sqlcese40.dll
    
myapp\bin\private\amd64
    sqlceca40.dll
    sqlcecompact40.dll
    sqlceer40EN.dll
    sqlceme40.dll
    sqlceqp40.dll
    sqlcese40.dll

Error:

An exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.DLL but was not handled in user code

Additional information: Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8402. Install the correct version of SQL Server Compact. Refer to KB article 974247 for more details.

Code:

SqlCeConnection conn = new SqlCeConnection();

CONFIGURATION 2

Same as #1, but with System.Data.SqlServerCE.Entity.dll at myapp\bin direction.

The page errors before hitting the code above. This is the message:

Could not load file or assembly 'System.Data.SqlServerCe.Entity' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.BadImageFormatException: Could not load file or assembly 'System.Data.SqlServerCe.Entity' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

I've checked the project settings in VS 2008 Pro and the .Net 3.5 framework is set as the target.

CONFIGURATION 3

Same as #1, except the System.Data.SqlServerCE.dll is referenced from the myapp\bin\private folder.

Results are the same as CONFIGURATION #1 (error message is 100% same and the error occurrs on the same line of code).

CORRECT CONFIGURATION

Per Erik's instructions (had I followed them more carefully), the setup should be

myapp\bin
    x86
    amd64
    System.Data.SqlServerCE.dll

Reference the System.Data.SqlServerCE.dll directly from the bin folder for the code. My folly was thinking the Private folder needed to be included, but it doesn't. Do not put the System.Data.SqlServerCE.Entity.dll in the bin folder unless you are using a .net 4.0 solution. I don't think that dll works w/ 3.5.

Helpful link:

Link

Sock

System.Data.SqlServerCe.dll is Any CPU... – Retroversion

Using config 3, no Entity.dll file. Wrong folder structure! Copy the following directory contents (including the x86 and amd64 folders) to the bin folder of your ASP.NET app: C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private There should be no "Private" folder in your asp.net bin folder. Under your bin folder, a x86 and amd64 folder, each with the correct files. – Retroversion

This also happens with VS 2010 and ASP.NET MVC 3 Beta – Custard

You need either x86 or amd64 alone if you know your architecture. Also you can have all the dlls in your output folder itself (by moving dlls in x86 folder and removing the folder) if thats cleaner. – Siouxie

Hi, the configuration works perfectly in Windows 7/8/10, but in Windows XP dont. Any idea for this case, I would appreciate it very much. – Preeminent

Answer 1

SQL CE 3.5 does not work with ASP.NET, you must use 4.0 CTP.

Download from here.

Install the runtime.

Copy the following directory contents (including the x86 and amd64 folders) to the bin folder of your ASP.NET app: C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private

UPDATE: Use System.Data.SqlServerCe.dll from the Desktop folder to avoid Medium Trust issues

myapp\bin\ 
 System.Data.SqlServerCe.dll 

myapp\bin\x86 
 sqlceca40.dll 
 sqlcecompact40.dll 
 sqlceer40EN.dll 
 sqlceme40.dll 
 sqlceqp40.dll 
 sqlcese40.dll 

myapp\bin\amd64 
 sqlceca40.dll 
 sqlcecompact40.dll 
 sqlceer40EN.dll 
 sqlceme40.dll 
 sqlceqp40.dll 
 sqlcese40.dll 

Add a reference to the System.Data.SqlServerCe.dll file you just put in your /bin folder.

Place the SQL Compact sdf file in your App_Data folder.

Add connection string:

<connectionStrings>
   <add name ="NorthWind"
   connectionString="data source=|DataDirectory|\Nw40.sdf" />
</connectionStrings>

Connect! :-)

using System.Data.SqlServerCe;

    protected void Page_Load(object sender, EventArgs e)
    {
        using (SqlCeConnection conn = new SqlCeConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
            conn.Open();
            using (SqlCeCommand cmd = new SqlCeCommand("SELECT TOP (1) [Category Name] FROM Categories", conn))
            {
                string valueFromDb = (string)cmd.ExecuteScalar();
                Response.Write(string.Format("{0} Time {1}", valueFromDb, DateTime.Now.ToLongTimeString()));
            }
        }
    }

Retroversion

Did everything you suggested here. I'm still getting the same 'Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8402. Install the correct version of SQL Server Compact. Refer to KB article 974247 for more details.' error whenever I try to create a new SqlCEConnection object. I've cleaned my temp files, exited VS 2008, cleaned the project, rebuilt, etc. Thoughts? – Sock

Sorry, tested with VS 2010 and .NET 4.0 - maybe this is required? Try the free VS 2010 Web Dev Express... – Retroversion

That'd really stink if it is as my hosting doesn't offer .net 4.0 yet. I would certainly think they'd target the 3.5 sp1 framework for this, but they might not have. – Sock

Just tested with VS 2008, works fine on my machine. Only diff is not to copy System.Data.SqlServerCe.Entity.dll to the bin folder. I am running Win 7 x64. – Retroversion

@Erik, I copied the Private folder itself and put it into the bin folder. Immediately inside of that is that System.Data.SqlServerCE.dll & System.Data.SqlServerCE.Entity.dll. Also included in the private folder are the amd64 & x86 folders with the other dll's for CE 4.0. I presumed that the code had someway of know which one to use. I'm running VS 2008 Pro, Win xp sp3 32 bit. – Sock

I guess its worth asking, does Sql CE 4 know to look for an x86 or amd64 folder for the other dll's it needs? I haven't done anything to bring them into the project except by copying that private folder with the x86 & amd64 folders into the bin folder. I've tried deleting the amd64 so the x86 was just there, but still get the same error. – Sock

Yes, it will look for the unmanaged DLLs in the folder corresponing to your processor architecture environment variable, either x86 or amd64. – Retroversion

Message: unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8402. Install the correct version of SQL Server Compact. Refer to KB article 974247 for more details. The kb article mostly details 32 bit vs 64 bit version conflicts, but does not really detail this error. I know for sure I installed the x86 versions of both sql CE 3.5 & 4.0. You should be able to run them side by side from the articles I've come across, though, I've never tried running either of them until now. – Sock

And you are referencing the System.Data.SqlServerCe.dll in the /bin folder as per my guide? I doubt it. – Retroversion

I see you found me on twitter. Small world. I've updated the question with details. In short, yes, I am referencing the System.Data.SqlServerCE.dll in the /bin folder per your instructions and I am getting the same error. The updated question shows (or will show) my configuration in detail. – Sock

Please follow my steps one by one. No "lib" folder, just a file copy of the SQLCE folder to your bin folder. And of course you cannot use a 3.5 file with 4.0, use WebMatrix or sqlcecmd.codeplex.com to create a 4.0 one. – Retroversion

Okay...I'm an idiot. I had misunderstood your guide. The correct way to do this (as your blatently instructed) is to copy the x86 & amd64 folders to the bin directory of my application. Have the System.Data.SqlServerCE.dll in the bin directory itself. Once I did this, I could create a new SqlCeConnection. The application blew up if I included the System.Data.SqlServerCE.Entity.dll, though, so I removed it. Thanks for your patience on this @erik! – Sock

Thank you kind sir! You have helped me get SQL CE CTP1 working on my machine! Kudos and +1... I wish this info was more easily findable! – Slider

Wow that is annoying. Thank you for providing a very helpful answer! – Custard

@Retroversion you said UPDATE: Use System.Data.SqlServerCe.dll from the Desktop folder to avoid Medium Trust issues but its version is different from what which is in private folder! the only dll which works fine for me (in a windows forms desktop app) is what which is in private folder – Hellgrammite

The original question is regarding Web apps, not dekstop apps. For desktop, you can use the file in the Private folder, yes. – Retroversion

Answer 2

If your using a connection string that uses a providerName and you haven't installed the SDK, then you also need to add this to you web.config (or app.config)

  <runtime>
     <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <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.1, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
  </system.data>

NOTE: the "remove" is needed in case you installed the SDK, as that will put this info in your machine.config

Behistun

Thanks! That solved a problem I'd been fighting. But WHY does it work? The System.Data.SqlServerCe.dll has a version number of 4.0.8435.1 on my system and your bindingRedirect targets 4.0.0.0-4.0.0.1. So why does the bindingRedirect even affect the situation in this case? I'm glad it works, I just don't understand why. Can you clear this up for me? – Fiducial

Just for reference purposes @ScottBussinger: I think you can skip the <runtime> element, and use 4.0.0.0 on the <DbProviderFactories>. If you use 4.0.0.1 on the latter, you effectively need to add the binding. – Nata

Answer 3

OK, here's a guess, since you're fishing for them.

Run corflags.exe on the assembly you copied to your references directory. What type of machine are you building for? If you're on a 64-bit machine and you're compiling to x64 or anyCpu, make sure that corflags tells you that your references are not 32-bit only references. Maybe it's "falling back" to an the wrong version in your GAC or something. If it tells you that the referenced assembly is 32-bit only, either compile your project as a 32-bit project or find a 64-bit version of the DLL?

Insinuate

Answer 4

If you are installing the SQL CE provider using NuGet, the simplest solution is to add a post-build step to copy these from the NuGet package NativeBinaries folder

Gadmann

This answer sounds plausible but I don't understand it. Could you, please, add an example of the necessary post-build step? – Tetrameter

Copy the DLLs from the packages folder to the bin folder using the technique described at #491968 – Gadmann

Answer 5

The key for me was realizing that the version of System.Data.SqlServerCe.Entity.dll in the Private directory (C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private) is 4.0.0.1, where the version beneath the Desktop directory (C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Desktop\System.Data.SqlServerCe.Entity) is 4.0.0.0. The version of System.Data.SqlServerCe.dll in the Private directory is 4.0.0.0.

I think it was a mistake on the part of Microsoft to distribute an updated SqlServerCe.Entity.dll without a corresponding update to SqlServer.dll.

Augustaaugustan

The assembly version of System.Data.SqlServerCe.dll in the Private directory is 4.0.0.1 ! – Retroversion

I second Erik. If you want to deploy with a private CE instance you need to link your project against the System.Data.SqlServerCe.dll version 4.0.0.1 found in the private directory. This is to avoid future problems when updating SQLCE – Gauhati

Answer 6

I build an asp.net web api and hosted it on azure and faced some issues with sql server compact I fix it by: first remove all system.data.sqlserverce.dll and any dll use it then installed these tow packages :

Install-Package SqlServerCompact then rebuild

Install-Package EntityFramework.SqlServerCompact -Version 4.3.6 then rebuild

after I did it just worked fine for me + install any NuGet package that depends on system.data.sqlserverce.dll they all just work great I hope this will help some one reference

Fayalite