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);
}
}
}