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