protected void ButtonLoc_Click(object sender, EventArgs e)
{
var datestring = datepickerLocal.Text;
var dt = DateTime.ParseExact(datestring, @"MM/dd/yyyy", CultureInfo.InvariantCulture);
if (dt < DateTime.Today)
{
lblmessageLoc.Text = "You must choose a date in the future";
}
else
{
var connectionString = "";
try
{
if (FileUploadLoc.HasFile)
{
if (ValidFile(FileUploadLoc, "LOCAL"))
{
var fileName = Path.GetFileName(FileUploadLoc.PostedFile.FileName);
var fileExtension = Path.GetExtension(FileUploadLoc.PostedFile.FileName);
var fileLocation = Server.MapPath("~/App_Data/" + fileName);
FileUploadLoc.SaveAs(fileLocation);
connectionString = UploadExcel(connectionString, fileExtension, fileLocation, "LOCAL");
}
}
else
{
lblmessageLoc.Text = "Please select an Excel file to upload.";
}
}
catch (Exception ex)
{
lblmessageLoc.Text = "Invalid Excel document.";
//lblmessageLoc.Text = ex.Message.ToString();
}
}
}
protected void ButtonLoc_Click(object sender, EventArgs e)
{
var connectionString = "";
try
{
if (FileUploadLoc.HasFile)
{
if (ValidExport(FileUploadLoc))
{
var fileName = Path.GetFileName(FileUploadLoc.PostedFile.FileName);
var fileExtension = Path.GetExtension(FileUploadLoc.PostedFile.FileName);
var fileLocation = Server.MapPath("~/App_Data/" + fileName);
FileUploadLoc.SaveAs(fileLocation);
//Check whether file extension is xls or xslx
if (fileExtension == ".xls")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation +
";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=2\"";
}
else if (fileExtension == ".xlsx")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation +
";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=2\"";
}
//Create OleDB Connection and OleDb Command
var con = new OleDbConnection(connectionString);
var cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
var dAdapter = new OleDbDataAdapter(cmd);
var dtExcelRecords = new DataTable();
con.Open();
var dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
var getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [LOCAL$A3:H] WHERE F1 IS NOT NULL";
dAdapter.SelectCommand = cmd;
var dsbd = new DataSet();
dAdapter.Fill(dsbd);
var status = SaveLocal(dsbd);
if (status == 1)
{
GridLocal.DataBind();
if (GridLocal.Rows.Count >= 1)
{
//show grid results
lblmessageLoc.Text = GridLocal.Rows.Count + " Duplicates detected.";
var connectLoc1 =
ConfigurationManager.ConnectionStrings["SecureConnectionString"].ConnectionString;
var deleteLocal1 = "DELETE FROM LocalQueue";
using (var myLocConnection1 = new SqlConnection(connectLoc1))
{
myLocConnection1.Open();
var myLocCommand1 = new SqlCommand(deleteLocal1, myLocConnection1);
myLocCommand1.ExecuteNonQuery();
myLocConnection1.Close();
}
}
else
{
//Fill dataset from staging table
var adapterDb = new SqlDataAdapter("Select * FROM LocalQueue", strConnection);
var datasetDb = new DataSet();
adapterDb.Fill(datasetDb);
//Check integer status returned
var newstatus = SaveNewLocal(datasetDb);
if (newstatus == 1)
{
//Delete localQueue
var connectLoc =
ConfigurationManager.ConnectionStrings["SecureConnectionString"]
.ConnectionString;
var deleteLocal = "DELETE FROM LocalQueue";
using (var myLocConnection = new SqlConnection(connectLoc))
{
myLocConnection.Open();
var myLocCommand = new SqlCommand(deleteLocal, myLocConnection);
myLocCommand.ExecuteNonQuery();
myLocConnection.Close();
}
lblmessageLoc.Text = "Local Queue updated successfully.";
}
}
}
else
{
lblmessageLoc.Text = "Upload Error.Please try again";
}
con.Close();
}
}
else
{
lblmessageLoc.Text = "Please select an Excel file to upload.";
}
}
catch (Exception ex)
{
lblmessageLoc.Text = "Invalid Excel document.";
}
}