c# - 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 FilesSql Server compactv4.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 FilesSql Server compactv3.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:DevelopmentMvc2MessingAroundMvc2MessingAroundinLib>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

myappin
     System.Data.SqlServerCe.dll

myappinprivate
    amd64
    x86

myappinprivatex86
    sqlceca40.dll
    sqlcecompact40.dll
    sqlceer40EN.dll
    sqlceme40.dll
    sqlceqp40.dll
    sqlcese40.dll

myappinprivateamd64
    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 myappin 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 myappinprivate 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

myappin
    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

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 FilesMicrosoft SQL Server Compact Editionv4.0Private

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

myappin 
 System.Data.SqlServerCe.dll 

myappinx86 
 sqlceca40.dll 
 sqlcecompact40.dll 
 sqlceer40EN.dll 
 sqlceme40.dll 
 sqlceqp40.dll 
 sqlcese40.dll 

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