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.
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.