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

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

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

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?