Friday, May 26, 2006 2:13 AM
You have an ADO.NET application that updates, inserts, or deletes data from your database. No errors occur and the data changes are visible in the application, but do not persist between application runs, or if you view the database file outside of the application.
There are two common reasons why this happens:
(1) You are calling AcceptChanges on your DataRow/DataTable/DataSet before you call Update on your DataAdapter or TableAdapter. AcceptChanges will commit all changes made since the data was loaded or since AcceptChanges was last called. This means that if you had a row that was marked as Modified, it will now be Unchanged. When you call Update, it will not recognize that the row has changes that need to be submitted to the database. The same applies for inserted and deleted rows as well. For more information, see the AcceptChanges documentation: http://msdn2.microsoft.com/en-us/library/system.data.dataset.acceptchanges.aspx. This applies to all database backends and application types (ASP.NET, Windows Forms, etc).
(2) The other common problem is that in Visual Studio, there is an option to copy your database file to the output folder of your project. Usually this is done with an Access MDB file, or a SQL Server MDF file, when you add the database file to your project.
This copy options applies each time you run the application. Therefore, although the database may in fact be updated, the updates are going to the copy instead of the original file that you expect. The key to this is the “Copy to Output Directory” property on the database file in your project. This is typically set to “Copy always” by default. What this means is that every time you build, run, or debug your application, Visual Studio copies the project file over to the output directory, and then that’s what the app connects to.
In these cases, you will probably also be using a connection string that includes the "|DataDirectory|" option. For non-Web apps, this is the project output folder where your executable is created (usually bin\debug). In order to ensure that this connection string will work, Visual Studio chooses the "Copy Always" setting by default. You can keep the connection string as-is, but change the copy option to "Do not copy", and in that case, you should see your changes persist between application runs, or if you check the database outside of the app. Make sure to check the copy in the output folder. Alternatively, you can change the connection string *and* the copy property, and point to the file you actually want to connect to.
This applies to Access MDB files, as well as SQL Server MDF files, as long as you are connecting to the MDFs file at runtime by specifying the location in the connection string with the AttachDBFilename keyword. Access databases are always file-based, so there is no need for a special keyword.
There is a utility you can use to see which database file is actually being used by the application: FileMon by http://www.sysinternals.com. Start tracing before you run your application, then watch to see which file is actually accessed. Keep in mind that the trace may also include any file copies, so make sure you search for all occurrences of your filename, to be sure that all of them are using the file you expect.