The process cannot access the file.xlsx' because it is being used by another process. While Exporting Data from ASPX to an EXCEL file

Question

When I try to Export I got an Exception which says that the "Process cannot access the file "Mypath\Filename.xlsx" because it is being used by another process" The Exception is Thrown when I try to Export and it is tracked back to the Line byte[] content = File.ReadAllBytes(fileName); So I don't Understand the Problem. Am Not Using File stream to Read data

Here are the codes I Used

protected void BtnExport_Click(object sender, EventArgs e)
    {
        using (MydatabaseEntities dc = new MydatabaseEntities())
        {
            List<EmployeeMaster> emList = dc.EmployeeMasters.ToList();
            StringBuilder sb = new StringBuilder();
            if (emList.Count > 0)
            {
                string fileName = Path.Combine(Server.MapPath("~/ImportDocument"), DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xlsx");
                string conString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileName + "; Extended Properties='Excel 12.0;HDR=yes'";
                using (OleDbConnection con = new OleDbConnection(conString))
                {
                    string strCreateTab = "Create table EmployeeData(" +
                    "[Employee ID] varchar(50)," +
                    "[Company Name] varchar(200)," +
                    "[Contact Name] varchar(200)," +
                    "[Contact Title] varchar(200)," +
                    "[Employee Address] varchar(200)," +
                    "[Postal Code] varchar(50))";
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    OleDbCommand cmd = new OleDbCommand(strCreateTab, con);
                    cmd.ExecuteNonQuery();
                    string StrInsert = "Insert into EmployeeData([Employee ID],[Company Name]," + "[Contact Name],[Contact Title],[Employee Address], [Postal Code]" +
                    ")values(?,?,?,?,?,?)";
                    OleDbCommand cmdIns = new OleDbCommand(StrInsert, con);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 50);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 50);
                    foreach (var i in emList)
                    {
                        cmdIns.Parameters[0].Value = i.EmployeeId;
                        cmdIns.Parameters[1].Value = i.CompanyName;
                        cmdIns.Parameters[2].Value = i.ContactName;
                        cmdIns.Parameters[3].Value = i.ContactTitle;
                        cmdIns.Parameters[4].Value = i.EmployeeAddress;
                        cmdIns.Parameters[5].Value = i.PostalCode;
                        cmdIns.ExecuteNonQuery();
                    }
                    //create Downloadable File
                    byte[] content = File.ReadAllBytes(fileName);
                    HttpContext context = HttpContext.Current;
                    context.Response.BinaryWrite(content);
                    context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    context.Response.AppendHeader("Content-Disposition", "attachment; filename=EmployeeData.xlsx");
                    context.Response.End();
                    con.Close();
                }
            }
        }
    }
}

So Help me Please

Answer 1

I'm really Happy that I was Able to Fix my Problem with a simple trick I tried. The Error I was Getting that The file is Already Open, was caused by the connection I didn't close before creating a downloadable file!! Here is the change I made in Bold. :-)

protected void BtnExport_Click(object sender, EventArgs e)
    {
        using (MydatabaseEntities dc = new MydatabaseEntities())
        {
            List<EmployeeMaster> emList = dc.EmployeeMasters.ToList();
            StringBuilder sb = new StringBuilder();
            if (emList.Count > 0)
            {
                string fileName = Path.Combine(Server.MapPath("~/ImportDocument"), DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xlsx");
                string conString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileName + "; Extended Properties='Excel 12.0;HDR=yes'";
                using (OleDbConnection con = new OleDbConnection(conString))
                {
                    string strCreateTab = "Create table EmployeeData(" +
                    "[Employee ID] varchar(50)," +
                    "[Company Name] varchar(200)," +
                    "[Contact Name] varchar(200)," +
                    "[Contact Title] varchar(200)," +
                    "[Employee Address] varchar(200)," +
                    "[Postal Code] varchar(50))";
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    OleDbCommand cmd = new OleDbCommand(strCreateTab, con);
                    cmd.ExecuteNonQuery();
                    string StrInsert = "Insert into EmployeeData([Employee ID],[Company Name]," + "[Contact Name],[Contact Title],[Employee Address], [Postal Code]" +
                    ")values(?,?,?,?,?,?)";
                    OleDbCommand cmdIns = new OleDbCommand(StrInsert, con);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 50);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 200);
                    cmdIns.Parameters.Add("?", OleDbType.VarChar, 50);
                    foreach (var i in emList)
                    {
                        cmdIns.Parameters[0].Value = i.EmployeeId;
                        cmdIns.Parameters[1].Value = i.CompanyName;
                        cmdIns.Parameters[2].Value = i.ContactName;
                        cmdIns.Parameters[3].Value = i.ContactTitle;
                        cmdIns.Parameters[4].Value = i.EmployeeAddress;
                        cmdIns.Parameters[5].Value = i.PostalCode;
                        cmdIns.ExecuteNonQuery();
                    }
                    //create Downloadable File
                    **con.Close();**
                    byte[] content = File.ReadAllBytes(fileName);
                    HttpContext context = HttpContext.Current;
                    context.Response.BinaryWrite(content);
                    context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    context.Response.AppendHeader("Content-Disposition", "attachment; filename=EmployeeData.xlsx");
                    context.Response.End();

                }
            }
        }
    }
}