Avatar of lostinthegame
lostinthegame
 asked on

Clickonce Update sqlce database and re-populate data

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.
Visual Basic.NETInstallationProgramming

Avatar of undefined
Last Comment
Jacques Bourgeois (James Burger)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Vadim Rapp

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Jacques Bourgeois (James Burger)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Nasir Razzaq

lostinthegame

ASKER
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.
Jacques Bourgeois (James Burger)

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Vadim Rapp

@JamesBurger, it's so nicely put, I suggest you publish this single comment as a whole separate  article, it deserves it.
Jacques Bourgeois (James Burger)