File not closing in windows service

I have a windows service program that seems to once in a while will not
release a file.

When I am trying to move the file (in the same program) I will get a file in
use and the file will not be moved.

If I go to a Windows Explorer, I cannot delete it there either.  I have to
stop the service to delete it.

But the only place I open and read it is in a try/catch where I close the
connect in the finally clause.

What would cause it to stay open?

***************************************************
try
{
if (Path.GetExtension(strFile).ToUpper() == ".XLS")
{
mstrConnectionXLS = string.Format(mstrConnectionXLS,
strFile);
connection = new OleDbConnection(mstrConnectionXLS);
connection.Open();


DataTable dt =
connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
....
}
catch (Exception exc)
{
Logging.WriteToLog(AppSettings.LogFilePath,
String.Format("Exception filling dataset: {0}",
exc.Message));

// Move Zip file to Exception Folder

}
finally
{
if (connection != null)
connection.Close();
}
********************************************************

Thanks,

Tom

tshad wrote:Hard to say if you do not post code that is actually directly

Hard to say if you do not post code that is actually directly interacting
with the file.

Or, put another way: you have chosen to delegate the file access to a
third-party provider (relative to your own code), and thus the question
of if and when it will eventually relinquish control of the file is
specific to that provider.

Because of connection pooling, I can see no reason why calling Close()
on a specific connection will necessarily actually close the file in
question.

Pete

This is not much better but I put the part in that fills the dataset.

This is not much better but I put the part in that fills the dataset.

This has been working fine for months without much change.  The only
difference seems to be with this file that actaully has about 6000 rows vs.
about 200.  Sometimes it releases and other times it does not.

So you are saying that my finally is really useless except to close the
connection since I am not actually closing the file?

***************************************************
string mstrConnectionXLS = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0};Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
string mstrConnectionXLSX = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source={0};Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"";
string strSQL = "SELECT * FROM [{0}$]";

try
{
if (Path.GetExtension(strFile).ToUpper() == ".XLS")
{
mstrConnectionXLS = string.Format(mstrConnectionXLS,strFile);
connection = new OleDbConnection(mstrConnectionXLS);
connection.Open();


DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null);
if (dt == null)
{
return null;
}
foreach (DataRow row in dt.Rows)
{
if (!row["TABLE_NAME"].ToString().EndsWith("$"))
{
// Check to see if Table_Name has single quote at end
if(!row["TABLE_NAME"].ToString().EndsWith("$'"))
continue;
}
tableName = row["TABLE_NAME"].ToString().Replace("$", "");
da = new OleDbDataAdapter(String.Format(strSQL, Regex.Replace(tableName,
da.Fill(ds, tableName);
}
}
catch (Exception exc)
{
Logging.WriteToLog(AppSettings.LogFilePath,
String.Format("Exception filling dataset: {0}",exc.Message));

// Move Zip file to Exception Folder

}
finally
{
if (connection != null)
connection.Close();
}
********************************************************

Thanks,

Tom

tshad wrote:Not useless.

Not useless.  it is just that by going through the DB provider, you are
too far removed from the file for the close of the _file_ to be
deterministic.

That said, browsing around MSDN, it looks like you can disable
connection pooling for the OleDb provider by including a specific flag
in the connection string.  Try modifying your connection strings so that
they look like this:

string mstrConnectionXLS = "Provider=Microsoft.Jet.OLEDB.4.0;OLE DB
Services=-4;Data Source={0};Extended Properties=\"Excel
8.0;HDR=NO;IMEX=1\"";
string mstrConnectionXLSX = "Provider=Microsoft.ACE.OLEDB.12.0;OLE DB
Services=-4;Data Source={0};Extended Properties=\"Excel
12.0;HDR=NO;IMEX=1\"";

Note the new "OLE DB Services" value.  Unfortunately, I just copied the
(http://msdn.microsoft.com/en-us/library/ms254502.aspx).  Obviously,
this corresponds to some flags that control the behavior of the
provider, but I have not found the reference that actually explains what
flags are possible or what their values are.

But maybe that magic number will fix your problem.  If not, maybe you
can look around for a flag that will.  :)

Pete