public XmlElement getXMLSheet(string select, string pk, string fk) { bfAdmin = new BenefitAdminDataContext(); XmlDocument xmlDoc = new XmlDocument(); XmlElement dataElm = xmlDoc.CreateElement("DATA"); xmlDoc.AppendChild(dataElm); List <string> dataPK = this.queryPK(pk); if (!select.Equals("All")) { string tmp = ""; foreach (string a in dataPK) { if (a.Trim().Equals(select)) { tmp = a.Trim(); } } dataPK.Clear(); dataPK.Add(tmp); } foreach (string stringPK in dataPK) { XmlElement root = xmlDoc.CreateElement(pk); root.SetAttribute("code", stringPK.Trim()); dataElm.AppendChild(root); if (fk.Equals("Activity") && pk.Equals("Service")) #region { var data = from m in bfAdmin.Activities where stringPK.Trim().Equals(m.SVCCode.Trim()) select m; foreach (var s in data) { XmlElement act = xmlDoc.CreateElement("Activity"); root.AppendChild(act); XmlElement ACTCode = xmlDoc.CreateElement("ACTCode"); ACTCode.InnerXml = s.ACTCode.Trim(); act.AppendChild(ACTCode); XmlElement ACTDesc = xmlDoc.CreateElement("ACTDesc"); ACTDesc.InnerXml = s.ACTDesc.Trim(); act.AppendChild(ACTDesc); XmlElement SVCCode = xmlDoc.CreateElement("SVCCode"); SVCCode.InnerXml = s.SVCCode.Trim(); act.AppendChild(SVCCode); XmlElement ICF_Code = xmlDoc.CreateElement("ICF_Code"); ICF_Code.InnerXml = s.ICF_Code.Trim(); act.AppendChild(ICF_Code); } } #endregion else if (fk.Equals("SubActivity") && pk.Equals("Activity")) #region { var data = from m in bfAdmin.SubActivities where stringPK.Trim().Equals(m.ACTCode.Trim()) select m; foreach (var s in data) { XmlElement sact = xmlDoc.CreateElement("SubActivity"); root.AppendChild(sact); XmlElement SACTCode = xmlDoc.CreateElement("SACTCode"); SACTCode.InnerXml = s.SACTCode.Trim(); sact.AppendChild(SACTCode); XmlElement SACTDesc = xmlDoc.CreateElement("SACTDesc"); SACTDesc.InnerXml = s.SACTDesc.Trim(); sact.AppendChild(SACTDesc); XmlElement ACTCode = xmlDoc.CreateElement("ACTCode"); ACTCode.InnerXml = s.ACTCode.Trim(); sact.AppendChild(ACTCode); } } #endregion else if (fk.Equals("Material") && pk.Equals("Service")) #region { var data = from m in bfAdmin.Materials where stringPK.Trim().Equals(m.SVCCode.Trim()) select m; foreach (var s in data) { XmlElement mat = xmlDoc.CreateElement("Material"); root.AppendChild(mat); XmlElement MaterialCode = xmlDoc.CreateElement("MaterialCode"); MaterialCode.InnerXml = s.MaterialCode.Trim(); mat.AppendChild(MaterialCode); XmlElement MaterialDesc = xmlDoc.CreateElement("MaterialDesc"); MaterialDesc.InnerXml = s.MaterialDesc.Trim(); mat.AppendChild(MaterialDesc); XmlElement Unit = xmlDoc.CreateElement("Unit"); Unit.InnerXml = s.Unit.Trim(); mat.AppendChild(Unit); XmlElement EstimatedPrice = xmlDoc.CreateElement("EstimatedPrice"); EstimatedPrice.InnerXml = s.EstimatedPrice.Trim(); mat.AppendChild(EstimatedPrice); XmlElement RealPrice = xmlDoc.CreateElement("RealPrice"); RealPrice.InnerXml = s.RealPrice.Trim(); mat.AppendChild(RealPrice); XmlElement SVCCode = xmlDoc.CreateElement("SVCCode"); SVCCode.InnerXml = s.SVCCode.Trim(); mat.AppendChild(SVCCode); XmlElement Note = xmlDoc.CreateElement("Note"); Note.InnerXml = s.Note.Trim(); mat.AppendChild(Note); } } #endregion } return(xmlDoc.DocumentElement); }
/* getXMLSheet : returen specific XmlElement of sheet * input : name of organization and name of sheet * : name of organization and string "All" * : name of organization and string "List" * output : XmlElement specific by input * contract string string -> XmlElement * */ public XmlElement getXMLSheet(string group, string sheet) { bfAdmin = new BenefitAdminDataContext(); XmlDocument xmlDoc = new XmlDocument(); //init filepath and read xml document #region if (group.ToUpper().Equals("CODE")) { if (sheet.ToUpper().Equals("ALL")) { return(getXmlDocumentFromDB(xmlCode).DocumentElement); } else { xmlDoc = getXmlDocumentFromDB(xmlCode); } } else if (group.ToUpper().Equals("COST")) { if (sheet.ToUpper().Equals("ALL")) { return(getXmlDocumentFromDB(xmlCost).DocumentElement); } else { xmlDoc = getXmlDocumentFromDB(xmlCost); } } #endregion XmlNodeList nodes = xmlDoc.GetElementsByTagName(group.ToUpper()); if (!sheet.ToUpper().Equals("LIST")) { foreach (XmlNode node in nodes.Item(0).ChildNodes) { if (node.Name.Equals(sheet)) { return((XmlElement)node); } } return(null); } else { if (group.ToUpper().Equals("COST")) { XmlDocument doc = new XmlDocument(); XmlElement root = doc.CreateElement("Lists"); doc.AppendChild(root); XmlElement list = doc.CreateElement("SheetName"); list.InnerText = "Annotation"; root.AppendChild(list); list = doc.CreateElement("SheetName"); list.InnerText = "ActivityCost"; root.AppendChild(list); return(doc.DocumentElement); } else if (group.ToUpper().Equals("CODE")) { XmlDocument doc = new XmlDocument(); XmlElement root = doc.CreateElement("Lists"); doc.AppendChild(root); XmlElement list = doc.CreateElement("SheetName"); list.InnerText = "Service"; root.AppendChild(list); list = doc.CreateElement("SheetName"); list.InnerText = "Activity"; root.AppendChild(list); list = doc.CreateElement("SheetName"); list.InnerText = "SubActivity"; root.AppendChild(list); list = doc.CreateElement("SheetName"); list.InnerText = "Material"; root.AppendChild(list); return(doc.DocumentElement); } } return(null); }
public static string SaveData(string data) { data = data.Replace("&", "&"); MemoryStream mem = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(data)); XPathDocument document; try { document = new XPathDocument(mem); } catch (Exception) { return("error"); } BenefitAdminDataContext bfAdmin = new BenefitAdminDataContext(); var allInSvr = from c in bfAdmin.Services select c; bfAdmin.Services.DeleteAllOnSubmit(allInSvr); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } var allInAct = from c in bfAdmin.Activities select c; bfAdmin.Activities.DeleteAllOnSubmit(allInAct); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } var allInSAct = from c in bfAdmin.SubActivities select c; bfAdmin.SubActivities.DeleteAllOnSubmit(allInSAct); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } var allInMat = from c in bfAdmin.Materials select c; bfAdmin.Materials.DeleteAllOnSubmit(allInMat); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } XPathNavigator navigator = document.CreateNavigator(); XPathNodeIterator allDocs = navigator.Select("DOCUMENTS/DOCUMENT"); while (allDocs.MoveNext()) { string title = allDocs.Current.GetAttribute("title", ""); if (title.Equals("Service")) { #region string col0 = "", col1 = "", col2 = "", col3 = "", col4 = "", col5 = ""; string col6 = "", col7 = "", col8 = "", col9 = "", col10 = ""; XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS"); countRowString.MoveNext(); int countRow = Convert.ToInt32(countRowString.Current.Value); XPathNodeIterator rows = allDocs.Current.Select("DATA"); while (rows.MoveNext()) { for (int i = 1; i < countRow; i++) { XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0"); getCol0.MoveNext(); col0 = getCol0.Current.Value; XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1"); getCol1.MoveNext(); col1 = getCol1.Current.Value; XPathNodeIterator getCol2 = rows.Current.Select("R" + i + "/C2"); getCol2.MoveNext(); col2 = getCol2.Current.Value; XPathNodeIterator getCol3 = rows.Current.Select("R" + i + "/C3"); getCol3.MoveNext(); col3 = getCol3.Current.Value; XPathNodeIterator getCol4 = rows.Current.Select("R" + i + "/C4"); getCol4.MoveNext(); col4 = getCol4.Current.Value; XPathNodeIterator getCol5 = rows.Current.Select("R" + i + "/C5"); getCol5.MoveNext(); col5 = getCol5.Current.Value; XPathNodeIterator getCol6 = rows.Current.Select("R" + i + "/C6"); getCol6.MoveNext(); col6 = getCol6.Current.Value; XPathNodeIterator getCol7 = rows.Current.Select("R" + i + "/C7"); getCol7.MoveNext(); col7 = getCol7.Current.Value; XPathNodeIterator getCol8 = rows.Current.Select("R" + i + "/C8"); getCol8.MoveNext(); col8 = getCol8.Current.Value; XPathNodeIterator getCol9 = rows.Current.Select("R" + i + "/C9"); getCol9.MoveNext(); col9 = getCol9.Current.Value; XPathNodeIterator getCol10 = rows.Current.Select("R" + i + "/C10"); getCol10.MoveNext(); col10 = getCol10.Current.Value; //insert to DB if (!col0.Equals("")) { Service svr = new Service(); svr.SVCCode = col0; svr.SVCName = col1; svr.SVCDesc = col2; svr.HostCode = col3; svr.StaffRole = col4; svr.SVCType = col5; svr.SVCObjective = col6; svr.SVCSupport = col7; svr.SVCCoverage = col8; svr.SVCStart = col9; svr.SVCEnd = col10; bfAdmin.Services.InsertOnSubmit(svr); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } } } #endregion } else if (title.Equals("Activity")) { #region string col0 = "", col1 = "", col2 = "", col3 = ""; XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS"); countRowString.MoveNext(); int countRow = Convert.ToInt32(countRowString.Current.Value); XPathNodeIterator rows = allDocs.Current.Select("DATA"); while (rows.MoveNext()) { for (int i = 1; i < countRow; i++) { XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0"); getCol0.MoveNext(); col0 = getCol0.Current.Value; XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1"); getCol1.MoveNext(); col1 = getCol1.Current.Value; XPathNodeIterator getCol2 = rows.Current.Select("R" + i + "/C2"); getCol2.MoveNext(); col2 = getCol2.Current.Value; XPathNodeIterator getCol3 = rows.Current.Select("R" + i + "/C3"); getCol3.MoveNext(); col3 = getCol3.Current.Value; //insert to DB if (!col0.Equals("") && !col2.Equals("")) { Activity act = new Activity(); act.ACTCode = col0; act.ACTDesc = col1; act.SVCCode = col2; act.ICF_Code = col3; bfAdmin.Activities.InsertOnSubmit(act); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } } } #endregion } else if (title.Equals("SubActivity")) { #region string col0 = "", col1 = "", col2 = ""; XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS"); countRowString.MoveNext(); int countRow = Convert.ToInt32(countRowString.Current.Value); XPathNodeIterator rows = allDocs.Current.Select("DATA"); while (rows.MoveNext()) { for (int i = 1; i < countRow; i++) { XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0"); getCol0.MoveNext(); col0 = getCol0.Current.Value; XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1"); getCol1.MoveNext(); col1 = getCol1.Current.Value; XPathNodeIterator getCol2 = rows.Current.Select("R" + i + "/C2"); getCol2.MoveNext(); col2 = getCol2.Current.Value; //insert to DB if (!col0.Equals("") && !col2.Equals("")) { SubActivity sact = new SubActivity(); sact.SACTCode = col0; sact.SACTDesc = col1; sact.ACTCode = col2; bfAdmin.SubActivities.InsertOnSubmit(sact); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } } } #endregion } else if (title.Equals("Material")) { #region string col0 = "", col1 = "", col2 = "", col3 = "", col4 = "", col5 = "", col6 = ""; XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS"); countRowString.MoveNext(); int countRow = Convert.ToInt32(countRowString.Current.Value); XPathNodeIterator rows = allDocs.Current.Select("DATA"); while (rows.MoveNext()) { for (int i = 1; i < countRow; i++) { XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0"); getCol0.MoveNext(); col0 = getCol0.Current.Value; XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1"); getCol1.MoveNext(); col1 = getCol1.Current.Value; XPathNodeIterator getCol2 = rows.Current.Select("R" + i + "/C2"); getCol2.MoveNext(); col2 = getCol2.Current.Value; XPathNodeIterator getCol3 = rows.Current.Select("R" + i + "/C3"); getCol3.MoveNext(); col3 = getCol3.Current.Value; XPathNodeIterator getCol4 = rows.Current.Select("R" + i + "/C4"); getCol4.MoveNext(); col4 = getCol4.Current.Value; XPathNodeIterator getCol5 = rows.Current.Select("R" + i + "/C5"); getCol5.MoveNext(); col5 = getCol5.Current.Value; XPathNodeIterator getCol6 = rows.Current.Select("R" + i + "/C6"); getCol6.MoveNext(); col6 = getCol6.Current.Value; //insert to DB if (!col0.Equals("") && !col5.Equals("")) { Material mt = new Material(); mt.MaterialCode = col0; mt.MaterialDesc = col1; mt.Unit = col2; mt.EstimatedPrice = col3; mt.RealPrice = col4; mt.SVCCode = col5; mt.Note = col6; bfAdmin.Materials.InsertOnSubmit(mt); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } } } #endregion } } return(null); }
private void excelToDB(DataTable data) { //clean DB #region if (RadioButtonList_type.SelectedIndex == 1) //selecte create new sheet { if (DropDownListTo.SelectedIndex == 0) { var allInSvr = from c in bfAdmin.Services select c; bfAdmin.Services.DeleteAllOnSubmit(allInSvr); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } else if (DropDownListTo.SelectedIndex == 1) { var allInAct = from c in bfAdmin.Activities select c; bfAdmin.Activities.DeleteAllOnSubmit(allInAct); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } else if (DropDownListTo.SelectedIndex == 2) { var allInSAct = from c in bfAdmin.SubActivities select c; bfAdmin.SubActivities.DeleteAllOnSubmit(allInSAct); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } else { var allInMat = from c in bfAdmin.Materials select c; bfAdmin.Materials.DeleteAllOnSubmit(allInMat); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } } #endregion if (DropDownListTo.SelectedIndex == 0) { int skip = 0; foreach (DataRow row in data.Rows) { if (row[0].ToString() != null && skip > 0) { Service svr = new Service(); try { svr.SVCCode = row[0].ToString(); svr.SVCName = row[1].ToString() == null ? "" : row[1].ToString(); svr.SVCDesc = row[2].ToString() == null ? "" : row[2].ToString(); svr.HostCode = row[3].ToString() == null ? "" : row[3].ToString(); svr.StaffRole = row[4].ToString() == null ? "" : row[4].ToString(); svr.SVCType = row[5].ToString() == null ? "" : row[5].ToString(); svr.SVCObjective = row[6].ToString() == null ? "" : row[6].ToString(); svr.SVCSupport = row[7].ToString() == null ? "" : row[7].ToString(); svr.SVCCoverage = row[8].ToString() == null ? "" : row[8].ToString(); svr.SVCStart = row[9].ToString() == null ? "" : row[9].ToString(); svr.SVCEnd = row[10].ToString() == null ? "" : row[10].ToString(); } catch { } var Svr = from c in bfAdmin.Services where c.SVCCode.ToString().Trim().Equals(row[0].ToString().Trim()) select c; if (Svr != null) { bfAdmin.Services.DeleteAllOnSubmit(Svr); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } bfAdmin.Services.InsertOnSubmit(svr); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } skip = 1; } } else if (DropDownListTo.SelectedIndex == 1) { int skip = 0; foreach (DataRow row in data.Rows) { if (row[0].ToString() != null && skip > 0) { Activity act = new Activity(); try { act.ACTCode = row[0].ToString(); act.ACTDesc = row[1].ToString() == null ? "" : row[1].ToString(); act.SVCCode = row[2].ToString() == null ? "" : row[2].ToString(); } catch { } var Act = from c in bfAdmin.Activities where c.ACTCode.ToString().Trim().Equals(row[0].ToString().Trim()) select c; bfAdmin.Activities.DeleteAllOnSubmit(Act); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } bfAdmin.Activities.InsertOnSubmit(act); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } skip = 1; } } else if (DropDownListTo.SelectedIndex == 2) { int skip = 0; foreach (DataRow row in data.Rows) { if (row[0].ToString() != null && skip > 0) { SubActivity sact = new SubActivity(); try { sact.SACTCode = row[0].ToString(); sact.SACTDesc = row[1].ToString() == null ? "" : row[1].ToString(); sact.ACTCode = row[2].ToString() == null ? "" : row[2].ToString(); } catch { } var Sact = from c in bfAdmin.SubActivities where c.SACTCode.ToString().Trim().Equals(row[0].ToString().Trim()) select c; bfAdmin.SubActivities.DeleteAllOnSubmit(Sact); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } bfAdmin.SubActivities.InsertOnSubmit(sact); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } skip = 1; } } else { int skip = 0; foreach (DataRow row in data.Rows) { if (row[0].ToString() != null && skip > 0) { Material mat = new Material(); try { mat.MaterialCode = row[0].ToString(); mat.MaterialDesc = row[1].ToString() == null ? "" : row[1].ToString(); mat.Unit = row[2].ToString() == null ? "" : row[2].ToString(); mat.EstimatedPrice = row[3].ToString() == null ? "" : row[3].ToString(); mat.RealPrice = row[4].ToString() == null ? "" : row[4].ToString(); mat.SVCCode = row[5].ToString() == null ? "" : row[5].ToString(); } catch { } var Mat = from c in bfAdmin.Materials where c.MaterialCode.ToString().Trim().Equals(row[0].ToString().Trim()) select c; bfAdmin.Materials.DeleteAllOnSubmit(Mat); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } bfAdmin.Materials.InsertOnSubmit(mat); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } skip = 1; } } //refresh page Response.Redirect(Request.RawUrl); }
public static string SaveData(string data) { data = data.Replace("&", "&"); MemoryStream mem = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(data)); XPathDocument document; try { document = new XPathDocument(mem); } catch (Exception) { return("error"); } BenefitAdminDataContext bfAdmin = new BenefitAdminDataContext(); var allInMis = from c in bfAdmin.Ministries select c; bfAdmin.Ministries.DeleteAllOnSubmit(allInMis); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } var allInOr = from c in bfAdmin.Organizations select c; bfAdmin.Organizations.DeleteAllOnSubmit(allInOr); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } var allInPro = from c in bfAdmin.Providers select c; bfAdmin.Providers.DeleteAllOnSubmit(allInPro); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } XPathNavigator navigator = document.CreateNavigator(); XPathNodeIterator allDocs = navigator.Select("DOCUMENTS/DOCUMENT"); while (allDocs.MoveNext()) { string title = allDocs.Current.GetAttribute("title", ""); if (title.Equals("Ministry")) { #region string col0 = "", col1 = ""; XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS"); countRowString.MoveNext(); int countRow = Convert.ToInt32(countRowString.Current.Value); XPathNodeIterator rows = allDocs.Current.Select("DATA"); while (rows.MoveNext()) { for (int i = 1; i < countRow; i++) { XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0"); getCol0.MoveNext(); col0 = getCol0.Current.Value; XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1"); getCol1.MoveNext(); col1 = getCol1.Current.Value; if (!col0.Equals("")) { Ministry min = new Ministry(); min.MinistryCode = col0; min.MinistryName = col1; bfAdmin.Ministries.InsertOnSubmit(min); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } } } #endregion } else if (title.Equals("Organization")) { #region string col0 = "", col1 = "", col2 = ""; XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS"); countRowString.MoveNext(); int countRow = Convert.ToInt32(countRowString.Current.Value); XPathNodeIterator rows = allDocs.Current.Select("DATA"); while (rows.MoveNext()) { for (int i = 1; i < countRow; i++) { XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0"); getCol0.MoveNext(); col0 = getCol0.Current.Value; XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1"); getCol1.MoveNext(); col1 = getCol1.Current.Value; XPathNodeIterator getCol2 = rows.Current.Select("R" + i + "/C2"); getCol2.MoveNext(); col2 = getCol2.Current.Value; if (!col0.Equals("")) { Organization org = new Organization(); org.OrgCode = col0; org.OrgName = col1; org.MinistryCode = col2; bfAdmin.Organizations.InsertOnSubmit(org); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } } } #endregion } else if (title.Equals("Provider")) { #region string col0 = "", col1 = "", col2 = ""; XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS"); countRowString.MoveNext(); int countRow = Convert.ToInt32(countRowString.Current.Value); XPathNodeIterator rows = allDocs.Current.Select("DATA"); while (rows.MoveNext()) { for (int i = 1; i < countRow; i++) { XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0"); getCol0.MoveNext(); col0 = getCol0.Current.Value; XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1"); getCol1.MoveNext(); col1 = getCol1.Current.Value; XPathNodeIterator getCol2 = rows.Current.Select("R" + i + "/C2"); getCol2.MoveNext(); col2 = getCol2.Current.Value; if (!col0.Equals("")) { Provider pro = new Provider(); pro.ProviderCode = col0; pro.ProviderName = col1; pro.OrgCode = col2; bfAdmin.Providers.InsertOnSubmit(pro); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } } } #endregion } } return(null); }
public static string SaveData(string data) { data = data.Replace("&", "&"); MemoryStream mem = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(data)); XPathDocument document; try { document = new XPathDocument(mem); } catch (Exception) { return("error"); } BenefitAdminDataContext bfAdmin = new BenefitAdminDataContext(); var allInAcost = from c in bfAdmin.ActivityCosts select c; bfAdmin.ActivityCosts.DeleteAllOnSubmit(allInAcost); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } var allInAnno = from c in bfAdmin.Annotations select c; bfAdmin.Annotations.DeleteAllOnSubmit(allInAnno); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } XPathNavigator navigator = document.CreateNavigator(); XPathNodeIterator allDocs = navigator.Select("DOCUMENTS/DOCUMENT"); while (allDocs.MoveNext()) { string title = allDocs.Current.GetAttribute("title", ""); if (title.Equals("Annotation")) { #region string col0 = "", col1 = "", col2 = "", col3 = ""; XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS"); countRowString.MoveNext(); int countRow = Convert.ToInt32(countRowString.Current.Value); XPathNodeIterator rows = allDocs.Current.Select("DATA"); while (rows.MoveNext()) { for (int i = 1; i < countRow; i++) { XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0"); getCol0.MoveNext(); col0 = getCol0.Current.Value; XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1"); getCol1.MoveNext(); col1 = getCol1.Current.Value; XPathNodeIterator getCol2 = rows.Current.Select("R" + i + "/C2"); getCol2.MoveNext(); col2 = getCol2.Current.Value; XPathNodeIterator getCol3 = rows.Current.Select("R" + i + "/C3"); getCol3.MoveNext(); col3 = getCol3.Current.Value; //insert to DB if (!col0.Equals("")) { Annotation anno = new Annotation(); anno.AID = col0; anno.AText = col1; anno.AnnotationID = col2; anno.Reference = col3; bfAdmin.Annotations.InsertOnSubmit(anno); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } } } #endregion } else if (title.Equals("ActivityCost")) { string col0 = "", col1 = "", col2 = "", col3 = "", col4 = "", col5 = "", col6 = "", col7 = "", col8 = "", col9 = ""; XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS"); countRowString.MoveNext(); int countRow = Convert.ToInt32(countRowString.Current.Value); XPathNodeIterator rows = allDocs.Current.Select("DATA"); while (rows.MoveNext()) { for (int i = 1; i < countRow; i++) { XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0"); getCol0.MoveNext(); col0 = getCol0.Current.Value; XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1"); getCol1.MoveNext(); col1 = getCol1.Current.Value; XPathNodeIterator getCol2 = rows.Current.Select("R" + i + "/C2"); getCol2.MoveNext(); col2 = getCol2.Current.Value; XPathNodeIterator getCol3 = rows.Current.Select("R" + i + "/C3"); getCol3.MoveNext(); col3 = getCol3.Current.Value; XPathNodeIterator getCol4 = rows.Current.Select("R" + i + "/C4"); getCol4.MoveNext(); col4 = getCol4.Current.Value; XPathNodeIterator getCol5 = rows.Current.Select("R" + i + "/C5"); getCol5.MoveNext(); col5 = getCol5.Current.Value; XPathNodeIterator getCol6 = rows.Current.Select("R" + i + "/C6"); getCol6.MoveNext(); col6 = getCol6.Current.Value; XPathNodeIterator getCol7 = rows.Current.Select("R" + i + "/C7"); getCol7.MoveNext(); col7 = getCol7.Current.Value; XPathNodeIterator getCol8 = rows.Current.Select("R" + i + "/C8"); getCol8.MoveNext(); col8 = getCol8.Current.Value; XPathNodeIterator getCol9 = rows.Current.Select("R" + i + "/C9"); getCol9.MoveNext(); col9 = getCol9.Current.Value; //insert to DB if (!col0.Equals("")) { ActivityCost acost = new ActivityCost(); acost.ACTCode = col0; acost.Unit = col1; acost.LabourCost = col2; acost.MaterialCost = col3; acost.CC_Equipment = col4; acost.CC_Building = col5; acost.IndirectCost = col6; acost.ProposedCost = col7; acost.CurrentCost = col8; acost.ReferencedCostOrg = col9; bfAdmin.ActivityCosts.InsertOnSubmit(acost); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } } } } } return(null); }
private void excelToDB(DataTable data) { //remove data if radio selected 1 if (RadioButtonList_type.SelectedIndex == 1) //selecte create new sheet { if (DropDownListTo.SelectedIndex == 0) { var allInAnno = from c in bfAdmin.Annotations select c; bfAdmin.Annotations.DeleteAllOnSubmit(allInAnno); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } else if (DropDownListTo.SelectedIndex == 1) { var allInAcost = from c in bfAdmin.ActivityCosts select c; bfAdmin.ActivityCosts.DeleteAllOnSubmit(allInAcost); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } } if (DropDownListTo.SelectedIndex == 0) { int skip = 0; foreach (DataRow row in data.Rows) { if (row[0].ToString() != null && skip > 0) { Annotation annot = new Annotation(); try { annot.AID = row[0].ToString(); annot.AText = row[1].ToString() == null ? "" : row[1].ToString(); annot.AnnotationID = row[2].ToString() == null ? "" : row[2].ToString(); annot.Reference = row[3].ToString() == null ? "" : row[3].ToString(); } catch { } var Annot = from c in bfAdmin.Annotations where c.AID.ToString().Trim().Equals(row[0].ToString().Trim()) select c; bfAdmin.Annotations.DeleteAllOnSubmit(Annot); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } bfAdmin.Annotations.InsertOnSubmit(annot); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } skip = 1; } } else if (DropDownListTo.SelectedIndex == 1) { int skip = 0; foreach (DataRow row in data.Rows) { if (row[0].ToString() != null && skip > 0) { ActivityCost act = new ActivityCost(); try { act.ACTCode = row[0].ToString(); act.Unit = row[1].ToString() == null ? "" : row[1].ToString(); act.LabourCost = row[2].ToString() == null ? "" : row[2].ToString(); act.MaterialCost = row[3].ToString() == null ? "" : row[3].ToString(); act.CC_Equipment = row[4].ToString() == null ? "" : row[4].ToString(); act.CC_Building = row[5].ToString() == null ? "" : row[5].ToString(); act.IndirectCost = row[6].ToString() == null ? "" : row[6].ToString(); act.ProposedCost = row[7].ToString() == null ? "" : row[7].ToString(); act.CurrentCost = row[8].ToString() == null ? "" : row[8].ToString(); act.ReferencedCostOrg = row[9].ToString(); } catch { } var Acost = from c in bfAdmin.ActivityCosts where c.ACTCode.ToString().Trim().Equals(row[0].ToString().Trim()) select c; bfAdmin.ActivityCosts.DeleteAllOnSubmit(Acost); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } bfAdmin.ActivityCosts.InsertOnSubmit(act); try { bfAdmin.SubmitChanges(); } catch (Exception) { bfAdmin = new BenefitAdminDataContext(); } } skip = 1; } } //refresh page Response.Redirect(Request.RawUrl); }