protected void ButtonExp_Click(object sender, EventArgs e)
{
var datestring = datepickerExport.Text;
var dt = DateTime.ParseExact(datestring, @"MM/dd/yyyy", CultureInfo.InvariantCulture);
if (dt < DateTime.Today)
{
lblmessageExp.Text = "You must choose a date in the future";
}
else
{
var connectionString = "";
try
{
if (FileUploadExp.HasFile)
{
if (ValidFile(FileUploadExp, "EXPORT"))
{
var fileName = Path.GetFileName(FileUploadExp.PostedFile.FileName);
var fileExtension = Path.GetExtension(FileUploadExp.PostedFile.FileName);
var fileLocation = Server.MapPath("~/App_Data/" + fileName);
FileUploadExp.SaveAs(fileLocation);
connectionString = UploadExcel(connectionString, fileExtension, fileLocation, "EXPORT");
}
}
else
{
lblmessageExp.Text = "Please select an Excel file to upload.";
}
}
catch (Exception ex)
{
lblmessageExp.Text = "Invalid Excel document.";
}
}
}
protected void ButtonExp_Click(object sender, EventArgs e)
{
var connectionString = "";
try
{
if (FileUploadExp.HasFile)
{
if (ValidExport(FileUploadExp))
{
var fileName = Path.GetFileName(FileUploadExp.PostedFile.FileName);
var fileExtension = Path.GetExtension(FileUploadExp.PostedFile.FileName);
var fileLocation = Server.MapPath("~/App_Data/" + fileName);
FileUploadExp.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 [EXPORT$A3:G] WHERE F1 IS NOT NULL";
dAdapter.SelectCommand = cmd;
var dsbd = new DataSet();
dAdapter.Fill(dsbd);
var status = SaveExport(dsbd);
if (status == 1)
{
GridExp.DataBind();
if (GridExp.Rows.Count >= 1)
{
//show grid results
lblmessageExp.Text = GridExp.Rows.Count + " Duplicates detected.";
var connectExp1 =
ConfigurationManager.ConnectionStrings["SecureConnectionString"].ConnectionString;
var deleteExport1 = "DELETE FROM ExportQueue";
using (var myExpConnection1 = new SqlConnection(connectExp1))
{
myExpConnection1.Open();
var myExpCommand1 = new SqlCommand(deleteExport1, myExpConnection1);
myExpCommand1.ExecuteNonQuery();
myExpConnection1.Close();
}
}
else
{
//Fill dataset from staging table
var adapterDb = new SqlDataAdapter("Select * FROM ExportQueue", strConnection);
var datasetDb = new DataSet();
adapterDb.Fill(datasetDb);
//Check integer status returned
var newstatus = SaveNewExport(datasetDb);
if (newstatus == 1)
{
//Delete exportQueue
var connectExp =
ConfigurationManager.ConnectionStrings["SecureConnectionString"]
.ConnectionString;
var deleteExport = "DELETE FROM ExportQueue";
using (var myExpConnection = new SqlConnection(connectExp))
{
myExpConnection.Open();
var myExpCommand = new SqlCommand(deleteExport, myExpConnection);
myExpCommand.ExecuteNonQuery();
myExpConnection.Close();
}
lblmessageExp.Text = "Export Queue updated successfully.";
}
}
}
else
{
lblmessageExp.Text = "Upload Error.Please try again";
}
con.Close();
}
}
else
{
lblmessageExp.Text = "Please select an Excel file to upload.";
}
}
catch (Exception ex)
{
lblmessageExp.Text = "Invalid Excel document.";
}
}