public void SyncCusteelData(StringBuilder sbSync) { var auth = new authorization { username = "******", password = "******" }; var dcmClient = new DataCenterMTOMVerifyServiceClient("DataCenterMTOMVerifyServiceImplPort"); //dcmClient.register(auth); var custeelTargetInsertRows = SyncCusteelTarget(auth, dcmClient); sbSync.Append("Table CusteelTarget Insert Rows:" + custeelTargetInsertRows + "\n"); var ds = new DataSet(); using (var con = new OracleConnection(Connectionstr)) { var cmd = new OracleCommand("SELECT code FROM CusteelTargets ", con); var da = new OracleDataAdapter(cmd); da.Fill(ds); } var count = ds.Tables[0].Rows.Count; int insertCount = 0; int updateCount = 0; for (int i = 0; i < count; i++) { var code = ds.Tables[0].Rows[i][0]; var dtbytes = dcmClient.getDataSet(code.ToString(), auth); string str = Encoding.UTF8.GetString(dtbytes); var xml = new XmlDocument(); xml.LoadXml(str); var tbXmlNode = xml.SelectSingleNode("//TABLE"); var tbElement = (XmlElement)tbXmlNode; if (tbElement != null) { var desetElement = (XmlElement)xml.SelectSingleNode("//DBSET"); if (desetElement != null) { var xnl = xml.SelectNodes("//ROW"); if (xnl != null) { using (var con = new OracleConnection(Connectionstr)) { con.Open(); OracleCommand cmd = new OracleCommand("", con); foreach (var value in xnl) { var xe = (XmlElement)value; var xnls = xe.ChildNodes; var dbSetId = desetElement.GetAttribute("ID"); var time = xnls[0].InnerText; var price = xnls[1].InnerText; string existStr = "select count(*) from CUSTEELDATA where code='" + code + "' and TIME='" + time + "'"; cmd.CommandText = existStr; int cmdresult = 0; try { object obj = cmd.ExecuteScalar(); if (Equals(obj, null) || (Equals(obj, DBNull.Value))) { } else { cmdresult = int.Parse(obj.ToString()); } } catch (OracleException e) { con.Close(); throw new Exception(e.Message); } string operationSql; if (cmdresult == 0) { insertCount++; operationSql = "insert into CUSTEELDATA values('" + code + "','" + dbSetId + "','" + time + "','" + price + "',sysdate)"; } else { updateCount++; operationSql = "update CUSTEELDATA set dbSetId='" + dbSetId + "',price='" + price + "',SYNCTIME=sysdate where code='" + code + "' and time='" + time + "'"; } cmd.CommandText = operationSql; try { cmd.ExecuteNonQuery(); } catch (OracleException e) { con.Close(); throw new Exception(e.Message); } } con.Close(); } } } } } sbSync.Append("Table CUSTEELDATA Insert Rows:" + insertCount + ",Update Rows:" + updateCount + " \n"); }
public int SyncCusteelTarget(authorization auth, DataCenterMTOMVerifyServiceClient dcmClient) { var bytes = dcmClient.getTargets(auth); string str = Encoding.UTF8.GetString(bytes); var xml = new XmlDocument(); xml.LoadXml(str); var targetsNodes = xml.SelectNodes("//targets"); int insertCount = 0; //int updateCount = 0; if (targetsNodes != null) { using (var con = new OracleConnection(Connectionstr)) { con.Open(); foreach (var targetsNode in targetsNodes) { var targetsName = ((XmlElement)targetsNode).GetAttribute("name"); var nodes = ((XmlElement)targetsNode).ChildNodes; foreach (var node in nodes) { var targetName = ((XmlElement)node).GetAttribute("name"); var code = ((XmlElement)node).GetAttribute("code"); string existStr = "select count(*) from CUSTEELTARGETS where code='" + code + "'"; int cmdresult = 0; using (OracleCommand cmd = new OracleCommand(existStr, con)) { try { object obj = cmd.ExecuteScalar(); if ((Equals(obj, null)) || (Equals(obj, DBNull.Value))) { } else { cmdresult = int.Parse(obj.ToString()); } } catch (OracleException e) { con.Close(); throw new Exception(e.Message); } string operationSql; if (cmdresult == 0) { insertCount++; operationSql = "insert into CUSTEELTARGETS values('" + targetsName + "','" + targetName + "','" + code + "',sysdate)"; } else { continue; //updateCount++; //operationSql = "update CUSTEELTARGETS set targetsName='" + targetsName + "',targetName='" + targetName + "',SYNCTIME=sysdate where code='" + code + "'"; } cmd.CommandText = operationSql; try { cmd.ExecuteNonQuery(); } catch (OracleException e) { con.Close(); throw new Exception(e.Message); } } } } con.Close(); } } return(insertCount); }