Link to home
Start Free TrialLog in
Avatar of lostinthegame

asked on

Clickonce Update sqlce database and re-populate data, 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.
Avatar of Vadim Rapp
Vadim Rapp
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lostinthegame


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


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)

    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

            '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")
                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
        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
            dbversion = 0
            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;"
            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)
            Next i

            'close connection

            '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

    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.
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.