how to update empty datatable by the data from another datatable

user_13174744 0
Viewed 100

I have 2 servers that contain the same databases with the same structure (Main Server + Sub Server).

  1. I deleted all data from the table in the sub server.
  2. I got the data from the table from the main server and put it in Datatable.
  3. I copied the data from the Datatable of the server2.
  4. I use Dataadapter2 to update the table in the sub server.

but still, the sub server is empty?!

'Delete Data From The Sub Server
    xSlConn.Open()
    Dim xcmddelete As New MySqlCommand("Delete FROM tblplaces", xSlConn)
    xcmddelete.ExecuteNonQuery()


    'Get Data Drom The Main Server
    Conn.Open()
    Dim xcmd1 As New MySqlCommand("SELECT * FROM tblplaces", Conn)
    Dim xdt1 As New DataTable
    Dim xda1 As New MySqlDataAdapter(xcmd1)
    xda1.Fill(xdt1)



    'Preparing the datatable for the Sub Server
    Dim xcmd As New MySqlCommand("SELECT * FROM tblplaces", xSlConn)
    Dim xdt2 As New DataTable
    Dim xda2 As New MySqlCommand(xcmd)
    Dim xB As New MySqlCommandBuilder(xda2)
    xda2.Fill(xdt2)

    'Copt the data from the main datatable to the sub datatable
    xdt2 = xdt1.Copy()

    'Update The Datatable2
    xB.GetUpdateCommand()
    xda2.Update(xdt2)
vb.net mysql
2 Answers

xda2.Fill(xdt2)

This line of code is useless; the next line of code tosses out xdt2 anyway

xdt2 = xdt1.Copy()

This line duplicates the datatable. It won't set all the rows in xdt2 to be RowState of Added, which is what the datadapter needs them to be in order to run an insert on them

A dataadapter inspects each row's RowState. Unchanged rows are ignored. Added have the INSERT command run on them, Modified maps to UPDATE, Deleted maps to the DELETE query

Right now all the rows in your xdt1 are Unchanged because the dataadapter called AcceptChanges on them after it added them to the table

Now.. you don't need to copy the data at all; you can just set all the rows in xdt1 to be Added so the xdt2 adapter will use its INSERT command on them. Also, it should be possible to just swap the connection out on the insertcommand

'Get Data Drom The Main Server
Dim dt As New DataTable
Dim da As New MySqlDataAdapter("SELECT * FROM tblplaces", Conn)
da.Fill(dt)
Dim xB As New MySqlCommandBuilder(da)
da.InsertCommand.Connection = xSlConn

For Each ro as DataRow in xdt1.Rows
  ro.SetAdded()
Next ro
da.Update(dt)

Or, you can simply request that the xda1 adapter NOT call AcceptChanges on them at all, so they will be in Added state already:

'Get Data Drom The Main Server
Dim dt As New DataTable
Dim da As New MySqlDataAdapter("SELECT * FROM tblplaces", Conn)
da.AcceptChangesDuringFill = False
da.Fill(dt)
Dim xB As New MySqlCommandBuilder(da)
da.InsertCommand.Connection = xSlConn
da.Update(dt)

Or if you're pushing to a different db

'Get Data Drom The Main Server
Dim dt As New DataTable
Dim da As New MySqlDataAdapter("SELECT * FROM tblplaces", Conn)
da.AcceptChangesDuringFill = False
da.Fill(dt)

Dim da2 as New SqliteDataAdapter("SELECT * FROM tblplaces", "put a connection string here")
Dim xB As New SqliteCommandBuilder(da2)
da2.Update(dt)

Note; this latter example uses eg SQLite as a demo of declaring two different adapters of different types for different db - it doesn't guarantee that eg the specific SQLite library you're using actually contains a dataadapter implementation (some don't)

user_14106640

First, you need to use Using...End Using blocks to make sure you database objects are disposed. Don't declare database connections and commands outside of the method where they are used.

Your main problem is the Fill method of the DataAdapter sets all rows to unchanged. I changed this to the DataTable.Load method which takes a parameter of LoadOption. This will preserve the RowState Added. Then when you call Update on the DataAdapter the row will be recognized. If the row is Unchanged the DataAdapter will not update the database.

Private ConStrMain As String = "Your Main server connection string"
Private ConStrSub As String = "Your Sub server connection string"


Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    'Delete Data From The Sub Server
    Using conSub As New MySqlConnection(ConStrSub),
            cmd As New MySqlCommand("Delete FROM tblplaces", conSub)
        conSub.Open()
        cmd.ExecuteNonQuery()
    End Using
    'Retrieve data from Main server
    Dim dt As New DataTable
    Using conMain As New MySqlConnection(ConStrMain),
            cmd As New MySqlCommand("SELECT * FROM tblplaces", conMain)
        conMain.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader, LoadOption.Upsert)
        End Using
    End Using
    'Insert data from Main to Sub server
    Using conSub As New MySqlConnection(ConStrSub),
            da As New MySqlDataAdapter("SELECT * FROM tblplaces", conSub)
        Dim sb As New MySqlCommandBuilder(da)
        da.Update(dt)
    End Using
End Sub
user_83676260
Related