I don't have a repro for your exact scenario, but I spoke to one of the VS.NET Program Managers and he offered a potential explanation. The VS.NET team assures me that they'll discuss the behavior in an upcoming blog entry. This may be the explanation for the behavior some are seeing. I'd appreciate feedback from people on the thread on this response. Does this explain the behavior you're seeing?
If you connect to a SQL Server database file in Visual Studio.NET 2005 Server Explorer and then create your Data Source using that connection, VS.NET will display a dialog asking the following question:
"The connection you selected uses a local data file that is not in the current project. Would you like to add the file to your project and modify the connection?"
If you say "Yes", when you build the application VS.NET will copy the .mdf file into the application's output directory, where the executable file lives. The design-time connection in Server Explorer will still point to the original .mdf file rather than the copy in your output directory. This approach simplifies the process of packaging and deploying your application. However, the approach has some interesting side effects.
You can then run your code and successfully add/modify/delete rows in your code to change the contents of the .mdf file that resides in the output directory. However, if you check the contents of the .mdf file through Server Explorer, you won't see the new data because you're looking at the original .mdf file.
There's another side effect of examining the .mdf file through Server Explorer. The next time you build the application, VS.NET will copy the original .mdf file into the output directory again. If you look at the .mdf file in Solution Explorer, you'll see that it's marked as "Copy if newer". VS.NET checks timestamps in the original .mdf file to determine when to perform the copy. This way, if you modify rows or schema in the original .mdf file, those changes will be available in the .mdf file in the output directory. According to the VS.NET team, the .mdf file is marked as "Copy always" in Solution Explorer in post-Beta 2 builds.
Say that your application creates a new row and submits it to your .mdf file using a TableAdapter, a DataAdapter, an INSERT INTO query or a stored procedure call. You can execute that code, see that the return value indicates success, and even query the table to verify that the new row exists. If you check the contents of the .mdf file in Server Explorer, you won't see the new row. You'll also cause VS.NET to re-copy the .mdf file into the application's output directory, and the changes you made the last time you ran the application will be gone.
These are the behaviors associated with saying "Yes" to that earlier dialog.
I hope this information proves helpful.
David Sceppa
G: So don't answer "YES" to anything !!!!