static void Main(string[] args) { //sql connection string string connectionString = @"server=;database=;integrated security=SSPI;"; #region US Reports Load //Reports string ReportUrl1 = "https:"; string ReportUrl2 = "https:"; string ReportUrl3 = "https:"; string ReportUrl4 = "https:"; string Url5 = "https:"; //Load US Reports try { //server uses TLS1.2 secuirty protocal ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12; CiscoDataLoad.InsertIntoTable1(ReportUrl1, connectionString); CiscoDataLoad.InsertIntoTable2(ReportUrl2, connectionString); CiscoDataLoad.InsertIntoTable3(ReportUrl3, connectionString); CiscoDataLoad.InsertIntoTable4(ReportUrl4, connectionString); CiscoDataLoad.InsertIntoTable5(Url5, connectionString); } catch (Exception ex) { LogControl.Write("Reports Inserted failed with error\r\n" + ex.Message); } #endregion #region EMEA Reports //Reports string DailyReportUrl21 = ""; string ReportUrl22 = ""; string ReportUrl23 = ""; string ReportUrl24 = ""; try { //ignore unverfied certificate warning from server for reports ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12 | SecurityProtocolType.Ssl3; CiscoDataLoad.InsertIntoTable1(DailyReportUrl21, connectionString); CiscoDataLoad.InsertIntoTable2(ReportUrl22, connectionString); CiscoDataLoad.InsertIntoTable3(ReportUrl23, connectionString); CiscoDataLoad.InsertIntoTable4(ReportUrl24, connectionString); } catch (Exception ex) { LogControl.Write("Reports Inserted failed with error\r\n" + ex.Message); } #endregion }
public static void InsertIntoTable5(string reporturl, string connectionString) { //Source: Report 5 //Destination: Table 5 string insertQuery = @"INSERT INTO dbo.table5(c1, c2, c3, c4, c5, c6) VALUES(@c1, @c2, @c3, @c4, @c5, @c6);"; //Load XML file into report variable XmlDocument document = new XmlDocument(); document.Load(reporturl); var report = document.LastChild; using (SqlConnection conn = new SqlConnection(connectionString)) using (SqlCommand insertCmd = new SqlCommand(insertQuery, conn)) { try { // define parameters insertCmd.Parameters.Add("@c1", SqlDbType.DateTime); insertCmd.Parameters.Add("@c2", SqlDbType.VarChar, 64); insertCmd.Parameters.Add("@c3", SqlDbType.Int); insertCmd.Parameters.Add("@c4", SqlDbType.Int); insertCmd.Parameters.Add("@c5", SqlDbType.Int); insertCmd.Parameters.Add("@c6", SqlDbType.Int); // open connection, loop over file 'rows' and 'column' tags, execute query to insert data one row at a time conn.Open(); //parse XML file to extract row/col values foreach (XmlNode row in report.ChildNodes) { DateTime?c1 = null; string c2 = ""; int c3 = 0; int c4 = 0; int c5 = 0; int c6 = 0; foreach (XmlElement col in row) { switch (col.Attributes["name"].Value) { case "c1": c1 = Convert.ToDateTime(col.InnerText); break; case "c2": c2 = col.InnerText; break; case "c3": c3 = Convert.ToInt32(col.InnerText); break; case "c4": c4 = Convert.ToInt32(col.InnerText); break; case "c5": c5 = Convert.ToInt32(col.InnerText); break; case "c6": c6 = Convert.ToInt32(col.InnerText); break; default: break; } } insertCmd.Parameters["@c1"].Value = c1; insertCmd.Parameters["@c2"].Value = c2; insertCmd.Parameters["@c3"].Value = c3; insertCmd.Parameters["@c4"].Value = c4; insertCmd.Parameters["@c5"].Value = c5; insertCmd.Parameters["@c6"].Value = c6; insertCmd.ExecuteNonQuery(); LogControl.Write("Data Inserted into Table Successfully"); } conn.Close(); } catch (Exception e) { LogControl.Write("Error at Insert into Table \r\n" + e.Message); } } }
public static void InsertIntoTable2(string reporturl, string connectionString) { //Source: Report2 //Destination: Table2 string insertQuery = @"INSERT INTO dbo.table2(col1, col2, col3, col4) VALUES(@col1, @col2, @col3, @col4);"; //Load XML file into report variable XmlDocument document = new XmlDocument(); document.Load(reporturl); var report = document.LastChild; using (SqlConnection conn = new SqlConnection(connectionString)) using (SqlCommand insertCmd = new SqlCommand(insertQuery, conn)) { try { // define parameters insertCmd.Parameters.Add("@col1", SqlDbType.VarChar, 64); insertCmd.Parameters.Add("@col2", SqlDbType.Date); insertCmd.Parameters.Add("@col3", SqlDbType.VarChar, 64); insertCmd.Parameters.Add("@col4", SqlDbType.Int); // open connection, loop over file 'rows' and 'column' tags, execute query to insert data one row at a time conn.Open(); foreach (XmlNode row in report.ChildNodes) { string col1 = ""; DateTime?col2 = null; string col3 = ""; int col4 = 0; foreach (XmlElement col in row) { switch (col.Attributes["name"].Value) { case "col1": col1 = col.InnerText; break; case "col2": col2 = Convert.ToDateTime(col.InnerText); break; case "col3": col3 = col.InnerText; break; case "col4": col4 = Convert.ToInt32(col.InnerText); break; default: break; } } insertCmd.Parameters["@col1"].Value = col1; insertCmd.Parameters["@col2"].Value = col2; insertCmd.Parameters["@col3"].Value = col3; insertCmd.Parameters["@col4"].Value = col4; insertCmd.ExecuteNonQuery(); LogControl.Write("nserted into Table Successfully"); } conn.Close(); } catch (Exception e) { LogControl.Write("Error at Insert into Table\r\n" + e.Message); } } }