private static async Task XMLToDatabase() { //throw new NotImplementedException(); FileInfo xFile = new FileInfo("Request_Definition.xml"); if (!xFile.Exists) { throw new Exception("XML File not exist."); } await Task.Run(() => { XElement xRequest = XElement.Load(xFile.FullName); //Debug.Print(xRequest.ToString()); //string Method, Input Address $H$51, //return value DateTime xDate(string Address) { IEnumerable <XElement> ieFilter = from XElement x in xRequest.Elements() where x.HasAttributes select x; IEnumerable <string> ieRangeVal = from XElement x in ieFilter where x.Attribute("CellAddress").Value == Address && x.Attribute("Type").Value == "xlRange" select x.Attribute("CellValue").Value; int FoundNode = ieRangeVal.Count(); if (FoundNode != 1) { return(DateTime.Parse("1900-01-01")); } string DateString = ieRangeVal.First(); bool IsDate = DateTime.TryParse(DateString, out DateTime result); if (IsDate) { return(result); } else { return(DateTime.Parse("1900-01-01")); } } string xRange(string Address) { IEnumerable <XElement> ieFilter = from XElement x in xRequest.Elements() where x.HasAttributes select x; IEnumerable <string> ieRangeVal = from XElement x in ieFilter where x.Attribute("CellAddress").Value == Address && x.Attribute("Type").Value == "xlRange" select x.Attribute("CellValue").Value; int FoundNode = ieRangeVal.Count(); if (FoundNode != 1) { return("未入力"); } return(ieRangeVal.First()); } //string Method, Input Checkbox Area $H$42:$K$43 //No, there is no need string xCheck(string Address) { CellRange TargetCellArea = new CellRange(Address); string AddressRange = TargetCellArea.Extend(); //Check if any of that is in the IEnumerable <XElement> ieFilter = from XElement x in xRequest.Elements() where x.HasAttributes select x; //Debug.Print(AddressRange); IEnumerable <string> ieCheckVal = from XElement x in ieFilter where AddressRange.Contains(x.Attribute("CellAddress").Value) && x.Attribute("Type").Value == "CheckBox" select x.Attribute("CellValue").Value; int ValueFound = ieCheckVal.Count(); switch (ValueFound) { case 0: return("未入力"); case 1: return(ieCheckVal.First()); default: return("無効な入力"); } } // Get Cell Value: xRange("$H$8")); // Get Checkbox Value: xCheck("$H$34:$K$36")); // Connect to the Database and sync it using (OleDbConnection conn = DatabaseConnection()) { conn.StateChange += (object sender, StateChangeEventArgs e) => Debug.Print("Database Status Changed from {0} to {1}" , e.OriginalState, e.CurrentState); OleDbCommand INSERT_Host = new OleDbCommand(); OleDbCommand INSERT_Excel = new OleDbCommand(); OleDbCommand INSERT_Agent = new OleDbCommand(); OleDbTransaction TransactAll = null; conn.Open(); TransactAll = conn.BeginTransaction(IsolationLevel.ReadCommitted); INSERT_Host.Connection = conn; INSERT_Host.Transaction = TransactAll; INSERT_Agent = INSERT_Host.Clone(); INSERT_Excel = INSERT_Host.Clone(); //Make 3 Commands, one for each column //return value is rows affected. int MakeCommandForServer(string StartAddress) { Regex ValidHostname = new Regex(@"(\w|\d){8,}\.?"); CellRange caStart = new CellRange(StartAddress); string MA_Hostname = xRange(caStart.Extend()); Match mhValidHost = ValidHostname.Match(MA_Hostname); if (mhValidHost.Success) { INSERT_Host.CommandText = File.ReadAllText(SQLFolder + "INSERTHost.sql"); INSERT_Host.Parameters.AddWithValue("@ostname", MA_Hostname); INSERT_Host.Parameters.AddWithValue("@PAddress", xRange(caStart.NextRow())); if (caStart.InitRange().Contains("49")) //M/Agent Range at 49 is VIP { INSERT_Host.Parameters.AddWithValue("@aker", "VIP"); INSERT_Host.Parameters.AddWithValue("@odel", "VIP"); INSERT_Host.Parameters.AddWithValue("@PUCount", "VIP"); INSERT_Host.Parameters.AddWithValue("@PUMicroprocessor", "VIP"); INSERT_Host.Parameters.AddWithValue("@S", "VIP"); INSERT_Host.Parameters.AddWithValue("@ersion", "VIP"); INSERT_Host.Parameters.AddWithValue("@itVal", "VIP"); INSERT_Host.Parameters.AddWithValue("@lusterBox", "VIP"); INSERT_Host.Parameters.AddWithValue("@lusterIndex", "VIP"); } else { INSERT_Host.Parameters.AddWithValue("@aker", xRange(caStart.NextRow())); INSERT_Host.Parameters.AddWithValue("@odel", xRange(caStart.NextRow())); INSERT_Host.Parameters.AddWithValue("@PUCount", xRange(caStart.NextRow())); INSERT_Host.Parameters.AddWithValue("@PUMicroprocessor", xRange(caStart.NextRow())); INSERT_Host.Parameters.AddWithValue("@S", xCheck(caStart.NextArea(3))); INSERT_Host.Parameters.AddWithValue("@ersion", xRange(caStart.NextRow())); INSERT_Host.Parameters.AddWithValue("@itVal", xRange(caStart.NextRow())); INSERT_Host.Parameters.AddWithValue("@lusterBox", xRange(caStart.NextRow())); INSERT_Host.Parameters.AddWithValue("@lusterIndex", xRange(caStart.NextRow())); } //Debug.Print("Parameters Before Clear: {0}", INSERT_Host.Parameters.Count); int RowsAffected = INSERT_Host.ExecuteNonQuery(); INSERT_Host.Parameters.Clear(); return(RowsAffected); } return(0); } int MakeCommandforAgent(char column) { if (!char.IsLetter(column)) { throw new Exception("Invalid Column Character"); } Regex ValidHostname = new Regex(@"(\w|\d){8,}\.?"); INSERT_Agent.CommandText = File.ReadAllText(SQLFolder + "INSERTAgent.sql"); //Agent Name is like uny40310.abc00101 //If VIP Exist, use VIP; If VIP not exist, use Pri //Validate VIP At 49, and PRI at 51 string magentName = ""; string tempRange = string.Format(@"${0}$49", column); if (ValidHostname.IsMatch(xRange(tempRange))) { magentName = xRange(tempRange); } tempRange = string.Format(@"${0}$51", column); if (ValidHostname.IsMatch(xRange(tempRange))) { magentName = xRange(tempRange); } if (magentName == "") { throw new Exception("Invalid MA Column, magentName Failed to caputre."); } tempRange = string.Format(@"${0}$98", column); string mserverName = xRange(tempRange); string AgentName = string.Format(@"{0}.{1}" , ControlMServer.Convert(mserverName), magentName); INSERT_Agent.Parameters.AddWithValue("@AgentName", AgentName); INSERT_Agent.Parameters.AddWithValue("@rlnFileName", xRequest.Element("xlname").Value); tempRange = string.Format(@"${0}$31", column); CellRange cell = new CellRange(tempRange); INSERT_Agent.Parameters.AddWithValue("@ApplyType", xCheck(cell.NextArea(2))); INSERT_Agent.Parameters.AddWithValue("@ChangePoint", xCheck(cell.NextArea(3))); INSERT_Agent.Parameters.AddWithValue("@SIer", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@ServerPIC", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@SystemID", xRange(cell.NextRow())); //7 INSERT_Agent.Parameters.AddWithValue("@SystemName", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@SystemSubName", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@NetworkLocation", xCheck(cell.NextArea(2))); INSERT_Agent.Parameters.AddWithValue("@NetworkArea", xCheck(cell.NextArea(4))); //11 INSERT_Agent.Parameters.AddWithValue("@ServerVIP", xRange(cell.NextRow(2))); INSERT_Agent.Parameters.AddWithValue("@ServerPRI", xRange(cell.NextRow(2))); INSERT_Agent.Parameters.AddWithValue("@ServerSEC", xRange(cell.NextRow(13))); //14 INSERT_Agent.Parameters.AddWithValue("@MStMACommunicationPort", xRange(cell.NextRow(13))); INSERT_Agent.Parameters.AddWithValue("@MA_InstallDate", xDate(cell.NextRow())); //16 INSERT_Agent.Parameters.AddWithValue("@MS_Connection", xDate(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@JobStartDate", xDate(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@JobCount", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@HasCallorder", xCheck(cell.NextArea(1))); INSERT_Agent.Parameters.AddWithValue("@HasFirewall", xCheck(cell.NextArea(1))); //21 INSERT_Agent.Parameters.AddWithValue("@MA_Version", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@IsFirstTime", xCheck(cell.NextArea(1))); INSERT_Agent.Parameters.AddWithValue("@IsProduction", xCheck(cell.NextArea(1))); INSERT_Agent.Parameters.AddWithValue("@TestDoneDate", xDate(cell.NextRow())); //25 INSERT_Agent.Parameters.AddWithValue("@CostFrom", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@CostFromSystemName", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@CostFromSubSystemName", xRange(cell.NextRow())); //28 INSERT_Agent.Parameters.AddWithValue("@HasSundayJobs", xCheck(cell.NextArea(1))); INSERT_Agent.Parameters.AddWithValue("@HasRelatedSystems", xCheck(cell.NextArea(1))); INSERT_Agent.Parameters.AddWithValue("@RelatedSystemID", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@RelatedSystemName", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@RelatedSystemSubName", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@RelatedSystemDatacenter", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@MAtMSCommunicationPort", xRange(cell.NextRow())); //30 INSERT_Agent.Parameters.AddWithValue("@MSVIP", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@MSPRI", xRange(cell.NextRow())); INSERT_Agent.Parameters.AddWithValue("@MSSEC", xRange(cell.NextRow())); //33 int RowsAffected = INSERT_Agent.ExecuteNonQuery(); INSERT_Agent.Parameters.Clear(); return(RowsAffected); /* * @AgentName, @rlnFileName, @ApplyType, @ChangePoint, @SIer, @ServerPIC, * @SystemID, @SystemName, @SystemSubName, @NetworkLocation, @NetworkArea, * @ServerVIP, @ServerPRI, @ServerSEC, @MStMACommunicationPort, @MA_InstallDate, * @MS_Connection, @JobStartDate, @JobCount, @HasCallorder, @HasFirewall, @MA_Version, * @IsFirstTime, @IsProduction, @TestDoneDate, @CostFrom, @CostFromSystemName, * @CostFromSubSystemName, @HasSundayJobs, @HasRelatedSystems, @RelatedSystemID, * @RelatedSystemName, @RelatedSystemSubName, @RelatedSystemDatacenter, * @MAtMSCommunicationPort, @MSVIP, @MSPRI, @MSSEC 38 */ } // Sync the information of the new task // return values is rows affected int MakeCommandforExcel() { INSERT_Excel.CommandText = File.ReadAllText(SQLFolder + "INSERTExcel.sql"); INSERT_Excel.Parameters.AddWithValue("@lname", xRequest.Element("xlname").Value); CellRange caStart = new CellRange("$H$7"); string applydate = xRange(caStart.EndinRange()); Debug.Print(applydate); INSERT_Excel.Parameters.AddWithValue("@ate_apply", DateTime.Parse(applydate)); INSERT_Excel.Parameters.AddWithValue("@pplier", xRange(caStart.NextRow())); INSERT_Excel.Parameters.AddWithValue("@ailaddress", xRange(caStart.NextRow())); INSERT_Excel.Parameters.AddWithValue("@honenumber", xRange(caStart.NextRow())); INSERT_Excel.Parameters.AddWithValue("@pprover", xRange(caStart.NextRow())); INSERT_Excel.Parameters.AddWithValue("@obcon_accept", "陳 黙"); INSERT_Excel.Parameters.AddWithValue("@obcon_confirm", "徐 長練"); INSERT_Excel.Parameters.AddWithValue("@obcon_approve", "孫 紅莉"); INSERT_Excel.Parameters.AddWithValue("@pecialcomment", xRange("$E$161")); Debug.Print("INSERT_Excel Parameters Count: {0}", INSERT_Excel.Parameters.Count); //Debug.Print(INSERT_Excel.CommandText); return(INSERT_Excel.ExecuteNonQuery()); }//(@lname, @ate_apply, @pplier, @ailaddress, @honenumber, @pprover, @obcon_accept, @obcon_confirm, @obcon_approve, @pecialcomment); if (MakeCommandForServer("$H$49") == 1 || MakeCommandForServer("$H$51") == 1 || MakeCommandForServer("$H$64") == 1) { MakeCommandforAgent('H'); } if (MakeCommandForServer("$L$49") == 1 || MakeCommandForServer("$L$51") == 1 || MakeCommandForServer("$L$64") == 1) { MakeCommandforAgent('L'); } if (MakeCommandForServer("$P$49") == 1 || MakeCommandForServer("$P$51") == 1 || MakeCommandForServer("$P$64") == 1) { MakeCommandforAgent('P'); } MakeCommandforExcel(); TransactAll.Commit(); } }); }