Two apps i have in mind...one is for the home market  and I was thinking i didn't want the overhead and possibly slower performance of the SQL Express if I could use compact edition.  Also, i am not sure whats involved in distributing sqlexpress to get it install properly as part of the application installation.

do you have some experience with sqlexpress and distributing?

Distributing SQL Express is extremely easy.  In fact, if you use a tool like InstallAware, it is literally as easy as clicking a CheckBox and tweaking a few parameters.  We use InstallAware for our medical software and though, like any install software, there are some frustrations, one thing that they got right were the pre-reqs and installing pre-reqs.

Even if you choose to do this yourself, you can download the SQL Server Express redistributable and via command lines install it very easily.  SQL Server is also easy to test for in regards to services if you take the manual approach.  We use SQL Server Express in a high percentage of our medical software user base and so we have to test for and install SQL Server Express if needed.  This is really one of the easier parts of the install.

If I had to choose between those two it would be Access.  FoxPro is nothing but a problem waiting for a place to happen...this comes from MANY years of experience. 

But in truth, I would not use either of these, but SQL Server Express.  I know that this post has been geared around embedded database conversation, but there is a reason that we have not yet created a data source item for this...there is generally a better solution.  I do see the benefits of a disconnected laptop for the day, etc.  But even then I would tend to lean towards SQL Server Express.  First of all, it is free.  Secondly, it is faster and a full version of SQL Server.  Additionally, you don't have to deal with data type issues.  For example, SQL Mobile doesn't have a VarChar data type but only a NVarChar (unless something has changed recently).  Not that using an NVarChar is bad, but it just might not line up exactly with the desktop structures (if you a.] have the need or b.] ever plan to have a larger version of an application).

In my opinion, and if I were hired in a capacity for consultation purposes in a situation like this, I would tell my client that they are better off developing on a full SQL Server version versus an embedded edition....I have a lot of embedded experience from years past and there are other issues that you will inevitabely run into (i.e. row locking, etc.).

Trent is right naturally, anything but a very simple app or disconnected feature would be better with Express.

That being said, my current project is VERY simple as far as data goes as it mostly does scanning and image manipulation, and will be single workstation only, so SQL Compact I think will be the right choice based on past experience with it.

The attachment is a very simple working sample that uses SF 1.6.6, the ADO .NET Provider for SQL Compact 3.5 (had issues with OLEDB updates I couldnt get past) included with VS2008, and the System.Data.SQLServerCE namespace used to create a SQLCEDataSourceItem. I tested with OptomisticRowVersion since that is what I use. Just slap the SDF file in the ZIP onto the root of your C drive and it should work. Good luck and I hope it helps someone.

Keith Chisarik

Thanks for the contribution, Keith.  You may want to add this to the user contributed samples area...I think that this would be a good fit

Hi.

What is the specific error you receive, Marcelo?  The connection string looks fine, and I'm pretty sure that the System.Data.SqlClient provider will support SQL Server CE.