private void InsertData(string path)
{
try
{
PHDS_BHUpload.BH_Transaction_TMPDataTable dtBHTrans = new PHDS_BHUpload.BH_Transaction_TMPDataTable();
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + path + " ; Extended Properties='Excel 8.0;IMEX=1;HDR=Yes;TypeGuessRows=0;ImportMixedTypes=Text;'";
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.ToLower().IndexOf("xlsx") > 0) ? connectionStringXLSX : connectionString;
OleDbConnection conn = new OleDbConnection(connectionString);
if (conn.State == ConnectionState.Open) conn.Close();
conn.Open();
try
{
lblErr.Text = "";
bool isSheetName = false;
int strUserId = int.Parse(Request["id"]);
string strSheet = "data wk" + lblWeek.Text.Substring(4,2);
string fileName = path.Split('\\').Length > 0 ? path.Split('\\')[path.Split('\\').Length - 1] : "";
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow drSheet in dtSheet.Rows)
{
if (drSheet["TABLE_NAME"].ToString().Replace("'", "").Replace("$", "").ToLower() == strSheet.ToLower()) isSheetName = true;
}
if (isSheetName)
{
#region Insert
//for (int index = 0; index < 2; index++)
//foreach (DataRow drSheet in dtSheet.Rows)
//{
int index = 0;
int rowCount = 0, rowError = 0, rowSucc = 0;
try
{
//strSheet = "bh_transaction";
string sql = "select * from [" + strSheet + "$]";
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataReader drRead = cmd.ExecuteReader();
PHDS_BHUpload.BH_Transaction_TMPRow dr = null;
string strColumns = "";
while (drRead.Read())
{
if (!string.IsNullOrEmpty(drRead[0].ToString().Trim()))
{
if (drRead[0].ToString().Trim().ToLower() != "week"){
#region Insert Row
try
{
index++;
dr = dtBHTrans.NewBH_Transaction_TMPRow();
dr.Week = drRead[0].ToString();
dr.Vendor_Name = drRead[2].ToString();
try
{
dr.Appt_Date = DateTime.ParseExact(drRead[3].ToString().Trim().Split(' ')[0], "M/d/yyyy", null); //drRead[3].ToString().Trim().Split(' ')[0];//
}
catch
{
dr.Appt_Date = DateTime.ParseExact(drRead[3].ToString().Trim().Split(' ')[0], "d/M/yyyy", null); //drRead[3].ToString().Trim().Split(' ')[0];//
}
dr.PO_No = drRead[6].ToString();
dr.Appt_To_DC = drRead[9].ToString();
dr.Appt_No = drRead[11].ToString();
dr.Remark = drRead[14].ToString();
dr.File_Name = fileName;
dr.UserID = strUserId;
dr.StampTime = DateTime.Now;
dr.Week_Upload = lblWeek.Text;
dr.Week_OnFile = lblWeek.Text;
dr.Period = !Regex.IsMatch(drRead[4].ToString(), @"\d") ? 0 : int.Parse(drRead[1].ToString());
strColumns = "Period";
dr.Load_Appt = !Regex.IsMatch(drRead[4].ToString(), @"\d") ? 0 : int.Parse(drRead[4].ToString());
strColumns = "Load_Appt";
dr.Load_Rcvd = !Regex.IsMatch(drRead[5].ToString(), @"\d") ? 0 : int.Parse(drRead[5].ToString());
strColumns = "Load_Rcvd";
dr.DC_No = !Regex.IsMatch(drRead[7].ToString(), @"\d") ? 0 : int.Parse(drRead[7].ToString());
strColumns = "DC_No";
dr.Load_No = !Regex.IsMatch(drRead[8].ToString(), @"\d") ? 0 : int.Parse(drRead[8].ToString());
strColumns = "Load_No";
dr.Type = !Regex.IsMatch(drRead[10].ToString(), @"\d") ? 0 : int.Parse(drRead[10].ToString());
strColumns = "Type";
dr.Case_Appt = !Regex.IsMatch(drRead[12].ToString(), @"\d") ? 0 : Convert.ToInt32(decimal.Parse(drRead[12].ToString()));
strColumns = "Case_Appt";
//dr.Pallet = !Regex.IsMatch(drRead[13].ToString(), @"\d") ? 0 : decimal.Parse(drRead[13].ToString());
dr.Pallet = drRead[13].ToString();
strColumns = "Pallet";
dr.Pallet_From_Vendor = !Regex.IsMatch(drRead[15].ToString(), @"\d") ? 0 : decimal.Parse(drRead[15].ToString());
strColumns = "Pallet_From_Vendor";
dr.Total_Pallet_From_Vendor = !Regex.IsMatch(drRead[16].ToString(), @"\d") ? 0 : int.Parse(drRead[16].ToString());
strColumns = "Total_Pallet_From_Vendor";
dr.Rate = !Regex.IsMatch(drRead[17].ToString(), @"\d") ? 0 : decimal.Parse(drRead[17].ToString());
strColumns = "Rate";
dr.Rate_Unloading = !Regex.IsMatch(drRead[18].ToString(), @"\d") ? 0 : int.Parse(drRead[18].ToString());
strColumns = "Rate_Unloading";
dr.Fuel_Rate = !Regex.IsMatch(drRead[20].ToString(), @"\d") ? 0 : decimal.Parse(drRead[20].ToString());
dtBHTrans.Rows.Add(dr);
//PH_BHUpload.PH_BHTransaction_InsertTMP(AppCode.strConnDB, dr);
if (PPH_BH.insert_bhtransactonTMP(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"], dr.Week_Upload, dr.Week, dr.Period, dr.Vendor_Name, dr.Appt_Date, dr.Load_Appt, dr.Load_Rcvd, dr.PO_No, dr.DC_No, dr.Load_No, dr.Appt_To_DC, dr.Type, dr.Appt_No, dr.Case_Appt, dr.Pallet, dr.Remark, dr.Pallet_From_Vendor, dr.Total_Pallet_From_Vendor, dr.Rate, dr.Rate_Unloading, dr.File_Name, dr.UserID,dr.Fuel_Rate) == true)
{
}
rowSucc++;
}
catch (Exception ex)
{
rowError++;
dr.Status = "err";
//dr.Remark2 = ex.Message;
PH_ExceptionManager.WriteError("prepare data BHUpload >> row index : " + index.ToString() + " Columns Err : " + strColumns + "err message : " + ex.Message);
}
}
#endregion
}
}
}
catch (Exception ex) { lblErr.Text += ex.Message; ; PH_ExceptionManager.WriteError("Verlify Data >>" + " Row Index : " + index.ToString() + " err message : " + ex.Message); }
//}
DataBind_BHTrans(strUserId.ToString());
//if (rowError == 0)
//{
btnInsert.Enabled = true;
//}
btnClear.Enabled = true;
lblErr.Text = " Success=" + rowSucc.ToString() + " Error=" + rowError.ToString();
#endregion
}
else
{
lblErr.Text = "Sheet name incorrect";
}
}
catch (Exception ex)
{
lblErr.Text = ex.Message;
PH_ExceptionManager.WriteError("Verlify Data >>" + ex.Message);
}
finally
{
// lblErr.Text = "";
}
}
catch (Exception ex)
{
throw new Exception("InsertData>>" + ex.Message);
}
}
private void InsertData(string path)
{
try
{
PHDS_BHUpload.BH_Transaction_TMPDataTable dtBHTrans = new PHDS_BHUpload.BH_Transaction_TMPDataTable();
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + path + " ; Extended Properties='Excel 8.0;IMEX=1;HDR=Yes;TypeGuessRows=0;ImportMixedTypes=Text;'";
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.ToLower().IndexOf("xlsx") > 0) ? connectionStringXLSX : connectionString;
OleDbConnection conn = new OleDbConnection(connectionString);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
try
{
lblErr.Text = "";
bool isSheetName = false;
int strUserId = int.Parse(Request["id"]);
string strSheet = "data wk" + lblWeek.Text.Substring(4, 2);
string fileName = path.Split('\\').Length > 0 ? path.Split('\\')[path.Split('\\').Length - 1] : "";
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow drSheet in dtSheet.Rows)
{
if (drSheet["TABLE_NAME"].ToString().Replace("'", "").Replace("$", "").ToLower() == strSheet.ToLower())
{
isSheetName = true;
}
}
if (isSheetName)
{
#region Insert
//for (int index = 0; index < 2; index++)
//foreach (DataRow drSheet in dtSheet.Rows)
//{
int index = 0;
int rowCount = 0, rowError = 0, rowSucc = 0;
try
{
//strSheet = "bh_transaction";
string sql = "select * from [" + strSheet + "$]";
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataReader drRead = cmd.ExecuteReader();
PHDS_BHUpload.BH_Transaction_TMPRow dr = null;
string strColumns = "";
while (drRead.Read())
{
if (!string.IsNullOrEmpty(drRead[0].ToString().Trim()))
{
if (drRead[0].ToString().Trim().ToLower() != "week")
{
#region Insert Row
try
{
index++;
dr = dtBHTrans.NewBH_Transaction_TMPRow();
dr.Week = drRead[0].ToString();
dr.Vendor_Name = drRead[2].ToString();
try
{
dr.Appt_Date = DateTime.ParseExact(drRead[3].ToString().Trim().Split(' ')[0], "M/d/yyyy", null); //drRead[3].ToString().Trim().Split(' ')[0];//
}
catch
{
dr.Appt_Date = DateTime.ParseExact(drRead[3].ToString().Trim().Split(' ')[0], "d/M/yyyy", null); //drRead[3].ToString().Trim().Split(' ')[0];//
}
dr.PO_No = drRead[6].ToString();
dr.Appt_To_DC = drRead[9].ToString();
dr.Appt_No = drRead[11].ToString();
dr.Remark = drRead[14].ToString();
dr.File_Name = fileName;
dr.UserID = strUserId;
dr.StampTime = DateTime.Now;
dr.Week_Upload = lblWeek.Text;
dr.Week_OnFile = lblWeek.Text;
dr.Period = !Regex.IsMatch(drRead[4].ToString(), @"\d") ? 0 : int.Parse(drRead[1].ToString());
strColumns = "Period";
dr.Load_Appt = !Regex.IsMatch(drRead[4].ToString(), @"\d") ? 0 : int.Parse(drRead[4].ToString());
strColumns = "Load_Appt";
dr.Load_Rcvd = !Regex.IsMatch(drRead[5].ToString(), @"\d") ? 0 : int.Parse(drRead[5].ToString());
strColumns = "Load_Rcvd";
dr.DC_No = !Regex.IsMatch(drRead[7].ToString(), @"\d") ? 0 : int.Parse(drRead[7].ToString());
strColumns = "DC_No";
dr.Load_No = !Regex.IsMatch(drRead[8].ToString(), @"\d") ? 0 : int.Parse(drRead[8].ToString());
strColumns = "Load_No";
dr.Type = !Regex.IsMatch(drRead[10].ToString(), @"\d") ? 0 : int.Parse(drRead[10].ToString());
strColumns = "Type";
dr.Case_Appt = !Regex.IsMatch(drRead[12].ToString(), @"\d") ? 0 : Convert.ToInt32(decimal.Parse(drRead[12].ToString()));
strColumns = "Case_Appt";
//dr.Pallet = !Regex.IsMatch(drRead[13].ToString(), @"\d") ? 0 : decimal.Parse(drRead[13].ToString());
dr.Pallet = drRead[13].ToString();
strColumns = "Pallet";
dr.Pallet_From_Vendor = !Regex.IsMatch(drRead[15].ToString(), @"\d") ? 0 : decimal.Parse(drRead[15].ToString());
strColumns = "Pallet_From_Vendor";
dr.Total_Pallet_From_Vendor = !Regex.IsMatch(drRead[16].ToString(), @"\d") ? 0 : int.Parse(drRead[16].ToString());
strColumns = "Total_Pallet_From_Vendor";
dr.Rate = !Regex.IsMatch(drRead[17].ToString(), @"\d") ? 0 : decimal.Parse(drRead[17].ToString());
strColumns = "Rate";
dr.Rate_Unloading = !Regex.IsMatch(drRead[18].ToString(), @"\d") ? 0 : int.Parse(drRead[18].ToString());
strColumns = "Rate_Unloading";
dr.Fuel_Rate = !Regex.IsMatch(drRead[20].ToString(), @"\d") ? 0 : decimal.Parse(drRead[20].ToString());
dtBHTrans.Rows.Add(dr);
//PH_BHUpload.PH_BHTransaction_InsertTMP(AppCode.strConnDB, dr);
if (PPH_BH.insert_bhtransactonTMP(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"], dr.Week_Upload, dr.Week, dr.Period, dr.Vendor_Name, dr.Appt_Date, dr.Load_Appt, dr.Load_Rcvd, dr.PO_No, dr.DC_No, dr.Load_No, dr.Appt_To_DC, dr.Type, dr.Appt_No, dr.Case_Appt, dr.Pallet, dr.Remark, dr.Pallet_From_Vendor, dr.Total_Pallet_From_Vendor, dr.Rate, dr.Rate_Unloading, dr.File_Name, dr.UserID, dr.Fuel_Rate) == true)
{
}
rowSucc++;
}
catch (Exception ex)
{
rowError++;
dr.Status = "err";
//dr.Remark2 = ex.Message;
PH_ExceptionManager.WriteError("prepare data BHUpload >> row index : " + index.ToString() + " Columns Err : " + strColumns + "err message : " + ex.Message);
}
}
#endregion
}
}
}
catch (Exception ex) { lblErr.Text += ex.Message;; PH_ExceptionManager.WriteError("Verlify Data >>" + " Row Index : " + index.ToString() + " err message : " + ex.Message); }
//}
DataBind_BHTrans(strUserId.ToString());
//if (rowError == 0)
//{
btnInsert.Enabled = true;
//}
btnClear.Enabled = true;
lblErr.Text = " Success=" + rowSucc.ToString() + " Error=" + rowError.ToString();
#endregion
}
else
{
lblErr.Text = "Sheet name incorrect";
}
}
catch (Exception ex)
{
lblErr.Text = ex.Message;
PH_ExceptionManager.WriteError("Verlify Data >>" + ex.Message);
}
finally
{
// lblErr.Text = "";
}
}
catch (Exception ex)
{
throw new Exception("InsertData>>" + ex.Message);
}
}
