[Don't miss the update below]
When I put my first pre-populated database file into a Mango project & ran it, I was unable to access the distributed database:
“Access to the database file is not allowed.”
Oddly, this error doesn’t appear until you actually attempt to select a record from the database.
When I originally created this database in my DB generator app, I used the following URI as a connection string:
As you might expect this saves the database to the Isolated Storage of the DB generator app. I point this out because after this database is extracted from the DB generator app, added to your “real” project & deployed to a phone, the database file gets written into a read-only storage area which you can connect to via the appdata:/ URI:
Although the DataContext constructs without a problem, using the appdata:/ URI, attempting to select records from this database threw the file access error above.
I wanted to rule out whether my database had actually been deployed at all, so I tried connecting to a purposefully bogus file:
Which gave me a different error (file not found), so I knew the database had been deployed and was in the expected location; but strangely, it looks like DataContext / LINQ to SQL doesn’t let me select records while the file resides in the app deployment area. Which effectively renders the “appdata:/” connection URI somewhat pointless :P
What you can do is copy the file out of appdata & into isostore:
This may be something to keep in mind, esp. if you planned to deploy a massive database with your app (although distributing a heavy .xap to the marketplace probably isn’t very advisable in the first place).
Arsanth’s database is only ~200KB at the moment, so producing a working copy is a trivial expense in terms of both storage space & IO time. I was just slightly surprised I actually had to this in order to select against a pre-populated database distributed with the app, so be aware of this gotcha.
Thanks to @gcaughey who pointed out connection string file access modes, which I was not aware of yet.
So instead of copying to Isolated Storage, I tried this connection string:
“datasource=’appdata:/db.sdf’; mode=’read only’”
And sure enough I can select records directly from the distributed database. Right on! If you wanted full CRUD & persistence to your distributed database, you can always create copy as described; but if all you need to do is select from the distributed database, you can set file access modes in the connection string.