sql express database, physically see changes

[jamie123]

I found out recently that everytime you build your application, if you're using a sql express database, it gets rid of the recent changes or something like that? i don't know exactly what it does, but i have a sql express database and can insert rows and modify data fine, it reflects it through my applications datagrid so i know that data is actually being put in the database.

However, if i update the database with a new row, go into visual studio 2008's server browser and look at my newly updated table, it does not show the new rows that I've put in. I know the rows are there, because when I reload the application (not re-building) the new rows show up. How can I see these new rows in the server browser? or get sql express to stop getting rid of my new changes everytime it builds.

Btw, when I say it gets rid of new changes, i mean that when i was given this database to play around and code with, it had certain rows and columns, everytime i use my application I add new rows and such, when I re-build, the database goes back to the original rows and columns it was given to me with

 

[Stonkie]

It seems like a typical error. The data you see in your application is stored in a cache called the dataset. As long as you don't reload it from the database, it shows all your changes, but it is just a cache. To flush the changes to the database, you have to use your TableAdapters to update the database.

Something like :

myTableAdapter.Update(datasetSomething)

 

[JohnH]

Have you by any chance set the "Copy to Output Directory" property to "Copy always" for you db file?

 

[jamie123]

Have you by any chance set the "Copy to Output Directory" property to "Copy always" for you db file?

i have not, where would i do that, in the property pane?

And to the post above JohnH's, it is not being recorded just in the dataset, I have tableadapter.update(dataset) commands just as you specified


In msdn's forums, one of the mods told me this:

Sounds like you are using a SQL Express database file. It gets overwritten in your development environment everytime you recompile. This is normal, there are tons of threads and articles about how to handle that issue.

Because I questioned that maybe I was just saving to the dataset, and he said there was no such command and that I was saving to the database, it was just this error. Would that copy always thing work? I don't have my project with me so i'll have to wait until tomorrow to find out, have you guys ever heard of this problem before though?

 

[Stonkie]

It is a setting you put on your project files. Go in the solution explorer, right click on the database file and choose properties. It will open the properties window for the file itself with the "Copy to Ouput Directory" option.

If it is set to "Copy always", then it replaces the file from your project to your compilation directory everytime you recompile, effectively producing the behavior your get. Why didn't I think of that?

 

[jamie123]

Well, ideally that sounds like it would work, but I just opened my project to find out that it's already set to "Copy Always." So unfortunately, that didn't work. I just want to physically be able to see the changes and I know it's saving to the database, so I don't understand why I can't see these.

 

[Stonkie]

Actually, it must NOT be set to "Copy always". When you work, you work on the compilation's copy of the database file. This copy is overwritten everytime you recompile because it is set to copy always. Change that to "Copy when newer" and it will be replaced only when you modify the project's database.

To give you an idea, go in the project's directory. You should see all the files from the solution explorer window of VS. Go in "\bin\debug" (or "\bin\release") and you'll find the compilation including the compilation's database file. This is the one you connect to. The one in the project is never modified and replaces this one everytime you recompile because it is set to "Copy always".

If you set it to "Copy if newer", it will leave in "\bin\debug" only the database that has the latest modification date between that in our project directory and the one already in the "\bin\debug" directory.

 

[jamie123]

Actually, it must NOT be set to "Copy always". When you work, you work on the compilation's copy of the database file. This copy is overwritten everytime you recompile because it is set to copy always. Change that to "Copy when newer" and it will be replaced only when you modify the project's database.

To give you an idea, go in the project's directory. You should see all the files from the solution explorer window of VS. Go in "\bin\debug" (or "\bin\release") and you'll find the compilation including the compilation's database file. This is the one you connect to. The one in the project is never modified and replaces this one everytime you recompile because it is set to "Copy always".

If you set it to "Copy if newer", it will leave in "\bin\debug" only the database that has the latest modification date between that in our project directory and the one already in the "\bin\debug" directory.

Well this works to a point, I thought it did at first, everytime I recompile it saves the data just fine, as opposed to before when it would get overwritten everytime i recompiled. But, when I tried to view the data in the datasource viewer tab on the left, I hit refresh and then tried to view it and it didn't show any of the changes..and, after I hit that refresh button, when I try to debug my app the data goes back to what it was before, and gets overwritten again...so yet again, i can still not view my newest rows in the data source viewer thing on the left

 

[JohnH]

The db file in project is the template you design, usually it is empty apart from the tables/columns you have added, but it is possible to add data to it also in Designer (from Table Data view of Database Explorer). When you build/deploy the source files are compiled to the executable application assembly file and copied to output path along with the template db file and perhaps more files. It is this runtime copy that is changed when your application update db in runtime. When you go back to design it is still the same empty template db that is there. So when you have default setting "copy always" you can rest assured that each time you run debug you start with a fresh empty db. If you want to "look into" the runtime copy of the db without using your application to run queries against it you can for example use the Microsoft SQL Server Management Studio Express to attach to it and "open table" or run ad-hoc queries to see data.

 

[cjard]

jamie123, please read the DNU link in my signature. Once you understand what is going on, the behaviour you see will become very clear to you

 

[jamie123]

thank you cjard, makes a lot more sense now!