Clickonce Update sqlce database and re-populate data

lostinthegame
lostinthegame used Ask the Experts™
on
vb.net, visual studio 2010, sqlce 3.5 database

I have a project that uses clickonce to deploy automatic updates.  Clickonce overwrites the user's current database every time I publish an update, which I can get around by excluding the database from my project when I publish the update.  My issue is that I now need to adjust the schema of the database, for example, add a new column to an existing table.

If I publish the update with the database, the user loses all of their existing data.  I'm not sure how to get around this.  So far, I have set the program up to create a backup of the database everytime the program opens.  The backup is located in an area that will not be unistalled or overwritten by the update, but I don't now how to pull the data from the backup into the new blank database that has the updated schema.

Does anyone have any ideas or a different way to handle this issue?  The end result needs to be that if I publish an update with an updated database schema, the user does not lose their data either by altering their existing database columns or re-populating the data from their database backup into the new database.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The usual practice is this:

during the initial installation, the installation finds out whether there's already an existing database. This is usually being done by asking the user about the location of the database and checking whether it is already there, or has to be created. You can't rely on whether the application is already installed, since the user might have uninstallted the previous version, but kept the database and would prefer to use it with newly-installed database.

If the database is already there, then you never overwrite it. All the upgrades of the schema should be handled only by running the scripts against the existing user database. For example, if you add new column, you run the script with alter table <table-name> add .... Keep in mind that the user might have modified your database (probably subject to the license), but  you shouldn't override even that.
Top Expert 2015
Commented:
I also call ALTER TABLE, CREATE TABLE or similar SQL commands when I need to update a database, and not only with ClickOnce. But I do it in the installation itself. I also use the same technique when I need to update a file to a new format.

Each databale (or file) I use in my applications has a version number, usually recorded in a field in a table called Config (that can also contains values that could change with time, such as tax rates, my company phone number, etc.). When the application starts it calls a method that check that version number against the current one, defined in a constant in the application. If the version number in the application does not match the one in the database, then I trigger the database updates.

I prefer that method to something that happens during installation, because it takes care of possible restores of the database. If you perform the database update when installing, and the user brings back an older version of the database later, the application and the database are not synchronized. Doing the job in the application itself solves that problem.
Most Valuable Expert 2012
Top Expert 2014

Commented:
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Got it....kindof rough, but here's what I have:

Imports:

Imports System.IO
Imports System.Data.SqlServerCe
Imports System.Deployment.Application

Open in new window


Then, either create a copy of the updated database if the database does not exist on the user's computer or use the existing copy if it does. I then set the datadirectory so that it points to the new location.  This will prevent the clickonce update from overwriting the existing database (also maintains the database even if the software is uninstalled)

'CREATE COPY OF THE NEW DATABASE OR JUST POINT DATADIRECTORY TO THE EXISTING LOCATION
    Sub databasebackup()

        Dim destfilepath As String
        Dim sourcefilepath As String
        Dim userfilepath As String
        Dim localappdata As String

        'save local folder location for the new database to be placed
        localappdata = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)
        userfilepath = Path.Combine(localappdata, "SimpleMortgagePro")

        'test if the folder exists, if not then create it and copy the database
        If (Not Directory.Exists(userfilepath)) Then
            Directory.CreateDirectory(userfilepath)

            'make sure the software is deployed
            If (System.Deployment.Application.ApplicationDeployment.IsNetworkDeployed) Then
                Dim assemblyInfo As System.Reflection.Assembly = System.Reflection.Assembly.GetExecutingAssembly()
                sourcefilepath = Path.Combine(ApplicationDeployment.CurrentDeployment.DataDirectory, "PlannerFiles.sdf")
                destfilepath = Path.Combine(userfilepath, "PlannerFiles.sdf")
            Else
                destfilepath = Path.Combine(userfilepath, "PlannerFiles.sdf")
                sourcefilepath = Path.Combine(System.Windows.Forms.Application.StartupPath, "PlannerFiles.sdf")
            End If

            'copy the database from the project folder to the new location
            File.Copy(sourcefilepath, destfilepath)

        End If

        'update the data directory to connect to the new database location
        AppDomain.CurrentDomain.SetData("DataDirectory", userfilepath)

    End Sub

Open in new window


Then test the database version number to see if the current database is lower than the newest version.  If so, update the schema.

Sub runDBUpdate()

        Dim dbversion As Integer
        Dim dbnewversion As Integer = 1

        'if the dataset does not contain the config table, then add it
        If Main.PlannerFilesDataSet.Tables.Contains("Config") = False Then
            Main.PlannerFilesDataSet.Tables.Add("Config")
            Main.PlannerFilesDataSet.Config.Columns.Add("DBVersion")
        End If

        'if the config file has no rows, then add a row, otherwise, use the version in the db
        If Main.PlannerFilesDataSet.Config.Rows.Count = 0 Then
            Main.PlannerFilesDataSet.Config.Rows.Add()
            dbversion = 0
        Else
            dbversion = Convert.ToDouble(Main.PlannerFilesDataSet.Config.Rows(0).Item("DBVersion"))
        End If

        'If database version is less than version 2
        If dbversion < 2 Then

            'open connection to local database
            Dim myConnection As New SqlServerCe.SqlCeConnection
            myConnection.ConnectionString = "Data Source=|DataDirectory|\PlannerFiles.sdf;"
            myConnection.Open()
            Dim cmd As System.Data.SqlServerCe.SqlCeCommand

            Dim add(14) As String

            'create new columns
            '   document checklist
            add(0) = "ALTER TABLE Client ADD Doc1 nvarchar(250) NULL"
            add(1) = "ALTER TABLE Client ADD Doc2 nvarchar(250) NULL"
            add(2) = "ALTER TABLE Client ADD Doc3 nvarchar(250) NULL"
            add(3) = "ALTER TABLE Client ADD Doc4 nvarchar(250) NULL"
            add(4) = "ALTER TABLE Client ADD Doc5 nvarchar(250) NULL"
            add(5) = "ALTER TABLE Client ADD Doc6 nvarchar(250) NULL"
            add(6) = "ALTER TABLE Client ADD Doc7 nvarchar(250) NULL"
            add(7) = "ALTER TABLE Client ADD Doc7 nvarchar(250) NULL"
            '   dates
            add(8) = "ALTER TABLE Client ADD DocDueDate nvarchar(250) NULL"
            add(9) = "ALTER TABLE Client ADD ApDate nvarchar(250) NULL"
            add(10) = "ALTER TABLE Client ADD ClDate nvarchar(250) NULL"
            add(11) = "ALTER TABLE Client ADD LExp nvarchar(250) NULL"
            add(12) = "ALTER TABLE Client ADD FPDate nvarchar(250) NULL"
            '   create new table for config file
            add(13) = "CREATE TABLE Config"
            add(14) = "ALTER TABLE Config ADD DBVersion nvarchar(250) NULL"

            On Error Resume Next

            'add columns to database
            Dim i As Integer = 0
            For i = 0 To 14
                cmd = New SqlServerCe.SqlCeCommand(add(i), myConnection)
                cmd.ExecuteNonQuery()
            Next i

            'close connection
            myConnection.Close()

            'after updates complete, set the new database version to version 2
            dbnewversion = 2

        End If

        'update the database value to reflect database version 2
        Main.PlannerFilesDataSet.Config.Rows(0).Item("DBVersion") = dbnewversion
        Main.ConfigTableAdapter1.Update(Main.PlannerFilesDataSet.Config)

    End Sub

Open in new window


I run the code on my form load event, and seems to work so far.  The existing database is copied to a save location, the columns are added, and they remain even if the program is updated or uninstalled and re-installed.

When I release another update, I just plan to add something like:

If dbversion < 3 Then
'some code
dbnewversion =3
end if

Open in new window


If anyone has any additional issues that they can see, please let me know.

Thank you guys for the help, this has really been bothering me for while.
Top Expert 2015

Commented:
Basically sound, but a very standard "mistake" here. Unfortunately, I am in a rush right now and do not have time to give you sample code, only the technique.

A DataSet is useful only when you need to link many tables with relations. When you work with only one table, a DataSet adds extra useless overhead. A DataTable is sufficient.

A DataTable is useful only when you need to get many rows. If you need only one row, a DataReader is sufficient.

A DataReader is useful only when you need to get a complete row. In your case, you need only to retrieve one value, the version number. This can be done by using only a Command object and calling its ExecuteScalar method.

I hope this is clear enough to lead to to a more efficient way to retrieve a single piece of information from the database. If not, ask for more details and I might be able to come back with some sample code tomorrow.
@JamesBurger, it's so nicely put, I suggest you publish this single comment as a whole separate  article, it deserves it.
Top Expert 2015

Commented:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial