CODEWITHSUNDEEP
mysqlvb.netwinforms
TheButterfly
TheButterfly

Reputation: 85

Update local database all modified data to server database

I'm facing a problem when I want to update data from local database to server data, replacing everything that has been modified at local database. I know it might be simple but I got no idea about this, so any help will be appreciate.

In my situation, I want to use a button to upload all modified data to the server database. Now I'm just using 2 databases at same server to do testing.

Private Sub btnUp_Click(sender As System.Object, e As System.EventArgs) Handles btnUp.Click
    localconn.ConnectionString = lctext
    serverconn.ConnectionString = sctext
    Try
        localconn.Open()
        serverconn.Open()
        Dim localcmd As New OdbcCommand("select a.acc_id as localid, a.acc_brcid, a.smartcardid, a.acc_created, a.acc_modified as localmodified, b.acd_firstname, b.acd_ic, b.acd_oldic, b.acd_race, b.acd_dob, b.acd_rescity, b.acd_resaddr1, b.acd_telmobile, b.acd_email, b.acd_telwork, b.acd_modified, b.acd_accid from nsk_account a inner join nsk_accountdetail b on a.acc_id = b.acd_accid", localconn)

        Dim servercmd As New OdbcCommand("select c.acc_id, c.acc_brcid, a.smartcardid, c.acc_created, c.acc_modified, d.acd_firstname, d.acd_ic, d.acd_oldic, d.acd_race, d.acd_dob, d.acd_rescity, d.acd_resaddr1, d.acd_telmobile, d.acd_email, d.acd_telwork, d.acd_modified, d.acd_accid from nsk_account c inner join nsk_accountdetail d on c.acc_id = d.acd_accid", serverconn)

        localcmd.CommandType = CommandType.Text
        Dim rdr As OdbcDataReader = localcmd.ExecuteReader()
        Dim thedatatable As DataTable = rdr.GetSchemaTable()

        'localcmd.Parameters.Add("@localid", OdbcType.Int, "a.acc_id")
        'localcmd.Parameters.Add("@localmodified", OdbcType.DateTime, "b.acd_modified")
        Dim localid As String
        Dim localmodi As String
        localcmd.Parameters.AddWithValue("localid", localid)
        localcmd.Parameters.AddWithValue("localmodified", localmodi)

        For Each localid In thedatatable.Rows
            Dim calldata As New OdbcCommand("SELECT acc_modified from nsk_account where acc_id ='" + localid + "'", serverconn)
            Dim reader As OdbcDataReader = calldata.ExecuteReader
            txtSDate.Text = reader("acc_modified").ToString
            If localmodi <= txtSDate.Text Then
                'do nothing, proceed to next data
            Else
                Dim ACCoverwrite As New OdbcCommand("Update nsk_account SET smartcardid = @mykad, acc_created = @created, acc_modified = @modify WHERE acc_id ='" + localid + "'", serverconn)
                Dim DEToverwrite As New OdbcCommand("Update nsk_accountdetail SET acd_firstname = @name, acd_ic = @newic, acd_oldic = @oldic, acd_race = @race, acd_dob = @dob, acd_rescity = @city, acd_resaddr1 = @address, acd_telmobile = @phone, acd_email = @email, acd_telwork = @language, acd_modified = @detmodify WHERE acd_accid ='" + localid + "'", serverconn)
                ACCoverwrite.ExecuteNonQuery()
                DEToverwrite.ExecuteNonQuery()
            End If
        Next

        MessageBox.Show("Upload success", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning)

    Catch ex As Exception
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning)
    Finally
        localconn.Close()
        serverconn.Close()
    End Try
End Sub

any comment or suggestion will be appreciate.

Upvotes: 1

Views: 1373

Answers (1)

T.S.
T.S.

Reputation: 19340

I hope you mean table by table. I didn't read your code much but you got the idea - you need 2 connections but here where there are 2 distinct ways of doing it.

Way #1 - you can use when amounts of data (how to say it better? - not huge). You can load a DataTable object with data from server and update changed records. You can use DataAdapter and issue CommitChanges - all changed/new rows will be written to server.

NOTE: you need a mechanism that will reliably able to tell which rows are new and modified on your local DB. Are you OK if your PK in local DB will be different than on the server? You need to answer these questions. May be you need a special mechanism for PK locally. For example, add rows using negative PK integers, which will tell you that these rows are new. And use "ModifiedDate", which together with PK will tell if the row needs updating.

Way #2 - use anytime, even with larger amount of data. Take a local row and examine it. If it is new - insert, if it is existing and "DateModified" changed - do update. There are variations of how to do it. You can use SQL MERGE statement, etc.

But these are two major ways - direct row insert/update and disconnected update/mass commit.

Also, you can do it in bulk, using a transaction - update some rows-commit, and start new transaction. This will help if the application being used as you updating it.

I hope these ideas help. If you do what you do, where you have 

For Each localid In thedatatable.Rows

I am not sure what localid is. It should be

' prepare command before loop
sql = "Select * From Table where ID = @1"
' you will create parameter for @1 with value coming from 
' row("ID")
Dim cmd As New .....
cmd.Parameters.Add(. . . . )
For Each row As DataRow In thedatatable.Rows

    cmd.Parameters(0).Value = row("ID") ' prepare command upfront and only change the value
    using reader as IDataReader = cmd.ExecuteReader(. .  . . )
        If Not reader.Read() Then 
            ' This row is not found in DB - do appropriate action
            Continue For
        Else
            ' here check if the date matches and issue update
            ' Better yet - fill some object 
        End if
    end using

    ' if you fill object with data from your row -here you can verify if
    ' update needed and issue it  
     . .  . . . . 

Next

Upvotes: 1

Related Questions