OLE DB memory leak issues

Recently, when locating a problem, I found that using OLE DB to open a large Excel file, even if nothing is colse out, memory frees up some, but not back to the level before opening. In Excel 150M, after the decompression of the 900M scene, opened directly close, the memory is more than before opening nearly 90M. If turned off again, the memory will no longer increase. However, if you open many different large Excel files in this way, the program is prone to memory overflow collapse.
When the red gate is used for memory analysis, it is true that Excel unmanaged code is accumulating memory, thinking for a long time, there are 3 kinds of solutions currently in mind.

First, the code to reproduce the problem first, very simple, is to use OLE DB to open a 150M or so Excel

C # code?

1234567891011

privatestaticvoidTest()        { Console.Read();            using(var con=newOleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=‘{0}‘;Extended Properties=‘Excel 8.0;HDR=Yes;IMEX=1‘",@"F:/1.xls")))            {                con.Open();                con.Close();                          }  GC.Collect(); Console.Read();        }

Recurrence steps:
1, prepare a 150M Excel
2. Open the Task Manager and open the program.
3. Log the current program memory.
4, enter, wait until the file has been read, and close to release the memory stability, and then log the current program memory.
5, found a lot of 70-90m


Solution Solutions
1, after verifying that the CSV type is opened with OLE DB, no similar memory leaks occur. Therefore, you can first convert XLS, xlsx to CSV, and then the CSV using OLE DB open operation, and finally delete the CSV.
If you use this method, you need to be aware that CSV reads cannot be read according to sheet, only the entire file.
The conversion of XLS and xlsx requires the use of Microsoft.Office.Interop.Excel.Apllication, so it is necessary to encapsulate a layer of database operations currently required.

2, directly using Microsoft.Office.Interop.Excel.Apllication for Excel operation, and after the operation directly kill the Excel process.
However, because the method is Microsoft.Office.Interop.Excel.Apllication for Excel reading, each range is read by the data exchange between processes, the performance is too slag. The only thing that works is that a chunk of the data is read, but no corresponding method is found.

3, another process that uses OLE DB to manipulate Excel and kill the process after the operation.
This scheme is also not feasible, in C #, to another process, the stability, performance overhead is relatively large, especially if it is frequently read in the scene of Excel.


Issues that need to be addressed
1, there is no better way to solve this problem without using Microsoft.Office.Interop.Excel.Apllication. Because using Microsoft.Office.Interop.Excel.Apllication means that you are going to open an Excel process, which means performance overhead.
2,oledb when open Excel file, will unzip, 150M file, open after completion, program process can increase more than 900 m. When opened with Microsoft.Office.Interop.Excel.Apllication, it was found to have increased by only 150M. Can be used in the case of OLE DB, to reduce this part of the memory increase, you know that in the program itself is very memory-intensive case, open an Excel 900M, is very easy to collapse.


Here are the solutions Microsoft offers
http://support.microsoft.com/kb/319998