I have a simple web form consisting of only a file upload control and submit button that fires the btnUpload_Click method. The file (when the "data" directory is empty) uploads successfully, updates my SQL Server table, and executes the "UpdateTierModel_Delete" stored procedure.
When the "data" directory already has a copy of the file, I get the error "Error: The process cannot access the file 'C:\inetpub\wwwroot\NBGTier\data\NBG Tier Users.xlsx' because it is being used by another process." When I attempt to delete the file manually from Explorer, I get an error that "the action can't be completed because the file is open in IIS Worker Process".
How do I get the IIS Worker Process to release the file after it's been processed so that it may be deleted after it's been processed?
Code:
public partial class _Default : System.Web.UI.Page
{
string oleConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\\inetpub\\wwwroot\\NBGTier\\data\\NBG Tier Users.xlsx';Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
string sqlConnString = "Data Source=sql432;Initial Catalog=nbg_t;Persist Security Info=True;User ID=*****;Password=*****";
protected void Page_Load(object sender, EventArgs e)
{
fuExcelFile.Focus();
}
protected System.Data.DataTable GetTableData(string wkSheet)
{
OleDbConnection cn = new OleDbConnection(oleConnString);
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + wkSheet + "] WHERE F11 = 'Y'", cn);
cmd.CommandType = CommandType.Text;
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds, "tablex");
DataRow[] dr = ds.Tables[0].Select();
foreach (DataRow row in ds.Tables[0].Rows)
{
row["F4"] = row["F4"];
row["F11"] = row["F11"];
}
ds.Tables[0].AcceptChanges();
return ds.Tables["tablex"];
}
public string[] GetExcelSheetNames()
{
OleDbConnection con = null;
System.Data.DataTable dt = null;
con = new OleDbConnection(oleConnString);
con.Open();
dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheetNames = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheetNames[i] = row["TABLE_NAME"].ToString();
i++;
}
return excelSheetNames;
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (fuExcelFile.HasFile == false)
{
Response.Write("Please select a file to upload.");
}
else
{
if (fuExcelFile.PostedFile.FileName.Contains("NBG Tier Users.xlsx"))
{
string fn = System.IO.Path.GetFileName(fuExcelFile.PostedFile.FileName);
string SaveLocation = Server.MapPath("data") + "\\" + fn;
try
{
fuExcelFile.PostedFile.SaveAs(SaveLocation);
Response.Write("The file has been processed.");
String[] sheetNames = GetExcelSheetNames();
DataView dv = new DataView(GetTableData(sheetNames[0].ToString()));
SqlConnection cn = new SqlConnection(sqlConnString);
cn.Open();
foreach (DataRow row in dv.Table.Select())
{
SqlCommand cmd = new SqlCommand("INSERT INTO Temp_Tier (AcctNo) VALUES (" + row["F4"].ToString() + ")", cn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
cn.Close();
DeleteTempStatements(SaveLocation);
}
catch (Exception ex)
{
Response.Write("Error: " + ex.Message);
}
}
else
{
Response.Write("The file name must be <b>"NBG Tier Users.xlsx"</b>");
}
}
}
protected void DeleteTempStatements(string fileName)
{
SqlConnection cn = new SqlConnection(sqlConnString);
SqlCommand cmd = new SqlCommand("UpdateTierModel_Delete", cn);
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
System.IO.File.Delete(fileName);
}
}
The process cannot access the file...
button below this post to show your appreciation!
