Save file uploaded to azurewebsite
Everything works fine on my local machine, but ..
After publishing my web project, MVC4
there is a problem with the downloaded Excel file. I am downloading HttpPostedFileBase
and posting the path to my BL. There I load it in dataTable
and on my second call I get it list
.
Here is the code.
Controller:
[HttpPost]
public ActionResult UploadCards(HttpPostedFileBase file, string sheetName, int ProductID)
{
try
{
if (file == null || file.ContentLength == 0)
throw new Exception("The user not selected a file..");
var fileName = Path.GetFileName(file.FileName);
var path = Server.MapPath("/bin");
if (!Directory.Exists(path))
Directory.CreateDirectory(path);
path = Path.Combine(path, fileName);
file.SaveAs(path);
DataTable cardsDataTable = logic.LoadXLS(path, sheetName);
cardsToUpdate = logic.getUpdateCards(cardsDataTable, ProductID);
foreach (var item in cardsToUpdate)
{
if (db.Cards.ToList().Exists(x => x.SerialNumber == item.SerialNumber))
cardsToUpdate.Remove(item);
}
Session["InfoMsg"] = "click update to finish";
}
catch (Exception ex)
{
Session["ErrorMsg"] = ex.Message;
}
return View("viewUploadCards", cardsToUpdate);
}
BL:
public DataTable LoadXLS(string strFile, String sheetName)
{
DataTable dtXLS = new DataTable(sheetName);
try
{
string strConnectionString = "";
if (strFile.Trim().EndsWith(".xlsx"))
strConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", strFile);
else if (strFile.Trim().EndsWith(".xls"))
strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFile);
OleDbConnection SQLConn = new OleDbConnection(strConnectionString);
SQLConn.Open();
OleDbDataAdapter SQLAdapter = new OleDbDataAdapter();
string sql = "SELECT * FROM [" + sheetName + "$]";
OleDbCommand selectCMD = new OleDbCommand(sql, SQLConn);
SQLAdapter.SelectCommand = selectCMD;
SQLAdapter.Fill(dtXLS);
SQLConn.Close();
}
catch (Exception ex)
{
string res = ex.Message;
return null;
}
return dtXLS;
}
and
public List<Card> getUpdateCards(DataTable dt, int prodId)
{
List<Card> cards = new List<Card>();
try
{
Product product = db.Products.Single(p => p.ProductID == prodId);
foreach (DataRow row in dt.Rows)
{
cards.Add(new Card
{
SerialNumber = row[0].ToString(),
UserName = row[1].ToString(),
Password = row[2].ToString(),
Activated = false,
Month = product.Months,
Bandwidth = product.Bandwidth,
ProductID = product.ProductID,
// Product = product
});
}
}
catch (Exception ex)
{
db.Log.Add(new Log { LogDate = DateTime.Now, LogMsg = "Error : " + ex.Message });
}
return cards;
}
Now I think it is Windows Azure
not allowing me to save this file because on the middle view when I should see the data, I cannot see it.
I was thinking of some ways ... one is not to save the file, but I don't see how to complete ConnectionString
... the second is maybe there is a way to save the file there.
I would like to receive suggestions on how to solve this problem ...
10x and sorry for my bad english =)
source to share
I'm confused, but I found a similar question here . Not really, but it gave me a good direction.
State the final result:
[HttpPost]
public ActionResult UploadCards(HttpPostedFileBase file, string sheetName, int ProductID)
{
IExcelDataReader excelReader = null;
try
{
if (file == null || file.ContentLength == 0)
throw new Exception("The user not selected a file..");
if (file.FileName.Trim().EndsWith(".xlsx"))
excelReader = ExcelReaderFactory.CreateOpenXmlReader(file.InputStream);
else if (file.FileName.Trim().EndsWith(".xls"))
excelReader = ExcelReaderFactory.CreateBinaryReader(file.InputStream);
else
throw new Exception("Not a excel file");
cardsToUpdate = logic.getUpdateCards(excelReader.AsDataSet().Tables[sheetName], ProductID);
foreach (var item in cardsToUpdate)
{
if (db.Cards.ToList().Exists(x => x.SerialNumber == item.SerialNumber))
cardsToUpdate.Remove(item);
}
Session["InfoMsg"] = "Click Update to finish";
}
catch (Exception ex)
{
Session["ErrorMsg"] = ex.Message;
}
finally
{
excelReader.Close();
}
return View("viewUploadCards", cardsToUpdate);
}
10q everything.
EDIT: download, link and use
dll is avalibale hare I add link to Excel.dll and I add using Excel;
source to share
The problem may be caused by writing the file to disk. Cloud providers usually don't allow applications to write to disk.
In your case, it seems like the file is only being written to disk temporarily and loaded directly into the DB. You should be able to directly open the stream from the downloaded file and write it directly to the DB - without writing to disk.
Check for the exception you are holding in the session - you should find more information there.
source to share
@hoonzis is right, writing files to disk in the cloud is not allowed (you cannot receive event or set file path). You should use blob storage, much more efficient for files and cheaper than sql. I recommend using a table storage service, it's noSQL, but it's cheaper than azure sql. Use azure sql only if necessary for your solution.
Blob storage is described in more detail here: http://www.windowsazure.com/en-us/develop/net/how-to-guides/blob-storage/
Table storage: http://www.windowsazure.com/en-us/develop/net/how-to-guides/table-services/
You can find more information on choosing the right storage here: http://www.windowsazure.com/en-us/develop/net/fundamentals/cloud-storage-scenarios/
source to share