protected void Page_Load(object sender, EventArgs e) { string connstr = "server=MES01;user id=SAID;password=SADBA"; string deleteSql = "truncate table SA.CA_TERMINAL_IP"; string sql = string.Format(@"select s.server_desc_e,M.TERMINAL_ID,G.driver_parameter,m.device_ID from sa.tgs_gateway_base G,sa.tgs_server_base S,SA.TGS_TERMINAL_LINK M where G.server_id=S.server_id AND G.SERVER_ID=M.SERVER_ID(+) and S.SERVER_DESC_E not like 'SMT_RF_%'"); OracleConnection conn = new OracleConnection(connstr); conn.Open(); using (OracleCommand delecmd = new OracleCommand(deleteSql, conn)) { delecmd.ExecuteNonQuery(); Label1.Text = "旧数据已删除,正在进行更新表......"; } List ipList = new List(); List serverList = new List(); try { using (OracleCommand cmd = new OracleCommand(sql, conn)) { using (OracleDataAdapter da = new OracleDataAdapter(cmd)) { DataSet ds = new DataSet(); da.Fill(ds); DataTable dt = ds.Tables[0]; string server_desc_e = null; int terminal_id = 0; int device_id = 0; Byte[] driver_parameter = null; string strdriver_parameter = null; StringBuilder sb = new StringBuilder("begin "); int count = 1; string[] driverArray = null; string[] ipArray = null; for (int j = 0; j < dt.Rows.Count; j++) { if (dt.Rows[j]["device_id"] == DBNull.Value) { device_id = 1; } else { device_id = Convert.ToInt32(dt.Rows[j]["device_id"]); } server_desc_e = dt.Rows[j]["server_desc_e"].ToString(); if (dt.Rows[j]["terminal_id"] != DBNull.Value) { terminal_id = Convert.ToInt32(dt.Rows[j]["terminal_id"]); } else { terminal_id = 0; } / if (dt.Rows[j]["driver_parameter"] == DBNull.Value) { ipArray = new string[1] { "" }; } else { driver_parameter = (byte[])dt.Rows[j]["driver_parameter"]; strdriver_parameter = System.Text.Encoding.GetEncoding("GB2312").GetString(driver_parameter); driverArray = strdriver_parameter.Split(';'); ipArray = driverArray[1].Split(','); } if (device_id <= ipArray.Length && device_id >= 1) { ipList.Add(ipArray[device_id - 1]); serverList.Add(server_desc_e);/// sb.Append(string.Format(@"insert into sa.ca_terminal_ip(server_desc_e,TERMINAL_ID,tgs_ip) values('{0}',{1},'{2}');", server_desc_e, terminal_id, ipArray[device_id - 1])); } if (j == count * 40) { count++; sb.Append("end;"); using (OracleCommand subcmd = new OracleCommand(sb.ToString(), conn)) { subcmd.ExecuteNonQuery(); sb.Length = 0; sb.Append("begin "); } } else if ((dt.Rows.Count - j) / 40 == 0) { if ((dt.Rows.Count - 1) == j) { sb.Append("end;"); using (OracleCommand subcmd = new OracleCommand(sb.ToString(), conn)) { subcmd.ExecuteNonQuery(); sb.Length = 0; } } } }//insert terminal exists } Label1.Text = "Execute OK"; } sql = string.Format(@"select s.server_desc_e,G.driver_parameter from sa.tgs_gateway_base G,sa.tgs_server_base S where G.server_id=S.server_id and S.SERVER_DESC_E not like 'SMT_RF_%'"); /2 using (OracleCommand cmd = new OracleCommand(sql, conn)) { using (OracleDataAdapter da = new OracleDataAdapter(cmd)) { DataSet ds = new DataSet(); da.Fill(ds); DataTable dt = ds.Tables[0]; string server_desc_e = null; Byte[] driver_parameter = null; string strdriver_parameter = null; StringBuilder sb = new StringBuilder("begin "); string[] driverArray = null; string[] ipArray = null; List tempList = new List(); List ip2List = new List(); List server2List = new List(); serverList.Clear(); for (int i = 0; i < dt.Rows.Count; i++) { if (dt.Rows[i]["driver_parameter"] != DBNull.Value) { server_desc_e = dt.Rows[i]["server_desc_e"].ToString(); driver_parameter = (byte[])dt.Rows[i]["driver_parameter"]; strdriver_parameter = System.Text.Encoding.GetEncoding("GB2312").GetString(driver_parameter); driverArray = strdriver_parameter.Split(';'); ipArray = driverArray[1].Split(','); foreach (string s in ipArray) { if (!string.IsNullOrEmpty(s)) { tempList.Add(s); serverList.Add(server_desc_e); } } } } for cycle int count = 0; for (int i = 0; i < tempList.Count; i++) { count = 0; int j = 0; for (j = 0; j < ipList.Count; j++) { if (tempList[i] != ipList[j]) { count++; } else continue; } if (count == j) { ip2List.Add(tempList[i]); server2List.Add(serverList[i]); } } for (int i = 0; i < ip2List.Count; i++) { sb.Append(string.Format(@"insert into sa.ca_terminal_ip(server_desc_e,TERMINAL_ID,tgs_ip) values('{0}',{1},'{2}');", server2List[i], 0, ip2List[i])); } sb.Append("end;"); using (OracleCommand subcmd = new OracleCommand(sb.ToString(), conn)) { subcmd.ExecuteNonQuery(); sb.Length = 0; } } } } catch { Label1.Text = "操作失败,请重试!"; } finally { conn.Close(); Response.Write(" "); } }
oracle longtext类型,C#读取oracle long raw数据类型
最新推荐文章于 2021-08-04 15:49:32 发布