How to read a database record with a DataReader and add it to a DataTable

Question

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!

See stackoverflow.com/questions/10844358/…

nawfal
– nawfal

2013-02-11 07:01:42 +00:00
Commented Feb 11, 2013 at 7:01 — nawfal
– nawfal, Commented Feb 11, 2013 at 7:01

John B · Accepted Answer · 2010-03-22 15:10:31Z

1

You could use your original method, but do it in batches using limit and skip in the select statement. So you would just do 100,000 rows at a time and loop until you get all the data.

That way you wouldn't have to change your code too much

answered Mar 22, 2010 at 15:10

John B♦

20.5k35 gold badges125 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Olga Over a year ago

thank you. could you provide a link or a code example to show me how i could do this?

Collectives™ on Stack Overflow

How to read a database record with a DataReader and add it to a DataTable

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related