OleDB locks excel file

[TB]

Greetings,

I use OleDB from my c# application to populate an excel sheet. I open
the connection in a using clause (which automatically close the
connection after exit) and the excel file is created.
However, I am not able to view excel file before I exit the
application. It seems that there is a lock on the excel file and that
the transaction is not fully completed before application close (or
sometimes when I open and close other parts of the application).

Does anyone know a way to force Ole Db to release all locks on the
file? connectionstring setting..?

ConnectionString:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"c:\\temp\
\myExcelFile.xls\";Extended Properties=\"Excel 8.0;OLE DB
Services=0;HDR=Yes;Mode = 3;\"";

Tried Services 0/-4

Example code:
using(OleDbConnection connection = new
OleDbConnection(connectionString.ToString()))
{
connection.Open();
transaction =
connection.BeginTransaction(IsolationLevel.ReadUncommitted);

CreateInspectionSheet(inspection, connection, transaction);

transaction.Commit(); // does not seem to force data written
to file
}
transaction.Dispose();

Any help highly appreciated!

-thomas

 

[Elad]

Umm, what does CreateInspectionSheet() do?

Do you use IDbCommand objects over the connection and dispose of all of them
when you're done?

Just so you know, from my own experience - using OleDb to interact in Excel
is a very bad idea. I've wasted days in debugging weird and erratic
behaviour until I finally gave up and move to an alternative.

Elad
http://www.sisense.com
"This was written in .Net?! Unbelievable!"

 

[TB]

Umm, what does CreateInspectionSheet() do?

Do you use IDbCommand objects over the connection and dispose of all of them
when you're done?

Just so you know, from my own experience - using OleDb to interact inExcel
is a very bad idea. I've wasted days in debugging weird and erratic
behaviour until I finally gave up and move to an alternative.

Eladhttp://www.sisense.com
"This was written in .Net?! Unbelievable!"

I disposed all objects ans explicitly set them to null.

However, I found a solution of how to force the connection to close:
Instead of using the using{} clause I opened a normal connection and
then explicitly called Dispose and set objects to null after
everything was finished.
Then I called
GC.Collect();
GC.WaitForPendingFinalizers();
which initiates the garbage collector. This seems to work as intended
closing the connection and freeing the excel file.

Otherwise I've had some other issues by using OleDB, but the
alternative has been much more cumbersome/slow and requires Excel to
be installed so for now OleDb is my solution.
Thanks anyway!
-thomas

 

[Rad [Visual C# MVP]]

Greetings,

I use OleDB from my c# application to populate an excel sheet. I open
the connection in a using clause (which automatically close the
connection after exit) and the excel file is created.
However, I am not able to view excel file before I exit the
application. It seems that there is a lock on the excel file and that
the transaction is not fully completed before application close (or
sometimes when I open and close other parts of the application).

Does anyone know a way to force Ole Db to release all locks on the
file? connectionstring setting..?

ConnectionString:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"c:\\temp\
\myExcelFile.xls\";Extended Properties=\"Excel 8.0;OLE DB
Services=0;HDR=Yes;Mode = 3;\"";

Tried Services 0/-4

Example code:
using(OleDbConnection connection = new
OleDbConnection(connectionString.ToString()))
{
connection.Open();
transaction =
connection.BeginTransaction(IsolationLevel.ReadUncommitted);

CreateInspectionSheet(inspection, connection, transaction);

transaction.Commit(); // does not seem to force data written
to file
}
transaction.Dispose();

Any help highly appreciated!

-thomas

Perhaps theres some code in the CreateInspectionSheet that is causing the
issue? What does the code do?

As for interfacing with Excel if you have flexibility for your next project
i woould highly recommend some library e.g Aspose or GemBox designed for
this sort of thing. Interfacing with Excel files through OleDB can at times
cause actue grief!