请问ADO.NET +大量的INSERT +的Excel + C＃=＆QUOT;一个坏的时间和QUOT;？ [英] Does ADO.NET + massive INSERTs + Excel + C# = "A bad time"?

问题描述

基本上，我需要插入一组数据到Excel文件。创建OLEDB连接似乎是最快的方式，但我已经看到了遇到了内存问题。进程使用的内存似乎让我执行INSERT查询的增长。我已经缩小下来，当我输出到Excel文件（内存持稳，而不输出到Excel），只发生。我关闭并重新打开每个工作表之间的连接，但是这似乎并没有对内存使用的效果（如也是如此的Dispose（））。该数据被写入成功，我可以用相对较小的数据集的验证。如果任何人有洞察力，这将是AP preciated。

Basically I need to insert a bunch of data to an Excel file. Creating an OleDB connection appears to be the fastest way but I've seen to have run into memory issues. The memory used by the process seems to keep growing as I execute INSERT queries. I've narrowed them down to only happen when I output to the Excel file (the memory holds steady without the output to Excel). I close and reopen the connection in between each worksheet, but this doesn't seem to have an effect on the memory usage (as so does Dispose()). The data is written successfully as I can verify with relatively small data sets. If anyone has insight, it would be appreciated.

initializeADOConn（）的是所谓的构造

initADOConnInsertComm（）的创建插入参数插入查询

initADOConnInsertComm() creates the insert parameterized insert query

writeRecord（）的称为每当一个新的记录被写入。根据需要创建新的工作表。

writeRecord() is called whenever a new record is written. New worksheets are created as needed.

public bool initializeADOConn()
        {
            /* Set up the connection string and connect.*/
            string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + this.destination + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
            //DbProviderFactory factory =
                //DbProviderFactories.GetFactory("System.Data.OleDb");
            conn = new OleDbConnection(connectionString);
            conn.ConnectionString = connectionString;
            conn.Open();

            /* Intialize the insert command. */
            initADOConnInsertComm();
            return true;
        }
    public override bool writeRecord(FileListerFileInfo file)
            {
                /* If all available sheets are full, make a new one. */
                if (numWritten % EXCEL_MAX_ROWS == 0)
                {
                    conn.Close();
                    conn.Open();
                    createNextSheet();
                }
                /* Count this record as written. */
                numWritten++;
                /* Get all of the properties of the FileListerFileInfo record and add
                 * them to the parameters of the insert query. */
                PropertyInfo[] properties = typeof(FileListerFileInfo).GetProperties();
                for (int i = 0; i < insertComm.Parameters.Count; i++)
                    insertComm.Parameters[i].Value = properties[i].GetValue(file, null);
                /* Add the record. */
                insertComm.ExecuteNonQuery();

                return true;
            }

编辑：

没有，我不使用Excel的。我故意避开Interop.Excel由于其表现不佳（至少从我的涉猎与它）。

No, I do not use Excel at all. I'm intentionally avoiding Interop.Excel due to its poor performance (at least from my dabbles with it).

推荐答案

答案是是，您所描述的公式的确实的相等的日子不好过。

The answer is Yes, the formula you describe does equal a bad time.

如果你有一个数据库，方便（SQL Server或访问有利于本），你可以做你的插入到数据库表，然后导出表一下子到Excel US preadsheet。

If you have a database handy (SQL Server or Access are good for this), you can do all of your inserts into a database table, and then export the table all at once into an Excel spreadsheet.

一般来说，数据库善于处理大量的插入，而小号preadsheets都没有。

Generally speaking, databases are good at handling lots of inserts, while spreadsheets aren't.

这篇关于请问ADO.NET +大量的INSERT +的Excel + C＃=＆QUOT;一个坏的时间和QUOT;？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！