Introduction
I recently had to copy large data from one dataset to another.
To do so, I decided to use DataTable objects. If you go through the rows of a DataTable and direct copy rows from one DataTable, you will get an exception saying "Row is already being used by another table".
The simplest way is to clone an existing DataTable, loop through all rows of source DataTable and copy data from column by column and add row to the destination DataTable. The following code does the same:
For Each dr As DataRow In sourceTable.Rows
r = destinationTable.NewRow
r("Name") = dr("Name")
r("City") = dr("City")
r("Cost") = dr("Cost")
destinationTable.Rows.Add(r)
Next
DataTable.ImportRow Method
The second method is using DataTable.ImportRow method. The ImportRow method of DataTable copies a row into a DataTable with all of the properties and data of the row. It actually calls NewRow method on destination DataTable with current table schema and sets DataRowState to Added. The following code does the same as we did in the previous code snippet.
For Each dr As DataRow In sourceTable.Rows
destinationTable.ImportRow(dr)
Next
Performance Check
Here are the numbers for both methods:
Number of redords Time in Milliseconds
Direct Copy DataTable.ImportRow
1,000 9.8 9.8
10,000 895.78 420.56
100,000 7089.34 3409.42
When I tried to copy over 10 million rows, DataTable.ImportRow method took 56 seconds but I got memory exception using direct copy method.
Summary
When it comes to copying bulk of data from one DataTable to another, DataTable.ImportRow method comes handy. This article compares both direct copy and ImportRow method and we saw ImportRow method may reduce the processing time when dealing with large data.
|
| Author: Gaurav 27 Apr 2007 | Member Level: Bronze Points : 0 |
I was really looking for the same method!
|