private void InsertButton_Click(object sender, RoutedEventArgs e)
{
if (dgvExcelToDb.ItemsSource != null)
{
tempProductTable = productDAL.BuildProductSchema();
List <Product> productList = new List <Product>();
if (tablesToInsert[cmbSheet.SelectedItem.ToString()] != null && tablesToInsert[cmbSheet.SelectedItem.ToString()].Rows.Count > 0)
{
foreach (DataRow dr in tablesToInsert[cmbSheet.SelectedItem.ToString()].Rows)
{
//Only take the values where product code is not an empty string
//NOTE: product code is present in 5th column of the excel sheet so we are checking columnNamesExcel[4]
if (dr[columnNamesExcel[4]] != null && !string.IsNullOrWhiteSpace(dr[columnNamesExcel[4]].ToString()))
{
DataRow tempProductDr = tempProductTable.NewRow();
tempProductDr["product_type"] = dr[columnNamesExcel[0]]?.ToString() ?? "";
tempProductDr["brand_code"] = dr[columnNamesExcel[1]]?.ToString() ?? "";
tempProductDr["delivery_agent"] = dr[columnNamesExcel[2]]?.ToString() ?? "";
tempProductDr["vendor"] = dr[columnNamesExcel[3]]?.ToString() ?? "";
tempProductDr["product_code"] = dr[columnNamesExcel[4]]?.ToString() ?? "";
// ternary operator used to fix the problem of converting whitespace or blank values in database
tempProductDr["unit_price_INR"] = (dr[columnNamesExcel[5]] != null && !string.IsNullOrWhiteSpace(dr[columnNamesExcel[5]]?.ToString())) ? float.Parse(dr[columnNamesExcel[5]].ToString()) : (float)0;
tempProductDr["unit_price_NPR"] = (dr[columnNamesExcel[6]] != null && !string.IsNullOrWhiteSpace(dr[columnNamesExcel[6]]?.ToString())) ? float.Parse(dr[columnNamesExcel[6]].ToString()) : (float)0;
tempProductDr["total_unit_in"] = (dr[columnNamesExcel[7]] != null && !string.IsNullOrWhiteSpace(dr[columnNamesExcel[7]]?.ToString())) ? float.Parse(dr[columnNamesExcel[7]].ToString()) : (float)0;
tempProductDr["remaining_unit"] = (dr[columnNamesExcel[8]] != null && !string.IsNullOrWhiteSpace(dr[columnNamesExcel[8]]?.ToString())) ? float.Parse(dr[columnNamesExcel[8]].ToString()) : (float)0;
tempProductDr["carrier_charge_unit"] = (dr[columnNamesExcel[9]] != null && !string.IsNullOrWhiteSpace(dr[columnNamesExcel[9]]?.ToString())) ? float.Parse(dr[columnNamesExcel[9]].ToString()) : (float)0;
tempProductDr["total_cost_per_unit"] = (dr[columnNamesExcel[10]] != null && !string.IsNullOrWhiteSpace(dr[columnNamesExcel[10]]?.ToString())) ? float.Parse(dr[columnNamesExcel[10]].ToString()) : (float)0;
tempProductDr["selling_price"] = (dr[columnNamesExcel[11]] != null && !string.IsNullOrWhiteSpace(dr[columnNamesExcel[11]]?.ToString())) ? float.Parse(dr[columnNamesExcel[11]].ToString()) : (float)0;
tempProductDr["added_date"] = DateTime.Now;
tempProductTable.Rows.Add(tempProductDr);
// code commented out below uses Method to make a list of product and add them to DB, not using this method as we are using dataTable schema method, where we create an inmemeory database and
//put values in it directly, the inmemeory database method is used so that we can use the bulk insert method to insert all the data at once
//Product product = new Product();
//product.productType = dr[columnNamesExcel[0]].ToString();
//product.brandCode = dr[columnNamesExcel[1]].ToString();
//product.deliveryAgent = dr[columnNamesExcel[2]].ToString();
//product.vendor = dr[columnNamesExcel[3]].ToString();
//product.productCode = dr[columnNamesExcel[4]].ToString();
//product.unitPriceINR = float.Parse(dr[columnNamesExcel[5]].ToString());
//product.unitPriceNPR = float.Parse(dr[columnNamesExcel[6]].ToString());
//product.totalUnitIn = float.Parse(dr[columnNamesExcel[7]].ToString());
//product.remainingUnit = float.Parse(dr[columnNamesExcel[8]].ToString());
//product.carrierChargePerUnit = float.Parse(dr[columnNamesExcel[9]].ToString());
//product.totalCostPerUnit = float.Parse(dr[columnNamesExcel[10]].ToString());
//product.sellingPrice = float.Parse(dr[columnNamesExcel[11]].ToString());
//product.addedDate = DateTime.Now;
//productList.Add(product);
}
else
{
continue;
}
}
//bulk insert to product table
if (productDAL.BulkInsertTable(tempProductTable))
{
MessageBox.Show("Import data from Excel to Database successfull");
}
else
{
MessageBox.Show("Could Not Import Data From Excel To Database!. Please try again", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
else
{
MessageBox.Show("No data present!", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
columnNamesExcel.Clear();
tempProductTable.Clear();
}