Where is your SQL Server CE Database?

Maybe not where you think. Now, I admit I am three years late with this bug strange feature of Visual Studio but it wasted some of my time today so it is still worth reporting.

I’ve been writing about creating database applications in Visual Studio. Specifically, I was looking at what happens if you download Visual Basic Express and take the quickest, easiest route to knocking together a database application.

The default local database engine these days is SQL Server Compact 3.5:

When you create applications, the preferred local database is SQL Server Compact 3.5.

says MSDN.

OK, so you add a new database to your project and accept various defaults. The wizard then asks you whether you would like to “copy the file to your project and modify the connection”?

Sounds reasonable, if you can figure out what it means. Default is Yes, so OK do it.

Mistake. Don’t do that. Not, at least, without reading and understanding this document. But I digress. Next up, you are asked another question:

Storing connection strings in your application configuration file eases maintenance and deployment … do you want to save the connection string to the application configuration file?

It’s another option that sounds good. OK, do it.

Now you set up a little table or two, add some data-bound controls, and off you go. Run the app, enter some data, save it. Run the app again … and all your data has disappeared. Why?

Well, it has to be either that the updates are silently failing; or that the database file is getting overwritten. It’s the latter. It turns out that VB is treating your database like any other resource, and copying to bin/debug when you run the app. This is the copy you are connecting to, you update it, but next time you build and run it gets overwritten.

None of this is obvious, because when you look at the connection string in the application settings (which VB hides by default, sigh), it shows the database file in the root of your project folder. Click Test Connection, all is fine. The only warning sign is that the connection string looks like this:

Data Source=|DataDirectory|\test.sdf

So where is |DataDirectory| set? That’s not obvious either. Read here for the answer. It’s an application property that is not visible anywhere, that gets set to different values depending on how you deploy the app. I can see why someone thought this was a smart idea; but the implementation is horrible. It gives you the illusion of having one database file, when in fact you have multiple copies (source, debug, release etc) overwriting one another, and during testing you are never editing the correct one.

Once you have worked this out you can fix it, of course. But here’s another problem. You are the single user of a database. You insert a record and save it, using all the generated data-bound stuff that Visual Studio provides. Works fine. Then you edit the record you just inserted, and save again. Boom. Concurrency exception. Why?

It is all do to with a limitation of SQL Server Compact 3.5. It can’t handle multiple SQL statements. This means that a feature of the ADO.NET TableAdapter, called Refresh the Data Table in the configuration wizard, is not available. This option kicks in when you have an identity column that auto-increments, which is by the easiest way to create a primary key. In this scenario, the actual value of the identity column is not known until after you make the insert, because it is generated by the database engine. Normally, the TableAdapter would retrieve it with a Select statement immediately after the Insert statement. However, with SQL Server Compact 3.5 that does not work.

The result is that saving a record works fine, but next time around the row has an incorrect primary key in the DataSet. No wonder you get a concurrency exception.

You can work around this in code, of course. But what surprises me is just how hard Microsoft has made all this for the kind of newbie programmer who might pick up VB Express. In fact, easy database programming in VB has marched backwards since Visual Basic 3 back in 1993.

By the way, I also dislike the way VB adds so much database gunk to your main form, again by default. What if you add another form to your app? What if you want to delete the first form? It all gets messy fast.

Look at Ruby on Rails. It has simple database handling that works. OK, you are going to have to modify that code eventually; and I accept that database apps have an inherent complexity that no amount of wizards, O/R layers or even "Convention over Configuration” can remove. I still think that simple, single table, single user apps should be, well, simple. Not in VB, unfortunately.

4 thoughts on “Where is your SQL Server CE Database?”

  1. Hi.

    What is your opinion of RoR vs PHP?

    Just curious as I finally have some time to really get into a new dev language and I am trying to choose between RoR and PHP/Prado.

    One project I was considering was writing a UI designer for Prado to simplify page design. But if I go for RoR then I’ll shelve it for the moment.

  2. @Edward

    I guess this is maturity (PHP) vs productivity (RoR).

    I’d suggest you try both and let us know what you think 🙂

    Tim

  3. Thank you. MSDN mentions that Access doesn’t work with “Refresh the Data Table” but they never really come out and say that SQL Server Compact 3.5 doesn’t support it either. I’m not a newbie but am trying to switch from larger server app to small client side app…
    Gosh, was I spoiled with my views, stored procedures, and ability to automatically get my identity columns. Thanks for the black and white. And yes I was a misfortunate one that hit the “Ok” to the copy local question as well.

  4. This is the only msg I have found that actually says what is going on. Several posts on other sites, including those sponsered from MS have a lot of false information and guesses. I thank you for this post for the fix was instant even though I had to redo all the associations which as you stated shows how painful this implementation of accessing data is. I was wondering if you are aware of other posts or information that clearly shows what is going on. I have figured things out by trial and error, running code and checking what is in memory versus what is written to disk but that is really a waste of time. I figured since this product has had several versions there should be some good documentation but I can’t find it. Every example uses different syntax and a clear explanation of how it is working or what is needed is never given. For example, people throw in code but don’t explain why the line of code is needed.

    I now understand some of the layers but am not 100% sure for I have been comparing the dataset data to the table adapter data to the database table. It is interesting to see how many copies of the data are maintained and which would allow a programmer to check if the record has changed or if the underlying data has changed.

    One thing that is missing in your article is … where is the mdf created and how can one access it from MSSQL Server. I tried to put the file in a specific directory but I was ignored .. some files were created where I wanted them but the MDF was not.

Comments are closed.