Can't Get SQL Server Compact 3.5/4 to Work with Asp .Net MVC 2

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

Unable to load the native components of SQL Server Compact

Finally got SQL Server Compact Edition 4.0 working under IIS 7.5. The problem was permission issue. Current Application Pool's identity IWAM_plesk(default) didn't have access to SQL Server Compact 4.0 folders:

C:\Program Files\Microsoft SQL Server Compact Edition\v4.0

C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0

I just granted Read & Execute and List Folder Contents permissions and now it works like a charm.

Using SQL Server CE 4 on a remote host with MVC 3

I don't like to respond to my own answer, but after hours of work I have the answer!

We need:

NuGet (Better than copying the dll's from program file)

With NuGet we install:

EFCodeFirst

SqlServerCompact

EFCodeFirst.SqlServerCompact

The problem was that EF need another dll for SQL CE 4 (System.Data.SqlServerCe.Entity.dll) and we need to put some configuration on web.config:

  <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.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
    </DbProviderFactories>
  </system.data>

With that, all is working. I have seen like 5 different errors, so we need:

The 2 dll from SQL CE 4, the EF dll, the config in web.config and the native dll (from the package directory where NuGet downloads the library).

It seems that the web.config config is pointing to a concrete version of the SQL CE .dll and the version of the RTM package is different. I can't find the concrete version so I use the .dll from NuGet.

That's all, SQL CE 4 + EF on a remote host.

SQL server compact 3.5 namespace not found

In Add Reference, if you do not see the reference as in the screenshot, browse to the C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5\Desktop folder

Using Entity Framework with an SQL Compact Private Installation

I figured out how to do it, thanks to a blog post by Steve Lasker. Basically, here is what you have to do:

(1) Set a reference to System.Data.SqlServerCe.dll in your project. Set the CopyLocal property to True.

(2) In the App.config for your project, add the following XML markup. It tells EntityFramework to look to your private installation of SQL Compact for its data provider:

<system.data>
    <DbProviderFactories>
        <remove invariant="System.Data.SqlServerCe.3.5"/>
        <add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
</system.data>

(3) In the Setup project, add the following files to the Application Folder in the File System Editor:

• sqlcecompact35.dll
• sqlceme35.dll
• sqlcese35.dll
• System.Data.SqlServerCe.Entity.dll

---

Related Topics