public async Task <HttpResponseMessage> UpdateEmployeeLeaveBalance()
{
// Check if the request contains multipart/form-data.
if (!Request.Content.IsMimeMultipartContent())
{
throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType);
}
string root = HttpContext.Current.Server.MapPath("~/App_Data");
var provider = new MultipartFormDataStreamProvider(root);
DataSet ds = new DataSet();
DataSet ospDS = new DataSet();
SqlConnection con = null;
OleDbDataAdapter oda = null;
SqlBulkCopy objbulk = null;
try
{
// Read the form data.
await Request.Content.ReadAsMultipartAsync(provider);
// This illustrates how to get the file names.
foreach (MultipartFileData file in provider.FileData)
{
Trace.WriteLine(file.Headers.ContentDisposition.FileName);
Trace.WriteLine("Server file path: " + file.LocalFileName);
}
MultipartFileData fileData = provider.FileData.First();
string fileName = fileData.Headers.ContentDisposition.FileName;
if (fileName.StartsWith("\"") && fileName.EndsWith("\""))
{
fileName = fileName.Trim('"');
}
string destinationFileName = fileName + Guid.NewGuid().ToString() + Path.GetExtension(fileName.Replace(" ", "_"));
//string destinationFileName = fileName;
// Use the Path.Combine method to safely append the file name to the path.
// Will not overwrite if the destination file already exists.
File.Move(fileData.LocalFileName, Path.Combine(HttpContext.Current.Server.MapPath("~/App_Data/Uploads/"), destinationFileName));
File.Delete(fileData.LocalFileName);
string fileExtension = Path.GetExtension(destinationFileName);
string fileLocation = HttpContext.Current.Server.MapPath("~/App_Data/Uploads/") + destinationFileName;
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
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();
string[] sheets = Utility.GetSheet.GetSheetName(excelConnection);
bool flag = false;
if (sheets.Length == 0)
{
return(Request.CreateResponse(HttpStatusCode.OK, new EMSResponseMessage("EMS_304", "No Sheets available in the excel file", "No Sheets available in the excel file")));
}
else
{
string query = string.Format("Select * FROM [{0}]", sheets.FirstOrDefault());
oda = new OleDbDataAdapter(query, excelConnection);
oda.Fill(ds);
DataTable Exceldt = ds.Tables[0];
string sqlconn = ConfigurationManager.ConnectionStrings["dbcon"].ConnectionString;
con = new SqlConnection(sqlconn);
//creating object of SqlBulkCopy
objbulk = new SqlBulkCopy(con);
//assigning Destination table name
objbulk.DestinationTableName = "EMS_Leave_Balance_Temp";
//Mapping Table column
objbulk.ColumnMappings.Add("Employee Id", "Employee_id");
objbulk.ColumnMappings.Add("CL", "CL");
objbulk.ColumnMappings.Add("EL", "EL");
objbulk.ColumnMappings.Add("ML", "ML");
objbulk.ColumnMappings.Add("LOP", "LOP");
objbulk.ColumnMappings.Add("WFH", "WFH");
//inserting Datatable Records to DataBase
con.Open();
objbulk.WriteToServer(Exceldt);
con.Close();
flag = true;
}
if (flag)
{
if (!CommonRepo.LoadDataFromTable())
{
return(Request.CreateResponse(HttpStatusCode.OK, new EMSResponseMessage("EMS_305", "Some problem in Stored Procedure", "Some problem in Stored Procedure")));
}
}
return(Request.CreateResponse(HttpStatusCode.OK, new EMSResponseMessage("EMS_306", "Leave Balance updated successfully", "Leave Balance updated successfully")));
}
else
{
return(Request.CreateResponse(HttpStatusCode.OK, new EMSResponseMessage("EMS_307", "Invalid File", "Invalid File")));
}
}
catch (OleDbException e)
{
Debug.WriteLine(e.Message);
Debug.WriteLine(e.GetBaseException());
return(Request.CreateResponse(HttpStatusCode.OK, new EMSResponseMessage("EMS_308", "Invalid Excel Sheet", e.Message)));
}
catch (InvalidOperationException e)
{
Debug.WriteLine(e.Message);
Debug.WriteLine(e.GetBaseException());
return(Request.CreateResponse(HttpStatusCode.OK, new EMSResponseMessage("EMS_308", "Mandatory columns are missing", e.Message)));
}
catch (IOException IOException)
{
Debug.WriteLine(IOException.Message);
Debug.WriteLine(IOException.GetBaseException());
return(Request.CreateResponse(HttpStatusCode.OK, new EMSResponseMessage("EMS_308", "Please select a file to upload", IOException.Message)));
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
Debug.WriteLine(e.GetBaseException());
return(Request.CreateResponse(HttpStatusCode.OK, new EMSResponseMessage("EMS_308", "Application Error", e.Message)));
}
}
