コード例 #1
0
        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);
        }
コード例 #2
0
        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);
        }