Tech
Forums
Jobs
Books
Events
Interviews
Live
More
Learn
Training
Career
Members
Videos
News
Blogs
Login
Sign Up
Ask Question
1
Reply
The process cannot access the file error
Edison Augusthy
Sep 3 2016 12:28 AM
270
Reply
i made a code to add multiple excel sheets to add to database.but when am trying to add same file its showing following error-"The process cannot access the file 'C:\Users\Sinnonteq\Desktop\my projects\Import\Import\Content\b.xlsx' because it is being used by another process."(b.xlsx is the excel file i tried).is it because of some type of locking mechanism in asp..? some one told me that with "using" i can solve this problem .since am new to mvc please help if anyone knows..
here is the code
using
System;
using
System.Collections.Generic;
using
System.Data;
using
System.Data.OleDb;
using
System.Linq;
using
System.Web;
using
System.Web.Mvc;
using
System.Xml;
using
System.Data.SqlClient;
using
System.Configuration;
namespace
Import.Controllers
{
public
class
HomeController : Controller
{
//
// GET: /Home/
public
ActionResult Index()
{
return
View();
}
[HttpPost]
public
ActionResult Index(HttpPostedFileBase file)
{
DataSet ds =
new
DataSet();
if
(Request.Files[
"file"
].ContentLength > 0)
{
string
fileExtension = System.IO.Path.GetExtension(Request.Files[
"file"
].FileName);
if
(fileExtension ==
".xls"
|| fileExtension ==
".xlsx"
)
{
string
fileLocation = Server.MapPath(
"~/Content/"
) + Request.Files[
"file"
].FileName;
{
if
(System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
}
Request.Files[
"file"
].SaveAs(fileLocation);
string
excelConnectionString =
string
.Empty;
excelConnectionString =
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ fileLocation +
";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""
;
//connection String for xls file format.
if
(fileExtension ==
".xls"
)
{
excelConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ fileLocation +
";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""
;
}
//connection String for xlsx file format.
else
if
(fileExtension ==
".xlsx"
)
{
excelConnectionString =
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ fileLocation +
";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""
;
}
//Create Connection to Excel work book and add oledb namespace
OleDbConnection excelConnection =
new
OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt =
new
DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null
);
if
(dt ==
null
)
{
return
null
;
}
String[] excelSheets =
new
String[dt.Rows.Count];
int
t = 0;
//excel data saves in temp file here.
foreach
(DataRow row
in
dt.Rows)
{
excelSheets[t] = row[
"TABLE_NAME"
].ToString();
t++;
}
OleDbConnection excelConnection1 =
new
OleDbConnection(excelConnectionString);
string
query =
string
.Format(
"Select * from [{0}]"
, excelSheets[0]);
using
(OleDbDataAdapter dataAdapter =
new
OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
}
if
(fileExtension.ToString().ToLower().Equals(
".xml"
))
{
string
fileLocation = Server.MapPath(
"~/Content/"
) + Request.Files[
"FileUpload"
].FileName;
if
(System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files[
"FileUpload"
].SaveAs(fileLocation);
XmlTextReader xmlreader =
new
XmlTextReader(fileLocation);
// DataSet ds = new DataSet();
ds.ReadXml(xmlreader);
xmlreader.Close();
}
for
(
int
i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string
conn = ConfigurationManager.ConnectionStrings[
"dbconnection"
].ConnectionString;
SqlConnection con =
new
SqlConnection(conn);
string
query =
"if not exists (select 1 from data where phone = '"
+ ds.Tables[0].Rows[i][2].ToString() +
"')Insert into data(name,addres,phone) Values('"
+ ds.Tables[0].Rows[i][0].ToString() +
"','"
+ ds.Tables[0].Rows[i][1].ToString() +
"','"
+ ds.Tables[0].Rows[i][2].ToString() +
"')"
;
con.Open();
SqlCommand cmd =
new
SqlCommand(query, con);
cmd.ExecuteNonQuery();
con.Close();
}
}
return
View();
}
}
}
Upload Source Code
Select only zip and rar file.
Post
Reset
Cancel
Answers (
1
)
Next Recommended Forum
Boxing and Unboxing in .NET
About Application Development