How to copy or editdata from db to db with DataAdapter

December 29, 2009 in .NET | No comments

if u want to copy data from table in database with DataAdapter

Dim oleAdapt As OleDbDataAdapter 'source adapter data
Dim adap As OleDbDataAdapter 'destination adapter data
Dim bldr As OleDbCommandBuilder
Dim ds As New DataSet
Dim dsdest As New DataSet

'source data
oleAdapt = New OleDbDataAdapter("SELECT * FROM Table1", _sourceConnStr)
oleAdapt.Fill(ds)

'destination data
adap = New OleDbDataAdapter("SELECT * FROM Table2 where 0 = 1", _desConnStr)
bldr = New OleDbCommandBuilder(adap)
adap.InsertCommand = bldr.GetInsertCommand
adap.Fill(dsdest)

'loop for copy or editdata
For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
Dim r As DataRow = dsdest.Tables(0).NewRow()
For j As Integer = 0 To ds.Tables(0).Columns.Count - 1
r(j) = ds.Tables(0).Rows(i)(j)
Next
dsdest.Tables(0).Rows.Add(r)
Next

'update data to Table2
adap.Update(dsdest)

Mission Complete.

Tags: , , , , ,