"-Lost" <ma************ ****@techie.com wrote in message
news:Xn******** *************** *@216.196.97.13 6...
Response to "RobinS" <ro****@imnotte lling.com>:
<snip>
>If you need to retain the database between updates, post
back and I will give more info.
I realize this thread is not my own, but I'd definitely
appreciate
you elaborating further.
I am currently researching the best methods for deployment
of one of
my applications. My thought was that adding them as
resources might
prove easier or more manageable at least.
One of the main problems I ran across was the file
disappearing or
my embedded projects* not building anew.
* Whatever the proper terminology is for a single solution
containing other solutions ("Add Project").
Thanks!
--
-Lost
Here is my experience with deploying data files... A lot of this information
is from Brian Noyes's book on ClickOnce, which is excellent. I have an
alternative method at the end that we use for our homespun configuration
files as well, and will probably use for SQLCE when we get to that point.
This is Brian Noyes' book on ClickOnce Deployment:
http://www.amazon.com/Smart-Client-D...6469474&sr=8-1
Deploy the SQL database with the application as a data file. It goes into
the folder defined by ApplicationDepl oyment.DataDire ctory, or
Application.Use rAppDataPath. (Assuming you have it in the top level of your
project, otw it puts it in a relative folder under that base path).
The database is only placed there when actually deployed, so when you test
out of VS, you have to set the "copy to output directory" to "copy always"
or "copy if newer", so the files will be copied to the same (relative)
folder location under the build output folder for your project.
Then you have to add code that checks to see if you're running the deployed
version (ApplicationDep loyment.IsNetwo rkDeployed = true), and if not, adjust
the path programmaticall y for your debugging (it will be under the
Application.Exe cutablePath + relative-folder-if-you-have-one).
string dataPath;
if (ApplicationDep loyment.IsNetwo rkDeployed)
dataPath = ApplicationDepl oyment.CurrentD eployment.DataD irectory;
else
dataPath = System.Windows. Forms.Applicati on.StartupPath;
dataPath = System.IO.Path. Combine(dataPat h, "yourdatabasena me.sdf");
When you deploy your app, it deploys the database in the DataDirectory. When
you publish an update to the application, it copies the database forward to
the folder for the new version of the application, assuming nothing about
the server-side database has changed in any way.
But if you change the database, that's where it gets complicated. It will
copy the new database from the server to the new version's DataDirectory
folder.
It will copy the old database to a .\pre subfolder under the DataDirectory
folder for the new version.
Then you have to write code to migrate the old data to the new database. If
you do nothing, it will ignore the previous version's database and use the
new database.
The general process to migrate the data is to run a SQL script or a set of
SQL queries to retrieve the data from the old database and insert the data
into the appropriate tables in the new database. You might write this code
as part of a separate migration utility that is invoked when the new version
gets deployed.
If you look at the code from Brian Noyes' book (available at
http://www.softinsight.com/clickoncebook), there is a sample of this called
SQLCompactScrip tedMigration. It includes v1.0.0.0 and 2.0.0.0 of the same
application, but 2.0.0.0 contains the code in the main form's constructor to
migrate the data. The difference between 1.0 and 2.0 in his case is that a
new table was added to the database with a fk relation to the old table.
Here's the example from v2.0.
public Form1()
{
InitializeCompo nent();
if (ApplicationDep loyment.IsNetwo rkDeployed)
{
if (ApplicationDep loyment.Current Deployment.IsFi rstRun)
{
MigrateData();
}
}
}
// This method takes advantage of the fact that for this simple
example, the typed data set
// Customers table and table adapter is compatible with the new schema
private void MigrateData()
{
string preFile =
Path.Combine(Ap plicationDeploy ment.CurrentDep loyment.DataDir ectory,@".\.pre \Northwind.sdf" );
if (!File.Exists(p reFile)) // nothing to migrate
return;
// Get a connection to the old data in the \.pre folder
string oldDataConnecti onString = @"Data
Source=|DataDir ectory|\.pre\No rthwind.sdf";
SqlCeConnection oldConnection = new
SqlCeConnection (oldDataConnect ionString);
// Get a connection to the new data
string newDataConnecti onString = @"Data
Source=|DataDir ectory|\Northwi nd.sdf";
SqlCeConnection newConnection = new
SqlCeConnection (newDataConnect ionString);
// Fill a dataset with the migration data
NorthwindDataSe t oldData = new NorthwindDataSe t();
CustomersTableA dapter oldAdapter = new CustomersTableA dapter();
oldAdapter.Conn ection = oldConnection;
oldAdapter.Fill (oldData.Custom ers);
// Create a table adapter for the new database and a compatible
data set
CustomersTableA dapter newAdapter = new CustomersTableA dapter();
newAdapter.Conn ection = newConnection;
NorthwindDataSe t newData = new NorthwindDataSe t();
// Fill the new data set with default data
newAdapter.Fill (newData.Custom ers);
// Merge the old data into the new schema
// Assumes compatible schemas - this is the hard part for
// Real apps with significant schema changes
foreach (DataRow row in newData.Custome rs)
{
row.Delete();
}
newData.Merge(o ldData);
newAdapter.Upda te(newData);
}
About connection strings: If you include the |DataDirectory| placeholder in
your connection string, the path will be filled in automatically by the
runtime. For example, for Northwind, it would be
Data Source = "|DataDirectory |\Northwind.sdf ".
Be sure your database is added to the project as a file with a Build Action
of "content", and marked as a Data File in the Application Files dialog.
Another problem to be aware of is that the migration of a data file is
triggered by *any* change to the data file in a newly published version of
your application. If you connect to your SQLServer Express or SQLCE database
in VS just to check the schema or contents of the default values, you will
have modified the date timestamp of the file, so the next time you publish
your application it will be treated as a new version of the database, and
you need to have your data migration code in place to handle that.
Basically, if you never change your database structure, ClickOnce will
handle the data migration for you by copying the database forward. OTW you
have to handle it yourself in code.
**AN ALTERNATIVE**
For databases that are just files, like Access databases or SQLCE, you can
do the following:
1) Include the database in the main project. In the ApplicationFile s dialog
in the Publish tab, set it to Include or Include(Require d) instead of
Include(Data).
2) In the application's startup, check to see if the folder you're going to
put the database in exists, and if there's already a database there. If
there's not, create the folder and copy the database over there.
In Vista, you can only write to MyDocuments and the user's profile. I
wouldn't put it in MyDocuments -- that's just asking for trouble. I put my
files in LocalApplicatio nData, as recommended for Windows Vista.
userFilePath =
Environment.Get FolderPath(Envi ronment.Special Folder.LocalApp licationData);
string userFilesGoHere = Path.Combine(us erFilePath, "ourCompanyName ");
string ourDatabase = Path.Combine(us erFilesGoHere, "myDB.mdb") ;
string whereTheDBStart s =
Path.Combine(Sy stem.Windows.Fo rms.Application .StartupPath, "myDB.mdb") ;
So in your startup, you can check for it, and copy it over if it's not
there:
if (!Directory.Exi sts(userFilesGo Here))
{
Directory.Creat eDirectory(user FilesGoHere);
File.Copy(where TheDBStarts, ourDatabase);
}
else if (!File.Exists(o urDatabase))
File.Copy(where TheDBStarts, ourDatabase);
And then just set the connection string to point at the location defined by
ourDatabase.
So the first time the user runs the application, it will set up the
directory and copy the database over there. On subsequent updates or
installations, it will just use the database that is already there.
The advantage of this is you don't have to deal with migration if you just
open the database and close it. You just have to handle it when you know
something has changed.
Also, if the user deinstalls your application and reinstalls it, the data is
left behind. This is bad (the data is left behind and not cleaned up) and
good (the user reinstalls the application and doesn't lose his data).
And one last extra helpful comment about SQLCE: If you're deploying this,
you can include the dll's as local references instead of pushing SLQCE as a
prerequisite. This is way cool, because it removes one step from the
installation process.
To do this, you add the dll's to your project and set "copy local" to true
and include them in the deployment. Then you can remove the prerequisite.
Here are the dll's you need to add (version may change):
sqlceca30.dll
sqlcecompact30. dll
sqlceer30en.dll
sqlceme30.dll
sqlceoledb30.dl l
sqlceqp30.dll
sqlcese30.dll
I hope this helps someone. This is a frequently asked question in the
ClickOnce forum, so hopefully it will also help someone here.
RobinS.
GoldMail.com
