So I'm using C# and Visual Studio 2013. I currently have an importer that uses System.Data.OleDb to connect to MS Access databases and System.Data.SqlClient for the Sql Server connection.
What I originally was doing was I would read data from MS Access and store tables in a DataTable and store the data in the DataTable into SQL Server. It was doing well until I finally hit a table with some 30 odd columns and almost 1 million rows and I got an OutOfMemoryException.
So now I'm attempting to think of a work around. I am thinking of setting a row count check on an MS Access table before I attempt to load into a DataTable and if it is a certain number of rows or higher I plan to attempt to write to an external file and then do an import on that file.
So what I'm asking is anyone know how I can go about this? Only solutions I've seen use Interop and I've heared as a practice you don't want to use interop in code because its slow and not terribly reliable. I was attempting to get an import from MS Access to a .csv or .txt file, but if a table doesn't have a Primary Key I'm not sure how to go about iterating over a table if it's not currently in a DataTable.
If you are doing an import on large data, you could use and OleDbReader. When using an OleDbReader, it would not affect your memory as you would read through one record at a time to do the insert into another database.
It may take slightly longer, but will ensure completion without an OutOfMemory Error.