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;
OleDbConnection con;
int i = 0;
workSheetNames = new String[] { };
if (isOpenXMLFormat)
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
GeneratedFile + ";Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=YES;\"";
else
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;
}
DataTable dataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
workSheetNames = new String[dataSet.Rows.Count];
i = 0;
foreach (DataRow row in dataSet.Rows)
{
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
{
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.