1

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.

Improve this question

1 Answer 1

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

to be more specific, it is tables to tables (for example, username to username, password to password) i understand by using 2 connections but i never tried before so I'm stuck at For Each process to read all the primary key in order to update my data
@TheButterfly I see no issues to open 2, 4, 8 connections. The problem that I see in your app is, that you will repeat this code for each table, and generally code too much. One of the ways how to avoid this is to use DataAdapter and CommandBuilder. You give the Select and it prepares Update/Delete/Insert, or you can prepare custom commands for adapter. Based on schema. But one thing you can't avoid- writing logic, how you determine what to update. Do you mark your local rows as deleted? You may need some Unique business key to identify rows. This is more than For-loop here.
in my mind the flow of the upload will be 1.) Compare modified date, if the local database has newer modified date then proceed to update 2.) the data will remain in local database without delete. all based on modified date
FYI, this system is to register personal details into member card, for new records will actually store into server first and download to local, therefore for local only needs to handle update since all the ID are match with server
@TheButterfly Than what is your problem. You have Id and Mod Date (if you had another local marker that it was in fact updated, whould be easier) With no marker - For each - select from server (load data table (1 record)) - check date. If no match - update that single row, issue commit changes, clear table, next row....
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.