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

Asked 2010-07-11T14:19:07.247

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 Sy

Edit Report

3 Answers

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

answered 2010-07-11T14:53:37.520

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?

answered 2010-07-11T14:26:08.710

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.

answered 2011-01-16T15:08:43.897

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

3 Answers

Your Answer