public bool UpdateDB_ExcelData(DataTable sites, out string message)
{
SiteRepository siteRep = new SiteRepository();
if (!sites.Columns.Contains("Result"))
{
sites.Columns.Add("Result", typeof(string));
}
foreach (DataRow row in sites.Rows)
{
Site site = new Site();
site.RegionId = siteRep.GetRegionId(row["Region"].ToString().Trim());
site.SiteName = row["Site_Name"].ToString().Trim();
site.SiteCode = row["Site_Code"].ToString().Trim();
site.CityId = siteRep.GetCityId(row["City"].ToString().Trim(), site.RegionId);
site.ZoneId = siteRep.GetCityZoneId(row["City_zone"].ToString().Trim(), site.CityId);
site.SiteType = siteRep.GetTypeId(row["Type"].ToString().Trim());
if (row["BSC"].ToString().Trim() == "#N/A" || row["BSC"].ToString().Trim() == "" || row["BSC"].ToString().Trim() == "NaN")
{
site.ControlerId2g = 0;
}
else
{
site.ControlerId2g = siteRep.GetCOntrollerId_2G(row["BSC"].ToString());
}
if (row["RNC"].ToString().Trim() == "#N/A" || row["RNC"].ToString().Trim() == "" || row["RNC"].ToString().Trim() == "NaN")
{
site.ControlerId3g = 0;
}
else
{
site.ControlerId3g = siteRep.GetCOntrollerId_3G(row["RNC"].ToString());
}
if (row["LTE_City"].ToString().Trim() == "#N/A" || (row["LTE_City"].ToString().Trim() == "") || (row["LTE_City"].ToString().Trim() == "NaN"))
{
site.ControlerId4g = 0;
}
else
{
site.ControlerId4g = siteRep.GetCOntrollerId_4G(row["LTE_City"].ToString());
}
site._2g = row["2G"] == null ? false : (row["2G"].ToString() == "1");
site._3g = row["3G"] == null ? false : (row["3G"].ToString() == "1");
site._4g = row["LTE"] == null ? false : (row["LTE"].ToString() == "1");
if (string.IsNullOrEmpty(site.SiteCode))
{
row["Result"] = "Site code must not equal null";
continue;
}
if (string.IsNullOrEmpty(site.SiteName))
{
row["Result"] = "Site name must not equal null";
continue;
}
if (site.RegionId < 0)
{
row["Result"] = "Missing region";
continue;
}
if (site.CityId < 0)
{
row["Result"] = "Missing city";
continue;
}
if (site.ZoneId < 0)
{
row["Result"] = "Missing zone";
continue;
}
if (site._2g && site.ControlerId2g == 0)
{
row["Result"] = "Missing controller 2G name";
continue;
}
if (site._3g && site.ControlerId3g == 0)
{
row["Result"] = "Missing controller 3G name";
continue;
}
if (site._4g && site.ControlerId4g == 0)
{
row["Result"] = "Missing controller 4G name";
continue;
}
int check = siteRep.CheckSiteExist(row["Site_Code"].ToString());
if (check > -1)
{
site.SiteId = check;
bool updated = siteRep.UpdateSite(site);
if (updated)
{
row["Result"] = "Updated successfully";
}
else
{
row["Result"] = "";
}
}
else
{
int inserted = siteRep.InsertNewSite(site);
if (inserted != -1)
{
row["Result"] = "Inserted successfully";
}
else
{
row["Result"] = "";
}
}
}
message = "Done";
lblUploadResult.Text = message;
gvSites.DataBind();
EppLusExcel down = new EppLusExcel();
down.ExporttoExcel(sites, "Sheet1", "SitesUploadResult");
return(true);
}