Problem with close connection to xls file

See more:

C#

MS-Excel

Hi All,

I used OleDb to get access to xls and xlsx file. I wrote a lot of information to Excel file. When I'am closing connection to this file, my program has acces to file. It takes a couple of minutes when program release handle to file. How I can get immediate effect?

string sqlCommandText;

//czytanie plików Excel'a z wykorzystaniem OLEDB
OleDbConnection con;
int i = 0;

workSheetNames = new String[] { };

if (isOpenXMLFormat) //read 2007 file
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
        GeneratedFile + ";Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=YES;\"";
else                //read 97-2003 file
    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
        GeneratedFile + ";Mode=ReadWrite;Extended Properties=Excel 8.0;";

try
{
    con = new OleDbConnection(connectionString);
    con.Open();
}
catch (Exception e)
{
    return;
}

//pobiera wszystkie dostepne arkusze
DataTable dataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

//pobiera nazwy arkuszy
workSheetNames = new String[dataSet.Rows.Count];
i = 0;
foreach (DataRow row in dataSet.Rows)
{
    //umieszcza nazwy arkuszy w tablicy i usuwa znaki $
    workSheetNames[i] = row["TABLE_NAME"].ToString().Trim(new[] { '$' });
    i++;
}
i = 0;

OleDbCommand insertCmd = null;
int j = 0;
try
{
    try
    {
        i = 0;
        foreach (DataRow dataRow in dt.Rows)
        {
            if (i > 3)
            {
                if (i % 100 == 0)
                {
                    con.Close();

                    while(FileInUse(GeneratedFile))
                        System.Threading.Thread.Sleep(250);
                }
                if (con.State == ConnectionState.Closed)
                    con.Open();

                sqlCommandText = "Insert INTO [" + workSheetNames[0] + "$](Nazwa, Telefon, IP, Grupa, Wlasciciel, TelnetPort) values (?, ?, ?, ?, ?, ?)";

                insertCmd = new OleDbCommand(sqlCommandText, con);
                OleDbParameter name = new OleDbParameter("@nazwa", OleDbType.VarChar, 50);
                name.Value = dataRow[1];
                insertCmd.Parameters.Add(name);
                OleDbParameter telefon = new OleDbParameter("@telefon", OleDbType.VarChar, 50);
                telefon.Value = dataRow[4];
                insertCmd.Parameters.Add(telefon);
                OleDbParameter ip = new OleDbParameter("@ip", OleDbType.VarChar, 20);
                ip.Value = dataRow[5];
                insertCmd.Parameters.Add(ip);
                OleDbParameter grupa = new OleDbParameter("@grupa", OleDbType.BSTR, 20);
                grupa.Value = tbAddGroup.Text;
                insertCmd.Parameters.Add(grupa);
                OleDbParameter wlasciciel = new OleDbParameter("@wlasciciel", OleDbType.BSTR, 20);
                wlasciciel.Value = tbAddOwner.Text;
                insertCmd.Parameters.Add(wlasciciel);
                OleDbParameter telnetPort = new OleDbParameter("@telnetport", OleDbType.BSTR, 20);
                if (String.IsNullOrEmpty(tbAddTelnetPort.Text))
                    telnetPort.Value = DBNull.Value;
                else
                    telnetPort.Value = tbAddTelnetPort.Text;
                insertCmd.Parameters.Add(telnetPort);

                insertCmd.CommandTimeout = 100;
                insertCmd.ExecuteNonQuery();
                Trace.WriteLine(con.State.ToString());

                if (i == dt.Rows.Count - 1 && con.State == ConnectionState.Open)
                {
                    con.Close();
                    System.Threading.Thread.Sleep(1000);
                }
            }
            i++;

            (sender as BackgroundWorker).ReportProgress(j++ * 100 / dt.Rows.Count);
        }

        if (con != null)
        {
            con.Close();
            con.Dispose();
        }

    }
    catch (OleDbException e)
    {
        MessageBox.Show(e.ToString());
    }
    catch (NullReferenceException e)
    {
        MessageBox.Show(e.ToString());
    }
    finally
    {
        // the closing of the reader is done in the BaseReportGenerator
        if (insertCmd != null)
            insertCmd.Dispose();

        if (con != null)
        {
            con.Close();
            con.Dispose();
        }

        if (dataSet != null)
            dataSet.Dispose();

        GC.Collect(GC.GetGeneration(con));
        GC.WaitForPendingFinalizers();

    }
}
catch (OleDbException ex)
{
    MessageBox.Show(ex.ToString());
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}

Please help me.

Thanks in Advance.