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:

http://blogs.msdn.com/b/sqlservercompact/archive/2010/07/07/introducing-sql-server-compact-4-0-the-next-gen-embedded-database-from-microsoft.aspx

Answer 1

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?

Answer 2

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

Download from here: http://tiny.cc/cfjia

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

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

Answer 3

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 ever to install the SDK, as that will put this info in your machine.config