private bool InsertData(string path)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] { new DataColumn("Year"), new DataColumn("Tesco_WK"), new DataColumn("Period_StartDate"), new DataColumn("Period_EndDate"), new DataColumn("Tesco_FY"), new DataColumn("Tesco_Period") });
//Response.Write(path);
main_function PPHfunction = new main_function();
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + path + " ; Extended Properties=Excel 8.0;";
string connectionStringXLSX = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + path + " ; Extended Properties=\"Excel 12.0;IMEX=1;HDR=Yes;TypeGuessRows=0;ImportMixedTypes=Text\"";
connectionString = (path.IndexOf("xlsx") > 0) ? connectionStringXLSX : connectionString;
OleDbConnection conn = new OleDbConnection(connectionString);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
try
{
#region Insert
string sql = "select * from [DateWeek$]";
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataReader drRead = cmd.ExecuteReader();
while (drRead.Read())
{
if (drRead[0].ToString() != "" && drRead[1].ToString() != "")
{
string strBetweenS, strBetweenE, strBetween;
string[] arrSDate = drRead[2].ToString().Split('/'); strBetweenS = arrSDate[1] + " " + PPHfunction.chrShortMonth(arrSDate[0]) + " " + arrSDate[2].Substring(2, 2);
string[] arrESDate = drRead[3].ToString().Split('/'); strBetweenE = arrESDate[1] + " " + PPHfunction.chrShortMonth(arrESDate[0]) + " " + arrESDate[2].Substring(2, 2);
string insert_dayweek = drRead[1].ToString();
if (insert_dayweek.Length == 1)
{
insert_dayweek = "0" + insert_dayweek;
}
strBetween = strBetweenS + " - " + strBetweenE;
PPHfunction.QueryExecuteNonQuery("insert into Date_Week_Info (Tesco_Year, Tesco_Week, Period_StartDate, Period_EndDate, Between_Date, Tesco_FY, Tesco_Period) values ('" + drRead[0] + "','" + insert_dayweek + "','" + drRead[2] + "','" + drRead[3] + "','" + strBetween + "', '" + drRead[4] + "', '" + drRead[5] + "')");
}
}
conn.Close();
return(true);
#endregion
}
catch (Exception ex)
{
conn.Close();
PrimaryHaul_WS.PH_ExceptionManager.WriteError(ex.Message);
return(false);
}
}
protected void Page_Load(object sender, EventArgs e)
{
main_function PPHfunction = new main_function();
string strS, strE, strBetweenS, strBetweenE, strBetween;
if (!string.IsNullOrEmpty(Request.Form["var01"] as string))
{
string[] arrSDate = Request.Form["var01"].ToString().Split('/'); strS = "'" + arrSDate[2] + "-" + arrSDate[1] + "-" + arrSDate[0] + "'"; strBetweenS = arrSDate[0] + " " + PPHfunction.chrShortMonth(arrSDate[1]) + " " + arrSDate[2].Substring(2, 2);
}
else
{
strS = "NULL"; strBetweenS = "";
}
if (!string.IsNullOrEmpty(Request.Form["var02"] as string))
{
string[] arrESDate = Request.Form["var02"].ToString().Split('/'); strE = "'" + arrESDate[2] + "-" + arrESDate[1] + "-" + arrESDate[0] + "'"; strBetweenE = arrESDate[0] + " " + PPHfunction.chrShortMonth(arrESDate[1]) + " " + arrESDate[2].Substring(2, 2);
}
else
{
strE = "NULL"; strBetweenE = "";
}
strBetween = strBetweenS + " - " + strBetweenE;
PPHfunction.QueryExecuteNonQuery("update Date_Week_Info set Period_StartDate=" + strS + ", Period_EndDate=" + strE + ", Between_Date='" + strBetween + "', Tesco_Year='" + Request.Form["var04"].ToString() + "', Tesco_Week='" + Request.Form["var05"].ToString() + "', Tesco_FY='" + Request.Form["var06"].ToString() + "', Tesco_Period='" + Request.Form["var07"].ToString() + "' where Wk_ID='" + Request.Form["var03"].ToString() + "'");
}