File not closing in windows service

  • Thread starter Thread starter tshad
  • Start date Start date
T

tshad

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 can't 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 said:
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 can't 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?
Click to expand...

Hard to say if you don't post code that is actually directly interacting
with the file.

Or, put another way: you've 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
 
Peter Duniho said:
Hard to say if you don't post code that is actually directly interacting
with the file.

Or, put another way: you've 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.
Click to expand...

This isn't 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 doesn't.

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,
"'", "")), connection);
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 said:
[...]
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 doesn't.

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

Not useless. It's 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
"-4" from the MSDN page
(http://msdn.microsoft.com/en-us/library/ms254502.aspx). Obviously,
this corresponds to some flags that control the behavior of the
provider, but I haven't 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
 
Back
Top