Setup projects with SQL Server Compact Edition (CE) databases Christian Donner, April 12, 2009August 19, 2009 This article is no longer relevant. In Visual Studio 2008 SP1, setup projects allow you to select the ‘User’s Application Data Folder’ as a deployment target. This is where any SDF files and other application data and content should go. The ‘Common Application Data Folder’ is no longer accessible for write operations in Windows 7 and should not be used. The application should use System.Environment.SpecialFolder.ApplicationData to obtain the physical path to this folder. The part about the Data Source string below remains valid, however. It is no longer an acceptable practice to deploy data files to the same location as an application’s executable. Vista’s more strict security model by default assumes read-only access for the files in the Program Files folder. Unfortunately, even the folks at Microsoft seemed to struggle with this change for a while, because the deployment of project files to the Application Data folders is not implemented very well in Visual Studio (2008). I found quite a few instances in various forums on the Web where people stumbled over this issue, but not a single instance of a good answer. I hope this is one here. The setup project gives the user the option to install the app “for the current user only” or “for all users”. Consequently, the database file would end up in either the current user’s or the All Users application data folder. The setup would have to write this information somewhere so that the application can later retrieve it, when it comes to accessing the database. How else would it know which application data folder to look in? To avoid this issue, I just wanted to install the database in the All Users/Application Data folder, regardless of whether the application was installed for one user or for all users. I realize, of course, that two users could not install the application on the same computer without overwriting each other’s data. This is such a remote possibility, though, that I don’t want to consider it. This turned out to be not so easy, and I figured it would be worth-while posting the solution here (in addition to the my answer on Stackoverflow.com). The first piece of the puzzle I got here: Form_Load(object sender, EventArgs e) { // Set the db directory to the common app data folder AppDomain.CurrentDomain.SetData("DataDirectory", System.Environment.GetFolderPath (System.Environment.SpecialFolder.CommonApplicationData)); } The DataDirectory property in the AppDomain will be used to expand a place holder in the connection string. The CommonApplicationData special folder points to the holder that the database was installed to. Now we need to make sure that the data source contains the DataDirectory placeholder. This piece came from here. In the DataSet designer, find the DataSet’s properties, open the Connection node and edit the ConnectionString property to look as follows: Data Source=|DataDirectory|\YourDatabase.sdf Then I followed Lyman Enders Knowles’ instructions (from his own answer to the question on Stackoverflow) for how to add the Common Application Data Folder to the setup project, and I placed the database file in that folder. I then followed Ove’s suggestion from Stackoverflow, i.e. I checked the “Enable ClickOnce Security Settings” and selected “This is a full trust application” in the Visual Studio project settings. After that, the application deployed fine on Vista and the database file was accessible for both reads and writes. Related Posts:Enphase Envoy Local AccessTyreWiz not working after battery changeThe Great Cat Litter Poop OffSUTAB Scam?Amazon threatens customer of 26 years Security Smartphone SQL Server CommonApplicationDataDataDirectorySecuritySetupSQL Server CEVistaVisual Studio 2008XP
Hi Christian, I am having a similar issue and wonder if you can help me out. I am using visual studio 2008 SP1. I too have a .sdf file. This file needs to be readable and writable for all users on a machine. The application itself handles multi user support, and some information is shared between users. I therefore need a place where all users can read from and write to, and I need this to be consistently set across XP, Vista and Windows 7, otherwise I’m sure I will have to write a bunch of extra code in the application as well as custom actions in the installer project. You mention in your top-edit that the “User’s Application Data Folder’” should now be used, but this gets back to the problem of it only being available to one user. I need a spot that all users have access to. Does such a spot exist, and if so, how can I tell my setup project to deploy to it, and my application to look for the .sdf there?
My solution needs tweaking for Windows 7. While it works, I do get challenged for the admin password every time I start the program. As you may know, Windows 7 “virtualizes” the directories that applications require write access to that do not adhere to the Windows 7 model. My database for instance ends up in C:\Users\[user]\AppData\Roaming\[App Name]. I think you should use “SpecialFolders.CommonApplicationData” (see this post on Stackoverflow) Other resources: http://www.svrops.com/svrops/articles/jpoints.htm http://technet.microsoft.com/en-us/library/cc766489%28WS.10%29.aspx
Thanks for your response Christian. I actually tried CommonApplicationData per the following… In the app, using the path Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData) + “\\” + Application.ProductName + “\\MySDF.sdf”) In the installer, creating a custom directory with the properties: DefaultLocation = [CommonAppDataFolder]\[ProductName] Property = COMMONAPPDATAFOLDER I then tried using this with a limited rights (user only) account on my XP machine, and still got the same “access to the database is not allowed” error. It looks like CommonAppDataFolder is not writeable either! 0_o
Dear All In the Application the connection string Data Source=.\SQLEXPRESS;AttachDbFilename=|AppDataFolder|Database\mydatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True is interpreted as C:\Program Files\Default Company Name\SetupTestOne\Database\mydatabase.mdf so It is use less and if we use Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) That returns the AppData\Roaming folder under the user’s profile. by this we can get access of our database but “what about our password security and data hiding? Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) command is accessing from our program and in program we use connection string, then our data security become very loose. we couldn’t hide our password and usernames etc.