SQL Server Compact Edition is advertised as a small embedded database that can easily be included with a Windows Forms application during deployment. According to some of the informal documentation online, one of the ways in which this is suppossed to be possible is to include the SQLce DLLs in the application file of your deployment. However, the official documentation (SQl Server CE Books Online) only mentions the options of installing the product via .msi on the client machine, or using a Click-Once application.
I would much rather deploy without having to do Click-Once or a full-install of SQLce. So I tried copying in the DLLs from the C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\ on my computer into the application directory during deployment, and it worked (!). There are 8 DLLs included, and through trial-and-error I was able to determine that (at least in my case) only four DLLs were really needed when deploying:
So I am in the middle of building a pretty substantial Windows Forms application in .Net 2.0/C#, which will rely very heavily on database integration. Currently development is proceeding using Sql Server Express 2005 for the database server. It is a very robust product that supports anything that you can do on the full edition of SQL Server, and is freely distributable. Stored procedures, triggers, the works.
However, when making the first internal release intended to be installed by other people, I ran into some problems, specifically related to the way that SQL Express works. As it is really the same thing as the full version of SQL Server (with limits put on DB size and other licensing issues), it requires a big download (around 35mb), large db file footprint (around 20mb) separate installation, and runs as a service on the client machine. Though the install can be packaged as part of your overall distribution and set to install “silently”, it is still an issue that requires ongoing support and trouble-shooting. Additionally, you can sometimes run into problems with the SQL Express service, permissions, problems attaching the database to the server, etc. It is a robust solution, but the potential problems involved in installing SQL Express on thousands of client machines and getting it to run flawlessly all the time is enough to give me pause.