How to deploy SQL CE 4 CTP to shared hosting?

Question

How do I deploy SQL CE 4.0 with EF4 to a shared hosting provider for ASP.NET MVC 2.0?

I've included System.Data.SqlServerCe.dll, and the amd64 + x86 directories in my bin folder, but keep getting a ".net provider not found". I realize it's currently in CTP, but this is just for testing purposes. My project + host is configured for .net 4.0

Answer 1

With Visual Studio 2010 there is now an easy way to deploy SQL CE 4 to a shared hosting environment through the use of "Deployable Dependencies". You do not need to manually copy dlls to your bin folder or modify your web.config. Both of these are done for you.

"We can include required assemblies in a Web site or Web Application project with a few simple steps. You right click the project node in Solution Explorer and select 'Add Deployable Dependencies…' context menu item."

Deployable Dependencies http://blogs.msdn.com/cfs-filesystemfile.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-63-56-metablogapi/4403.MenuItem_5F00_1.png

(http://blogs.msdn.com/b/webdevtools/archive/2011/01/06/how-to-bin-deploy-sql-compact-edition-4-0-and-razor-web-projects.aspx)

Answer 2

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

Answer 3

There are two possibilities which may cause this problem:

1. When you install SQL CE on the development machine with the Windows Installer, an entry to machine.config is added for the provider. However when you deploy to the hosting, the provider entry will not possibly exist so you should add this entry to your web.config (especially for EF to work I guess):

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

2. Edit: Fortunately Microsoft solved the deployment problem in SQL CE 4.0 RTM (version 4.0.8482.1) which is released on 1/12/2011. The “Private” folder contains both the x64 and x86 related DLL files, and now also contains the required C++ runtime DLLs under amd64\Microsoft.VC90.CRT and x86\Microsoft.VC90.CRT subfolders. So from now on, deploying all the DLLs and folders in the Private folder deploys all the files needed for Compact to work properly. SQL CE 4.0 CTP looks promising but deployment still sucks, we still need to include several native DLLs (amd64 and x86 directories, this is ugly) and in addition there is a dependancy on Visual C++ 2008 Runtime Libraries SP1 which no one seems to be aware of. How are we supposed to be sure a shared hosting has this fix? I wish we were able to deploy simply by including System.Data.SqlServerCe.dll (and System.Data.SqlServerCe.Entity.dll for EF support). I hope they release a fully managed version (like VistaDB) in the future. At least MS should have forced this runtime along with .Net Framework 4.0 installation, see below for more information:

   Installing the SQL Server Compact 4.0 CTP1 using the Windows Installer (.exe) file, also installs the Visual C++ 2008 Runtime Libraries SP1. If SQL Server Compact 4.0 CTP1 is deployed privately in the application’s folder the following have to be present on the machine for SQL Server Compact to function properly:

   1.Installing the .NET Framework 3.5 SP1 also installs the Visual C++ 2008 Runtime Libraries SP1.

   2.Visual C++ 2008 Runtime Libraries SP1 can be downloaded and installed from the location given below: http://go.microsoft.com/fwlink/?LinkId=194827

   Note that installing .NET Framework 2.0 or 3.0 or 4 does not install the Visual C++ 2008 Runtime Libraries SP1.