--
The following article is bogus and confusing:
How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy'
http://msdn2.microsoft.com/en-us/library/ms246989.aspx
You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.
Do not copy
The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.
You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.
--
The above article is bogus and confusing.
This is rediculous!
This is the most vague and convoluted bunch of nonsince I've ever come accross!
Getting caught out on this issue for the 10th time!
And not being able to find an exact step-by-step solution.
--
I've tried it and it doesn't work for me.
Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.
If you have a step-by-step procedure that can be reproduced this properly please PM me.
I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.
Many thanks.
This is the exact procedure I have come up with:
NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.
NOTE 2: DO NOT hand copy the database to a folder/directory in your project.
Yes, I know its hard not to do it because you want your project nice and tidy.
I just simply could NOT get it to work.
You should NOT have myData.mdf listed in the Solution Explorer. Ever.
Create a folder for your data following NOTE 2.
Copy your data to that folder. * mine was C:\mydata\myData.mdf
Create a NEW project.
Remove any Data Connections. ( no matter what)
Save it.
Data | View Data Sources
Add New Data Source
select NEW CONNECTION ( No Matter what, do it!
Select the database. * again mine was C:\mydata\myData.mdf
Answer NO to the question:
Would you like to copy the file to your project and modify the connection?
- NO ( no matter what - ANSWER NO ! - Absolutely NO )
Then select the tables you want in the DataSet.
and Finish.
To Test -
From the Solution Explorer | click the table name drop down arrow | select details
Now Drag the table name onto the form.
The form is then populated with a Navigation control
and matching Labels with corresponding Textboxes for each field in the table.
Save it.
1) Run the app.
Add one database record to the database by pressing the Add(+) icon
Just add some quick junk data that you don't mind getting lost if it doesn't save.
YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Now exit the application.
2) Run the app again.
And verify there is one record already there.
Now add a second database record to the database by pressing the Add (+) icon.
NOW add some quick junk data that you WILL intentionally loose.
*** DO NOT *** press the save icon.
Just Exit the app.
3) Again, Run the app.
Verify that the first record is still there.
Verify that the Second record is NOT there.
Its NOT there because you didn't save the data before exiting the app.
Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Also proving you must add your own code to catch the changes
and ask the user to save the data before exitiing or moving to another record.
As a side note, since vb.net uses detached datasets,
(a copy/snapshot of the dataset in memory and NOT directly linked to the database)
the dataset will reflect all changes made when moving around the detached datasets.
YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM.
Otherwise, they will simply be discarded without notice.
Whewh!
I hope this saves me some time the next time I want to start a new database project.
Oh, and uh, for anyone else reading this post.
Thanks,
Barry G. Sumpter
Currently working with:
Visual Basic 2005 Express
SQL Server 2005 Express
Developing Windows Forms with
101 Samples for Visual Basic 2005
using the DataGridView thru code
and every development wizard I can find within vb.net
unless otherwise individually stated within a thread.
Hi Barry,
What you describe will work as you suggest, but only when you are in Debug mode (F5). When you build you application into an EXE and deploy it to another machine (i.e. using ClickOnce, etc.) the database will not be included in the deployment and you're application will cause errors as it will be looking for the database in C:\Mydata that doesn't exist unless you've created it through some external process. Just realize that your solution is incomplete because it doesn't handle deployment, it only works for Debugging.
I offer two alternat solutions in the SQL Express blog, here. One is a more simple version of the KB article (it has limitations that are explained) and the second is a more involved deployment system that basically automates what you're talking about but includes a way to deploy the database when you deploy your application.
Mike
|||Hi Mike,
Thanks for the reply.
You've obviously been there and I certaily appreciate your work and posts.
And please accept my well deserved respect for same.
I look forward to reading all your posts.
I'm not sure which solutions the blog in the reply was offering / refering to.
As the "Change the Copy to output directory property" just never worked in any variation.
And has been offered as a solution on many many resources.
Hince my post.
The web cast never loads properly and closes down all my explorer windows when I force it to close.
Which I think is pretty much like a hammer with no handle.
(I can't seem to grasp it very well.)
And a hyperlink at the bottom of the page with the appropfriate title takes me to some soft of a spam web page.
Which makes me dought the reseach thread.
Regretably, I've been putting up with this type of presentation for some time now.
Where a 30 sentence subject is so buried in advertising its hard to extract the information.
At this stage I can't confirm viability of the webcase information.
But would be very very interested in viewing it in its entirity.
Is there another place where the webcast can be viewed?
I would prefer to view the video before starting my first official project using vb.net 2005 and SQL Server 2005.
To prove to myself it can be done.
The reply although well intentioned, I believe, took my post out of context.
If I might be so bold as to explain:
My post addresses the issue the subject relates to in context.
i.e. ...saving updates to DB ... (during development)
and makes no mention of deployment.
My post also has step-by-step procedures and proof of solution.
And has already been confirmed by a number of resources.
As you can read in my other posts over the last few days I've been querying the subject of the reply.
Specifically, what version of SQL should I be developing against and then deploying.
(I'm sure we know there are a meriad of SQL Versions to add to the confusion.)
Perhaps this reply would better server as a response to one of those posts.
And mayhaps was intended for same.
I haven't had the chance as yet to sort out all the details regarding deployment, again, as mentioned in my other posts.
If the oneclick method doesn't allow me to edit the properties of the database location or add additional objects then
its a short coming of MS products I'm used to.
And not an assumed shortcoming of a deployment procedure I haven't been able to considered yet.
I'll happily research the issue when discoverd.
And perhaps find an easy workaround.
And, as always, I will post my findings once solidified.
All the best.
Great work.
And thanks again,
Barry G. Sumpter
|||Hi all,
OK, Many thanks to the individual who requested to remain anonymous for arranging the video download for me.
The video answered heaps of my queries.
The video did mention that we ARE allowed to distribute SQL Server Express as long as we sign an agreement.
IS the distributable version the same one from the Express downloads?
Including the SQL Server Management Studio, etc.
Or is it a cut down version like MSDE with only a command line interace?
I'll have to reseach more on the "user instance" concepts and what it means and how to use it.
--
The DB installation technique mentioned is one we have been using in vb6 and MSAccess solutions for a number of years.
Where all new installs, updates, and major revisions are handled in scripts.
The scripts (in vb6 code) are Generated by third party tools to build the database from scratch, make updates, and convert/port the data for major revisions.
All executed thru code as mentioned. Either automated or thru an admin interface.
With a .ini file to point to the database on a file server somewhere.
Including versioned and date and time stamped backups, of course.
It's a lot of work keeping all the versions and revisions organized.
Same issues, different language, different database.
We were hoping MS had sorted this out. Oh well.
--
Does anyone know where the "Copy to Output Directory" concept came from?
Did it originate in some C version or Java or Web feature before .net?
Or is it a new concept for all in .net?
And does it actually work in non express versions?
--
All the best,
Barry G. Sumpter
|||
BarrySumpter wrote:
The video did mention that we ARE allowed to distribute SQL Server Express as long as we sign an agreement. IS the distributable version the same one from the Express downloads?
Including the SQL Server Management Studio, etc.Or is it a cut down version like MSDE with only a command line interace?
There are two "versions" of SQL Express, both of which you can distribute. We call them SQL Express and SQL Express with Advanced Services. You can see a high level break down of the differences here but the upshot is they are both identical database engines with Express Advanced offering some additional serices and the inclusion of Management Studio Express within the installer. (SSMSE is also available as a standalone installer.) Deployment of SSMSE is allowable as long as you are deploying it with SQL Express.
I'll have to reseach more on the "user instance" concepts and what it means and how to use it.
Start with the white paper
Same issues, different language, different database.
You can never get away from the classics. 
Seriously though, a database isn't a normal file that you can just replace to update it, this is true for any file that has user modifyable information. Everyone, MS included, has to cope with ways to "script" the structural update of user modifyable files without destroying the user data.
VS has recently released VS 2005 Team Systems for Database Professionals which introduces some great tools for creating, testing and version tracking of databases. It also has tools to create deployment and update scripts based on the version tracking system. If maintaining complex database across multiple versions is a significant portion of your life, I suggest you check it out.
Does anyone know where the "Copy to Output Directory" concept came from?
Did it originate in some C version or Java or Web feature before .net?
Or is it a new concept for all in .net?And does it actually work in non express versions?
No clue where the idea came from, but it functions identically in all versions of VS 2005. Actually, this is a pretty slick technology for handling the deployment of project files in VS and it works quite well for VS files. The paradigm breaks down for database files as I discussed in the webcast as database files present some specific challenges with regards to distribution. VS is working to solve this problem for their next release.
For my purposes, 'Copy if Newer' has worked well once the database is more or less complete, but as mentioned, it doesn't prevent the copying of a new database over the old 100% of the time, so there will still be cases where you'll get a copy over. There are a myriad of ways you can solve this problem, which to use is based on your specific needs. One that isn't discussed often (maybe not enough) is to just put the database in the SQL Data directory, Attach it to the server and change your connection string. This is very similar to what you're already suggest as it requires that you do not put the database into the Project. It has the same issues when it comes to deployment time if your plan is to deploy the database to every computer as a local data store. If that is the case, you can always just add the database to the project (and change your connection string again) once you are ready to deploy.
Mike
Mike
|||Hi Mike,
Thanks so much for your detailed replys and staying with this thread.
I do see the defference in the express editions now.
I will be needing client reporting on the SQL Express data.
The chart shows "Reporting Services" with Advanced Services.
I'm hoping its a proper reporting interface for building reports from sql express data.
And perhaps be able to save each report layout and execut them from a menu etc.
As a side note: As I speak with other vb6 to .net developers often, the subject of MSAccess reporting engine comes up regularly.
And how dissapointed we are in how MS hasn't persued the reporting engine as a stand alone reporting object,
as we confirm to one another that the MSAccess reporting engine is the best we've ever used.
The latest fantasy is to have the MSAccess reporting engine interface be able to access a sql server database.
Thanks so much for the hyperlink to the white paper: SQL Server 2005 Express Edition User Instances
Where I found heaps of info and an excellent definitions for:
User Instances
... A SQL Server instance is a SQL Server executable program running on a server. Each instance has a name, a sqlservr.exe process in memory, buffer memory, its own copies of the system databases, and its own set of user databases. By default, SQL Server Express installs as an instance named "SQLEXPRESS," for example. You connect to a named instance by specifying the instance name with the server name in the connection string. That is why you normally specify ".\SQLEXPRESS" as the server name when connecting to a local SQL Server Express database. The dot means the local server and \SQLEXPRESS specifies the SQLEXPRESS named instance. The SQL Server service (sqlservr.exe) runs as a Microsoft Windows service and executes in the user context of the user specified as the service account in the Windows service manager. For SQL Server Express, this account defaults to "NT AUTHORITY\NETWORK SERVICE" although a different account can be specified during setup.
SQL Server Express expands the concept of SQL Server instances by supporting user instances. A user instance is similar to a normal instance but it is created on demand while normal instances are created during setup. The service account for a user instance is the Windows user who opened the SQL Client connection to the database. In other words, if Sally opens a connection to a database file specifying the user instance option in the connection string, the user instance will have Sally as the service account.
...
Thanks for suggeting VS 2005 Team Systems for Database Professionals but I'll be looking for a no frills 3rd party tool to generate the scripts for me.
As you mentioned "Tried n True" procedures slighly modified to compensate for SQL Express idios are hard to get away from.
... "No clue where the idea came from" ...
Any C people out there? Java? Or Web? Have you seen this before?
Can anyone confrim its a "new idea" from MS?
For me I dought I'll be using 'Copy if Newer' and would like to be able to just turn it off completely.
And handle my db as dicussed.
I just don't see a time where the database is close to completion.
i.e. I exprect the database to always be in transition.
In some way or another.
I'll post my methodology once I get to it.
Maybe a class guru will be able build a souped up version everyone can use like the one in your article. And preferably in vb.net. ;)
Thanks again and all the best,
Barry G. Sumpter
|||
Here is someone who has actually gone as far as writting a Visual Studio addin to compensate for this anomoly.
It may help someone reading this post.
I HAVE NOT tested this as yet.
http://www.codeproject.com/useritems/db_manual_copy_add-in.asp
|||
Thanks for pointing out the add-in Barry, it's always nice to see the creative solutions from our customers. As I said, VS is working on this, so hopfully we'll see better behavior in the next version.
I will be needing client reporting on the SQL Express data.
The chart shows "Reporting Services" with Advanced Services.
I'm hoping its a proper reporting interface for building reports from sql express data.
And perhaps be able to save each report layout and execut them from a menu etc.
Let me give you the five minute primer on Reporting, Visual Studio Express and Reporting Services...
Reporting Services - This is a service that runs on top of IIS. The version that installs with SQL Express Advanced only supports local relational datasources. (i.e. It can only pull data from a relational database and the SQL instance has to be installed on the same computer where RS is installed.) The full version of RS that is installed with the paid Editions of SQL do not have these limitations.
I'm guessing that by "client reporting" you are looking to have reports built directly into your application. This is not what Reporting Sericies does, as I said, it's a service on IIS. The report building tools installed with the SQL Express Toolkit builds reports for the IIS based Reporting Services, not for client based reports in VS. Initially, VS Express Editions did not include any reporting fuctionality as this was considered enough of a value add that it was only release in the paid editions. VS later modified a bit by releasing a report viewer for all Express Editions and a report designer for VWD Express only. If you're goal is to create reports built into a Windows Forms application you actually need to have one of the paid editions of VS.
You're not totally out of luck if your application is going to be connected through. The report viewer that is available for all VS Express Editions can display reports that are hosted in Reporting Services as well, so you could create your reports for the RS system on IIS and then just show them in your client UI while you are connected to the network. Based on the limitations above, this will not really work for data that is stored locally in your project using a User Instance.
Mike
|||Another excellent response.
Thanks for the heads-up!
Although, I'm not ready to start in depth research on the subject,
I'm wondering if I could use the inetExplorer control to view the RS/VWD Express Reports?
Hi all,
In the same idea of using SQL Express taking it another step further and attepting to salvage some work where I used the vb.net wizards...
Subject: Is there an easy way to convert vb.net drag-n-drip wizard created dataset .xsd location to all code?
Desc: I need to convert fully funcitonal detailed forms SQL Server and DBName to code retrieval only.
As the subject and Description reads, I need to update my forms to programatically open a .ini file and get the Server and DBName and use them to point to the correct SQL Server Server and SQL Server Database Name.
Any hints to standardize this would be greatly appreciated.
I'm just a bit lost at the moment trying to sort all this out before continuing my first official assignment.
app.config
AttachDbFilename= is a place I can find that has the database
[code]
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="myApp.My.MySettings.myAppDataConnectionString"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename='C:\myProjects\MyAppData\MyData.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True"
providerName="System.Data.SqlClient" />
....
[/code]
and Settings.settings
again the two AttachDbFilename= is a place I can find that has the database
[Code]
<?xml version='1.0' encoding='utf-8'?>
<SettingsFile xmlns="http://schemas.microsoft.com/VisualStudio/2004/01/settings" CurrentProfile="(Default)" GeneratedClassNamespace="My" GeneratedClassName="MySettings" UseMySettingsClassName="true">
<Profiles />
<Settings>
<Setting Name="MyDataConnectionString" Type="(Connection string)" Scope="Application">
<DesignTimeValue Profile="(Default)"><?xml version="1.0" encoding="utf-16"?>
<SerializableConnectionString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<ConnectionString>Data Source=.\SQLEXPRESS;AttachDbFilename="C:\myData\myData.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True</ConnectionString>
<ProviderName>System.Data.SqlClient</ProviderName>
</SerializableConnectionString></DesignTimeValue>
<Value Profile="(Default)">Data Source=.\SQLEXPRESS;AttachDbFilename="C:\myData\myData.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True</Value>
</Setting>
</Settings>
</SettingsFile>
[/code]
Is there a way to override or replace these settings with one from a .ini file?
Ultimatly, my fantasy, is to be able to take full advantage of the drag-n-drop wizards so I can setup a master/detail form with database connections etc.
i.e. very quickly.
Then, hopefully, be able to change the code slightly, to point to a SQL express server and DBName retrieved from a .ini file.
I may be getting the new .net technology mixed up with the old. I hope I'm getting the jist accross.
|||Maybe I could default the installation to the app directory?
And maybe to setting.settings and app.config script have a app.path variable I could use?
Also, ClickOnce Patterns and Practices Whitepaper Download
a 66 page document from an IDesign Newsletter.
|||
Hi Barry,
It depends on what you mean when you say RS/VWD Express Reports. The thing is, there are two different report formats, one that is hosted on Reporting Services, the other is the client report that you can create using the VS Designer. I don't know what the specific extentions are.
If you're refering to the reports that are hosted by Reporting Services, I'm guessing you could view them using the InetExplorer control, but I'm not sure that makes sence given that you can also view them using the ReportViewer control which is available for Windows Forms apps, even the ones you make in VS Express. Download it from here. (Should have given you that link before, but I got lazy. 
)
If you're talking about the reports created by the VS designer, I doubt those can be viewed in the inetExplorer control.
One idea I've been wondering about, but haven't had a chance to try myself is whether you can use the Report Designer addin for VWD Express (available here) to design reports and then copy those reports into your VB or C# projects. It's a bit of a round about solution, but if it get you there...
Mike
|||Hi Mike,
Thanks for another detailed reply.
Yes, I'm getting just a bit too far ahead of myself at this point but will Favorite this thread in my SQL Reporting section.
Also, my solution for my database location issues:
Jeez! Took far far too long to nut this out myself!
Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;
At runtime, for me
DataDirectory is
C:\myVB2005ExpressProjects\MyProjectFolder\myProject\bin\Debug
I've confirmed this by placing my database there.
I'm NOT sure how this works with the "Copy to Output Directory" settings as discussed in this original post...
--
Running the Compiled executable:
Confirmed using Data sub directory:
MyProject | Settings | Type: (Connection Sting) |
Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\data\myData.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True
Build
Copy the .exe from ...bin\Release to C:\xxx
Copy myData.mdf and MyData_log.ldf to C:\xxx\Data\
NOTE: Data Subdirectory needed with this example.
Run the executable and test the database connection.
Works for me!
No convoluted class this or collection that.
Again, I'm NOT sure how this works with the "Copy to Output Directory" settings as discussed in this original post...
Well for crying out loud! and ROFL!
That DataDirectory directive is what vb.net express defaults too when using the dataconnection wizards!
Specifically, when answering Yes to the Question:
Would you like to copy the file to your project and modify the connection?
Which is where all this started in the first place.
Now that I've gone full circle this is what I've found:
I created a new project and ran thru the connection wizard to setup the dataset to point to my database
and included ALL tables.
I then dragged the DataSource to the form to get a grid.
The solution explorer then reflects the database .mdf file.
I view the properties for the .mdf file and set the "Copy to output directory" to "never copy"
Then run the app and get an error because the runtime can't find the database in the bin\debug\data folder
I then view the properties for the .mdf file again and set the "Copy to output directory" to "always copy"
Then run the app and make changes and find the changes are not saved by moving next and previous.
I then add an "Update" button and add the following to its _click event:
Me.Validate()
Me.TblBindingSource.EndEdit()
Me.TblTableAdapter.Update(Me.myDataSet.myTable)
(slightly changed here for context)
Then run the app and make changes and click the update button and find the changes are now saved correctly by moving next and previous.
I then exit the app and nrun the app and find the data has returned to its original state. i.e. no updates
(I believe its because I have set the "Copy to output directory" to "always copy".
Now that the "Copy to output directory" to "always copy" has copied the database to the bin\debug\data folder,
I now set the "Copy to output directory" to "Never Copy".
I then Run the app, make the changes, save the changes, verify changes, exit the app, run the app, and verify the changes are correct. Noteing that the database was not copied to the output directory.
I believe "Copy to output directory" should be "Replace db in debug folder" (always, never, when newer).
And should be defaulted to "Never".
Perhaps the combination of having to code the Me.TblTableAdapter.Update along with the "always copy"/"Always replace db" setting
AND receiving numerous IDE Errors is what made me doubt this functionality in the first place.
I DO NOT know how this effects Database updates after I've setup my datasets (.xsd).
I do know that Database updates ARE NOT reflected in the dataset .xsd files automatically.
In the past I had to also update the dataset .xsd files by hand.
Which makes multiple changes to the database at one time almost impossible.
If I had built the Dataset using the wizard (as before by selecting all tables) I could just rebuild it.
But found that I loose whatever additional queries I've added to the Dataset.
hmmm, following on, I'm now thinking the lost queries should have been created as stored procedures.
Then selected the tables and the stored procedures when rebuilding the dataset.
I must also mention that I did attempt to add a few querys to the new dataset in the new project
and received multiple vb.net ide errors but vb.net did NOT crash out as I recall previously.
Perhaps the Service Packs have corrected some annomoly since then.
Whew!
|||
Hello Barry,
Choosing the *.mdf in the Solution explorer and setting it's Properties >Advanced>Copy to Output Directory: to Copy if newer will solve the problem for the Tabor VB09 example.
Cheers,
No comments:
Post a Comment