OledbConnection的“关闭”和/或“处置”非常慢，需要20秒钟才能执行 [英] OledbConnection Close and/or Dispose are very slow, take 20s to execute

问题描述

在使用OleDbConnection访问excel文件的工作表名称时，遇到了一个问题。代码中的所有内容都可以正常执行并正常工作，直到OleDbConnection在using语句结束时关闭为止。这会导致程序停止15到20秒或更长时间！

我尝试摆脱using语句，并分别使用OleDbConnection.Close（）和OleDbConnection .Dispose（）方法，这两个方法的执行时间都非常长。如果我从不关闭连接，则程序运行完美。

  //由于某种原因，现在关闭连接的速度确实非常慢b $ b使用（OleDbConnection conn = new OleDbConnection（connectString））
 {
 conn.Open（）; 
 DataTable dbSchema = conn.GetOleDbSchemaTable（OleDbSchemaGuid.Tables，null）; 
 sheetNames.Clear（）; 
 for（int i = 0; i< dbSchema.Rows.Count; i ++）
 {
 sheetNames.Add（dbSchema.Rows [i] [ TABLE_NAME]。ToString（） ）; 
} 
 comboBox1.Items.Clear（）; 
 comboBox1.Items.AddRange（sheetNames.ToArray（））; 
 comboBox1.SelectedIndex = 0; 
 //conn.Close（）; 
} 
 //连接字符串
 // XLS 
 connectionString = Provider = Microsoft.Jet.OLEDB.4.0; Data Source = {0}; Extended Properties ='Excel 8.0 ; HDR =否; IMEX = 1;'; /> 
 // XLSX 
 connectionString = Provider = Microsoft.ACE.OLEDB.12.0; Data Source = {0}; Extended Properties ='Excel 12.0; HDR = No; IMEX = 1;'; 
  

我注意到一个非常奇怪的事情是，对于每种类型的excel文件，连接都需要很长时间才能关闭.xls文件除外（自1997-2003年以来的Excel）。

最重要的是，我已经在互联网上到处搜索过，实际上找不到任何找到解决方案的人，只有未解决的论坛帖子。以下是我从同样问题的人那里找到的一些链接：

OleDbConnection关闭方法需要较长的执行时间，

OleDbConnection处理速度非常慢（2s）

http://forums.asp.net/t/2059359。 aspx？OledbConnection + Close + take + too + long + to + execute

解决方案

我遇到了类似的性能问题关闭与Access数据库的OLE DB连接时。一个相关问题的建议解决了该问题：在连接字符串中添加以下内容：

  OLE DB Services = -1; 
  

我无法在Excel工作簿中重现该问题，但这也可能适用于这些人。

While using OleDbConnection to access sheet names of the excel files I encountered a problem. Everything in the code executes fine and works, until the OleDbConnection closes at the end of the using statement. This causes the program to stop for 15-20 seconds or more!

I've tried getting rid of the using statement and individually using the OleDbConnection.Close() and OleDbConnection.Dispose() methods, both of those take incredibly long to execute as well. If I never close the connection at all, the program runs perfectly.

//Closing connection is inredibly slow now for some reason
                using (OleDbConnection conn = new OleDbConnection(connectString))
                {
                    conn.Open();
                    DataTable dbSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    sheetNames.Clear();
                    for (int i = 0; i < dbSchema.Rows.Count; i++)
                    {
                        sheetNames.Add(dbSchema.Rows[i]["TABLE_NAME"].ToString());
                    }
                    comboBox1.Items.Clear();
                    comboBox1.Items.AddRange(sheetNames.ToArray());
                    comboBox1.SelectedIndex = 0;
                    //conn.Close();
                }
//Connection strings
//XLS
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0;HDR=No;IMEX=1;';" />
//XLSX
connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties='Excel 12.0;HDR=No;IMEX=1;';"

A very strange thing I noticed is that the connection takes very long to close for every type of excel file EXCEPT .xls files (from 1997-2003 era Excel).

On top of all this I've searched everywhere on the internet and cannot actually find anyone who has found a solution to this, only unsolved forum posts. Here are a few links I've found from those with the same problem:

OleDbConnection close method taking long execution time,

OleDbConnection Dispose very slow (2s)

http://forums.asp.net/t/2059359.aspx?OledbConnection+Close+taking+too+long+to+execute

解决方案

I had a similar performance issue when closing OLE DB connections to Access databases. A suggestion from a related question resolved the issue: adding the following to the connection string:

OLE DB Services=-1;

I can't reproduce the issue with Excel workbooks, but this may also work for those.

这篇关于OledbConnection的“关闭”和/或“处置”非常慢，需要20秒钟才能执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！