we're using ZyWall to guard our servers from external intrusions. It generates daily log files with huge sizes, over a GB, sometimes 2 GBs. They ususally contain more than 10 millions of lines. Now my task is to write an application that will import these lines into Oracle database. I'm writing it in C#. What I'm currently doing is:
I read the logfiles line by line. I do not load the whole file at once:
using(StreamReader reader=new StreamReader("C:\ZyWall.log")) { while ((line=reader.ReadLine())!=null) ...... }
Every line read I split the line into parts according to the commas in it.
string[] lines = line.Split(new Char[] { ',' }, 10);
Then I iterate through the lines array, create a new Row for a predefined DataTable object and assign array values to the columns in the row. Then I add the row to the datatable.
After all the lines are read to the datatable I use OracleBulkCopy to write the data in it to a physical table in the database with the same structure. But the thing is I get SystemOutOfMemoryException as I add the lines to the Datatable object, that is the 3rd step. If I comment out the 3rd step then in the task manager I see that the application consumes the stable amount of memory which is something like 17000 K but if I uncomment that step the memory usage grows unless there's no enough memory to allocate. Is there still a way I can use BulkCopy to perform this or will I have to do it manually? I used BulkCopy becasue it's way faster than inserting lines one by one.