What is the best deployment approach for WPF applications with local database?

Question

• I want to make a WPF application that exists in one directory including all files that it needs: .exe, .mdf database, .xml config files, etc.
• the application should work no matter what directory it is in so that it supports this scenario:
  • person 1 executes the application in c:\temp\wpftool.exe
  • the application reads and writes to the c:\temp\wpftool.mdf database
  • person 1 zips up that directory and sends it to person 2 via e-mail
  • person 2 unzips it to c:\Users\jim\documents\checkout\wpftool.exe, the application reads and writes to the same database in that directory (c:\Users\jim\documents\checkout\wpftool.mdf)
  • person 2 zips the directory up again and sends it back to person 1 to continue making changes on it

What is the best way to create a WPF application that supports the above scenario?, considering:

• there should be no hard-coded database connection strings
• what is the best deployment method, click once? or just copy the .exe file out of the /release directory?
• reasonable security so that users have to log in based on passwords in the database, and if a third person happens to intercept the e-mail, he could not easily look at the data in the database

Answer 1

For the database, I would look into using the "user instance" feature in SQL Express. Combined with the |DataDirectory| substitution string support it makes it very easy for your application to get hooked up.

In all honesty I have not deployed a ClickOnce app leveraging this approach myself yet, but I just thought I would bring it to your attention because it's what I would look into myself if I was building something like you described.

Answer 2

Some points on the database side:

Assuming the "New user" already has SQL installed, they'd need to attach the (newly copied) database. Besides having sufficient access rights to attach a database, your application would need to configure the call to include the drive\folder containing the database files. If your .exe can identify it's "new home folder" on the fly, you should be able to work that out.

Define "reasonable security". Any database file I get, I can open, review, and ultimately figure out (depends on how obscure the contents are). Can you obfuscate your data, such as using table "A" instead of "Customer"? Would you really want to? The best possible security involves data encryption, and managing that--and in particular, the encryption keys--can be a pretty advanced subject, depending on just how "secure" you want your data to be.