Hi all,
I am an experienced developer but a TOTAL newbie when it comes to databases. I am creating a new Visual Basic app for which I need a database and I'm using SQL Server Express with VS 2005.
The problem I'm having is that even after perusing this forum and a lot of MSDN articles, I don't understand something basic... how do I create a database and modify the tables and columns of it (the schema, I mean) while I'm developing this application, without losing all the data every time I make a change?
To clarify, I don't know the entire schema of the database yet; I'm still working it out. I want to develop some forms that display data from some of the tables that I do have, but it seems that no matter what I do, my app wants to connect to the .mdf file in the \bin\debug folder of my project, instead of the one in the project folder itself. So it seems like I have two choices:
1) I leave "Copy to Output Directory" set to "Copy always" which means the .mdf will have the updated schema, but I lose all the data I put in the database every time I run the app, which is no good.
2) I change "Copy to Output Directory" to "Do not copy" which means my data will be preserved but when I change the table schema the database in the \bin\debug folder will still have the old schema.
So what is the correct approach here? Am I supposed to use option #1 above, and then, before exiting the application I write all my data into a separate file that I can read back into the database the next time I run the app (since the old data gets nuked by the "Copy Always" option)? Or is there some way to work directly on the schema of the database with the saved data in it?
The other thing I don't understand is why does VB always look for the db in the \bin\debug folder? How are you supposed to connect to an existing database that lives somewhere else on your drives? When I first created the connection (under "Data Connections" in the Server Explorer) I got a popup window that asked if I wanted to copy the database over, so I said no. So why wouldn't it connect to the original db at the path I specified? Why is still looking for \bin\debug\mydb.mdf?
Sorry for such basic questions, but I'm really confused.
Thanks!
Phil
hi Phil,
first and always, [IMVHO]
pwinant wrote:
The problem I'm having is that even after perusing this forum and a lot of MSDN articles, I don't understand something basic... how do I create a database and modify the tables and columns of it (the schema, I mean) while I'm developing this application, without losing all the data every time I make a change?
To clarify, I don't know the entire schema of the database yet;
bad sentence, this one... this mean no design has been actually implemented... just a brute force "write through" approach...
ok.. that can be an experience as well.. and you see some of the effects when using User Instances feature ... personally I do not use that paradigm so this trouble is not included in my day to day problems list 
I'm still working it out. I want to develop some forms that display data from some of the tables that I do have, but it seems that no matter what I do, my app wants to connect to the .mdf file in the \bin\debug folder of my project, instead of the one in the project folder itself. So it seems like I have two choices: 1) I leave "Copy to Output Directory" set to "Copy always" which means the .mdf will have the updated schema, but I lose all the data I put in the database every time I run the app, which is no good.
2) I change "Copy to Output Directory" to "Do not copy" which means my data will be preserved but when I change the table schema the database in the \bin\debug folder will still have the old schema.
So what is the correct approach here? Am I supposed to use option #1 above, and then, before exiting the application I write all my data into a separate file that I can read back into the database the next time I run the app (since the old data gets nuked by the "Copy Always" option)? Or is there some way to work directly on the schema of the database with the saved data in it?
what if you copy back your actual mdf + ldf files in the project folder when you are finished executing/debugging the app, so that you can continue modifying it at design time and maintain the inserted data?
The other thing I don't understand is why does VB always look for the db in the \bin\debug folder? How are you supposed to connect to an existing database that lives somewhere else on your drives? When I first created the connection (under "Data Connections" in the Server Explorer) I got a popup window that asked if I wanted to copy the database over, so I said no. So why wouldn't it connect to the original db at the path I specified? Why is still looking for \bin\debug\mydb.mdf?
my answer is, again, not to use User Instance feature and connect to a "traditional" instance of SQL Server/SQLExpress providing traditional connection parameters... you connect that way to the instance present in your (usually) \Program Files\Microsoft SQL Server\MSSQL.x folder, and it's database are usually in the \MSSQL\Data subfolder...
so you provide InstaceName, InitialCatalog, and authentication info (trusted or standard SQL Server login's credentials) parameters like
connectionString = "Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"
or
connectionString = "Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;"
but, if you use an Express edition of Visual Studio, you loose the possibility to use the integrated designers as they only work with User Instances implementations..
you can code (manually code) your app to connect to "standard" SQL Server\SQLExpress editions, but not via the designers...
[/IMVHO]
regards
|||Hi Phil,
I am not the most experienced guy in this forum but I believe I can make a suggestion. I believe you are updating your DB schema in VS IDE. I suggest you use of SQL Management Studio Express (It's a free download from Microsoft). In object explorer window of SQL MSE you can right click on databases node and select attach option. This will allow you to select an mdf file from somewhere on your computer and attach it to your DB. I hope it helps.
FlatWhite
|||Hi Phil,
Check out my last post in this thread. In the post I discuss user instances and how they work and point to a white paper that explains them in more detail. The behavior you're seeing is the result of how VS works with files and is by design. When you include your database as part of your project, it gets moved around just like any project file; when you are debuging (F5) you are actually running your application from the .\bin\debug directly, not from your project directory, which is why you see this behavior. This behavior is required for ClickOnce to work, but is targeted as single user applications. If you're creating a multi-user application you shouldn't be using user instances, you should put your database on a central server and connect to it through tat.
There is no one right way to deal with the whole "lost data" issue caused by the VS behavior related to embedded databases. I've described a couple ways, here and here and MF mentions a variation in the post I've already linked to above. There are others as well, it's all about how you want to deploy your database.
Mike
|||Hi all,
I've received three emails now saying that different people have replied to my post, but when I click on the link in the email it says there's no such post, and I don't see anything here in the forum except my original post. Not sure why that's happening (I hope this post will show up!). Anyway, thanks to all those who are trying to answer my question. :-) Hopefully your posts will actually show up for me at some point so I can read your entire message! 
Phil
Edit: ok, now that I've replied I can see the other posts! Yay! 
hi Phil,
actually I do not see your last post as well ... does this one shows up?
let's see..
No comments:
Post a Comment