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" <ber...@gmail.com> wrote in message
news:c3f9f744-7f4e-4613...@h11g2000prf.googlegroups.com...

[TB]

On Feb 26, 10:15 pm, "Elad" <eladooosh```hotmail```com> wrote:
> 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!"
>

> "TB" <berh...@gmail.com> wrote in message
>
> news:c3f9f744-7f4e-4613...@h11g2000prf.googlegroups.com...
>
> > Greetings,
>
> > I use OleDB from my c# application to populate anexcelsheet. I open

> > the connection in a using clause (which automatically close the

> > connection after exit) and theexcelfileis created.
> > However, I am not able to viewexcelfilebefore I exit the
> > application. It seems that there is alockon theexcelfileand 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 forceOleDbto release all locks on the

> >file? connectionstring setting..?
>
> > ConnectionString:
> > Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"c:\\temp\
> > \myExcelFile.xls\";Extended Properties=\"Excel8.0;OLEDB
> > 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
> > tofile
> > }
> > transaction.Dispose();
>
> > Any help highly appreciated!
>
> > -thomas

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]]

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!

--
http://www.thinkersroom.com/bytes

[Collin Yeadon]

I found almost the same thing happening on my import script. What ended up being the issue is that I was replacing a string and stripping out extra spaces.. I didn't realize that there was an endless loop.. oops!

I had something like..

while (tempString.IndexOf(" ") != -1) // condense spaces
{
tempString.Replace(" ", " ");
}

and this was not actually changing the value of tempString.. So I had to change this line to...

tempString = tempString.Replace(" ", " ");


This was hard to track down because there were several sheets inside the XLS file and only a few had multiple spaces.. It seemed to me that the debugger was exiting but the lock on the file remained..

The lesson is.. don't assume it's this lousy 'ol Jet drivers.. it might just be simply stepping over the wrong methods when you are debugging...

>> On Tuesday, February 26, 2008 3:15 PM Elad wrote:

>> 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" <ber...@gmail.com> wrote in message
>> news:c3f9f744-7f4e-4613...@h11g2000prf.googlegroups.com...