How to make updates stick to database?
If you are working in Visual Studio and are struggling to save data to your local database then this article is for you. Visual Studio makes a copy of the database in output(debug) folder every time the project is debugged and this is the root cause of the problem. This article explains how to overcome this issue.
Background: When I was new to Visual Studio I used to face a peculiar and very frustrating issue. Whenever I used to insert or update data through the application the changes would not be reflected to the database. The next time I ran the application the updates made during previous run would be gone!
This issue became a big pain in the neck. After scouring the web for a long time I finally found the answer which I will be sharing with you guys. 1. When adding database as a "New item"...
i. Go to Project -> Add New Item
In the Add New Item Dialog box choose the database of your choice(Local or Service-based). I will add Service-based Database.ii. In the Data Source Configuration Wizard change the DataSet name if you want to and then click on Finish.
Database is created in the Root Directory.iii. Change the value of "Copy to Output Directory"
In the Solution Explorer click on the database file and find the Copy to Output Directory property in the Properties dock panel. The default value of this property is Copy always. Change it to Do not copy. Why are we doing this?
When the Copy to Output Directory is set to Copy always what Visual Studio does is it copies the Database from the Root Directory to the Output Directory (bin/Debug) every time when you run/debug the project/solution.
Here is the catch, when you run the project, the project uses the database from the Output Directory and whatever data you insert or update its done on this database. Now you got to know why updates weren't sticking to the database. iv. Change the Connection String in app.config
So what we did in the previous step was to avoid the replication of database. Now we have to hard code the Connection String. Find the app.config file in the Solution Explorer and double click it. There you will find that the Connection String is soft coded this way,
Data Source=|DataDirectory|\Sample.mdf;
|DataDirectory| is a macro which resolves to the Output Directory's path during run time. But we have the database in the Root Directory so change the Connection String to this,
Data Source=C:\Users\Admin\Desktop\WindowsFormsApplication1\WindowsFormsApplication1\Sample.mdf;2. When adding an existing database...
i. Go to Project -> Add Existing Item
In the Add Existing Item dialog box browse to the database and select it. In the following Data Source Configuration Wizard select the Data objects and click Finish.
If you are prompted to copy the database to Output Directory say 'NO'.ii. Follow the steps iii and iv from above and change the Connection String to the path from where you exported the database.
3. When deploying your application...
Since you have hard coded the Connection String in app.config it will cause problems during/after deployment. To avoid that change back the absolute path to |DataDirectory| before the Final/Release build.