Click here to Skip to main content
15,922,533 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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.
Posted
Updated 6-Jun-11 2:06am
v2

1 solution

It think it has something to do with Connection Pooling[^]. It is not closing the file before connection times out.

Try add this to your connection string: Pooling=false
Or set the timeout to a lower value: Connection Lifetime=5 (5 seconds)
 
Share this answer
 
Comments
Skynet87 9-Jun-11 8:06am    
When I'm using this options (Pooling or Connection Lifetime) I'm getting error - "Could not find installable ISAM".
Kim Togo 9-Jun-11 8:26am    
Perhaps it has something to do with the choice of the provider?

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" or "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
Skynet87 13-Jun-11 8:18am    
When I'm executing thr program from VS this error apeared and when I'm executing the program directly - everything is OK apart from keeping handle to a xls file.
Kim Togo 13-Jun-11 10:22am    
I still think it has something to do with connection pooling. The connection pool will keep the file open as long as the connection lifetime is defined.
And it is consistent with what you have seen. After couple of minutes the file is closed, and that is the connection pool that is releasing the file, not the program.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900