How to read a database record with a DataReader and add it to a DataTable
- by Olga
Hello
I have some data in a Oracle database table(around 4 million records) which i want to transform and store in a MSSQL database using ADO.NET. So far i used (for much smaller tables) a DataAdapter to read the data out of the Oracle DataBase and add the DataTable to a DataSet for further processing.
When i tried this with my huge table, there was a outofmemory exception thrown. ( I assume this is because i cannot load the whole table into my memory) :)
Now i am looking for a good way to perform this extract/transfer/load, without storing the whole table in the memory. I would like to use a DataReader and read the single dataRecords in a DataTable. If there are about 100k rows in it, I would like to process them and clear the DataTable afterwards(to have free memory again).
Now i would like to know how to add a single datarecord as a row to a dataTable with ado.net and how to completly clear the dataTable out of memory: My code so far:
Dim dt As New DataTable
Dim count As Int32
count = 0
' reads data records from oracle database table'
While rdr.Read()
'read n records and add them to a dataTable'
While count < 10000
dt.Rows.Add(????)
count = count + 1
End While
'transform data in the dataTable, and insert it to the destination'
' flush the dataTable after insertion'
count = 0
End While
Thank you very much for your response!
