/* * 入库 */ #region 入库 public static Boolean insert(List <List <KCT_CASE_INFO> > listKct, string inv_no, string conn) { DBHelperORACLE db = new DBHelperORACLE(conn); int a = 0, b = 0, c = 0, d = 0; try { db.openConn(); db.beginTrans(); string newcaseinfoid = Guid.NewGuid().ToString("N"); //代码产生新的CASEINFOID string insertCaseInfo = "insert into xcky.kct_case_info (ID,CASE_ID,KCT_UUID,CASE_START_TIME,CASE_END_TIME,CASE_LON,CASE_LAT,WITNESS_INFO,REMARK,CREATE_USER,CREATE_DATETIME,UPDATE_USER,UPDATE_DATETIME,RESERVER1,RESERVER2,RESERVER3,RESERVER4,RESERVER5,RESERVER6,RESERVER7,RESERVER8,GPS_NAME,CASE_NAME,INVESTIGATION_ID) values ('" + newcaseinfoid + "'," + " (select case_id from xcky.scene_investigation where investigation_no = '" + inv_no + "'), '" + "52813100500148620140730230647098" + "'," + " to_date('" + listKct[0][0].Cdate + "','yyyy-MM-dd HH24:mi:ss')," + "to_date('" + listKct[listKct.Count - 1][listKct[listKct.Count - 1].Count - 1].Cdate + "','yyyy-MM-dd HH24:mi:ss')," + "'" + listKct[0][0].Slong + "','" + listKct[0][0].Slat + "','','',(select create_user from xcky.scene_investigation where investigation_no = '" + inv_no + "')" + ",(select create_datetime from xcky.scene_investigation where investigation_no = '" + inv_no + "')" + ",'','','','','','','','','','','',''," + "(select id from xcky.scene_investigation where investigation_no = '" + inv_no.Trim() + "'))"; d = db.execSqlREF(insertCaseInfo); string insertloace = "insert into xcky.kct_locale_data(ID,LOCALE_NAME,COL_STARTTIME,COL_ENDTIME,CREATE_USER,CREATE_DATETIME,UPDATE_USER,UPDATE_DATETIME,RESERVER1,RESERVER2,RESERVER3,RESERVER4,RESERVER5,RESERVER6,RESERVER7,RESERVER8,DATA_TYPE,CASE_INFO_ID) values(sys_guid(),'" + listKct[0][0].Lname + "',to_date('" + listKct[0][0].Cdate + "','yyyy-MM-dd HH24:mi:ss'),to_date('" + listKct[listKct.Count - 1][listKct[listKct.Count - 1].Count - 1].Cdate + "','yyyy-MM-dd HH24:mi:ss'),(select create_user from xcky.scene_investigation where investigation_no = '" + inv_no + "')," + "(select create_datetime from xcky.scene_investigation where investigation_no = '" + inv_no + "')," + "'','','','','','','','','','','1','" + newcaseinfoid + "')"; a = db.execSqlREF(insertloace); string insertGsm = "insert into xcky.kct_basestation_data (ID,BS_TYPE,IFACTIVE,REG_ZONE,SID,NID,BASE_ID,CDMA_CH,PN,STRENGTH,MCC_MNC,LAC,CELL_ID,BCCH,BSIC,SYS_BAND,RESERVER1,RESERVER2,RESERVER3,RESERVER4,RESERVER5,RESERVER6,RESERVER7,RESERVER8,RESERVER9,RESERVER10,LON,LAT,LOCALE_DATA_ID,COL_TIME)" + "values(sys_guid(),'CMCC_GSM','ACTIVE','','" + listKct[0][0].Lacsid + "','" + listKct[0][0].Nid + "','','','','-21','','13945','" + listKct[0][0].Mlac + "','','','','','','','','','','','','','','','',(select ID from xcky.kct_locale_data where case_info_id='" + newcaseinfoid + "'),sysdate)"; b = db.execSqlREF(insertGsm); string insertCdma = "insert into xcky.kct_basestation_data (ID,BS_TYPE,IFACTIVE,REG_ZONE,SID,NID,BASE_ID,CDMA_CH,PN,STRENGTH,MCC_MNC,LAC,CELL_ID,BCCH,BSIC,SYS_BAND,RESERVER1,RESERVER2,RESERVER3,RESERVER4,RESERVER5,RESERVER6,RESERVER7,RESERVER8,RESERVER9,RESERVER10,LON,LAT,LOCALE_DATA_ID,COL_TIME)" + "values(sys_guid(),'" + "CDMA" + "','ACTIVE','','" + listKct[0][0].Lacsid + "','" + listKct[0][0].Nid + "','110','','','-212','','','','','','','','','','','','','','','','','','',(select ID from xcky.kct_locale_data where case_info_id='" + newcaseinfoid + "'),sysdate)"; c = db.execSqlREF(insertCdma); db.commitTrans(); db.closeConn(); } catch (Exception ex) { db.rollbackTrans(); Console.WriteLine("err" + ex.Message); Program.LastError = ex.Message; return(false); throw; } if (a > 0 && b > 0 && c > 0 && d > 0) { return(true); } else { return(false); } }
//测试数据库连接是否可用 public int checkOracleConn(string oracleSid, string oracleName, string oraclePwd) { string constring = "Data Source=" + oracleSid + ";Persist Security Info=True;User ID=" + oracleName + ";Password="******";Unicode=True"; DBHelperORACLE doo = new DBHelperORACLE(constring); try { doo.openConn(); return(1); } catch (Exception) { return(-1); throw; } }
/* *替换 */ #region 替换 public static Boolean update(List <List <KCT_CASE_INFO> > listKct, string inv_no, string conn) { DBHelperORACLE db = new DBHelperORACLE(conn); int a = 0, b = 0, c = 0, d = 0; try { db.openConn(); db.beginTrans(); string updateCaseInfo = "update xcky.kct_case_info set CASE_ID=(select case_id from xcky.scene_investigation where investigation_no = '" + inv_no.Trim() + "'),KCT_UUID='52813100500148620140730230647098',CASE_START_TIME=(select investigation_date_from from xcky.scene_investigation where investigation_no = '" + inv_no.Trim() + "'),CASE_END_TIME=(select investigation_date_to from xcky.scene_investigation where investigation_no = '" + inv_no.Trim() + "'),CASE_LON='" + listKct[0][0].Slong + "',CASE_LAT='" + listKct[0][0].Slat + "',WITNESS_INFO='',REMARK='',CREATE_USER=(select create_user from xcky.scene_investigation where investigation_no = '" + inv_no + "'),CREATE_DATETIME=(select create_datetime from xcky.scene_investigation where investigation_no = '" + inv_no + "'),UPDATE_USER='',UPDATE_DATETIME=''," + "RESERVER1='',RESERVER2='',RESERVER3='',RESERVER4='',RESERVER5='',RESERVER6='',RESERVER7='',RESERVER8='',GPS_NAME='',CASE_NAME='',INVESTIGATION_ID=(select id from xcky.scene_investigation where investigation_no = '" + inv_no.Trim() + "') where ID =(select ID from xcky.kct_case_info where kct_uuid = '52813100500148620140730230647098' and investigation_id =(select id from xcky.scene_investigation where investigation_no = '" + inv_no.Trim() + "') and rownum=1)"; Console.WriteLine("一" + updateCaseInfo); a = db.execSqlREF(updateCaseInfo); string updateloace = "update xcky.kct_locale_data set LOCALE_NAME='" + listKct[0][0].Lname + "',COL_STARTTIME=sysdate,COL_ENDTIME=sysdate,CREATE_USER=(select create_user from xcky.scene_investigation where investigation_no = '" + inv_no + "'),CREATE_DATETIME=(select create_datetime from xcky.scene_investigation where investigation_no = '" + inv_no.Trim() + "'),UPDATE_USER='',UPDATE_DATETIME='',RESERVER1='',RESERVER2='',RESERVER3='',RESERVER4='',RESERVER5='',RESERVER6='',RESERVER7='',RESERVER8='',DATA_TYPE='1' where ID =(select id from xcky.kct_locale_data where case_info_id =(select ID from xcky.kct_case_info where kct_uuid = '52813100500148620140730230647098' and investigation_id =(select id from xcky.scene_investigation where investigation_no = '" + inv_no.Trim() + "') and rownum=1) and rownum=1)"; Console.WriteLine("二" + updateloace); b = db.execSqlREF(updateloace); string updateGsm = "update xcky.kct_basestation_data set BS_TYPE='CMCC_GSM',IFACTIVE='ACTIVE',REG_ZONE='',SID='" + listKct[0][0].Lacsid + "',NID='" + listKct[0][0].Nid + "',BASE_ID='',CDMA_CH='',PN='',STRENGTH='',MCC_MNC='',LAC='" + listKct[0][0].Mlac + "',CELL_ID='',BCCH='',BSIC='',SYS_BAND='',RESERVER1='',RESERVER2='',RESERVER3='',RESERVER4='',RESERVER5='',RESERVER6='',RESERVER7='',RESERVER8='',RESERVER9='',RESERVER10='',LON='" + listKct[0][0].Slong + "',LAT='" + listKct[0][0].Slat + "',COL_TIME=sysdate where LOCALE_DATA_ID =(select ID from xcky.kct_locale_data where case_info_id =(select id from xcky.kct_case_info where kct_uuid='52813100500148620140730230647098' and investigation_id=(select id" + " from xcky.scene_investigation where investigation_no = '" + inv_no + "') and rownum=1) and rownum=1) and BS_TYPE='CMCC_GSM'"; Console.WriteLine("三" + updateGsm); c = db.execSqlREF(updateGsm); string insertCdma = "update xcky.kct_basestation_data set BS_TYPE='CDMA',IFACTIVE='ACTIVE',REG_ZONE='',SID='" + listKct[0][0].Lacsid + "',NID='" + listKct[0][0].Nid + "',BASE_ID='',CDMA_CH='',PN='',STRENGTH='',MCC_MNC='',LAC='" + listKct[0][0].Mlac + "',CELL_ID='',BCCH='',BSIC='',SYS_BAND='',RESERVER1='',RESERVER2='',RESERVER3='',RESERVER4='',RESERVER5='',RESERVER6='',RESERVER7='',RESERVER8='',RESERVER9='',RESERVER10='',LON='" + listKct[0][0].Slong + "',LAT='" + listKct[0][0].Slat + "',COL_TIME=sysdate where LOCALE_DATA_ID =(select ID from xcky.kct_locale_data where case_info_id =(select id from xcky.kct_case_info where kct_uuid='52813100500148620140730230647098' and investigation_id=(select id" + " from xcky.scene_investigation where investigation_no = '" + inv_no + "') and rownum=1) and rownum=1) and BS_TYPE='CDMA'"; Console.WriteLine("四" + insertCdma); d = db.execSqlREF(insertCdma); db.commitTrans(); db.closeConn(); } catch (Exception ex) { db.rollbackTrans(); Program.LastError = ex.Message; return(false); throw; } if (a > 0 && b > 0 && c > 0 && d > 0) { return(true); } else { return(false); } }
private void button1_Click(object sender, EventArgs e) { string constring = "Data Source=" + oracleSid + ";Persist Security Info=True;User ID=" + oracleName + ";Password="******";Unicode=True"; DBHelperORACLE doo = new DBHelperORACLE(constring); doo.openConn(); Boolean createXmlOk = createXml(); if (createXmlOk) { DataTable dt = CSVFileHelper.OpenCSV(csvPath); List <KCT_CASE_INFO> kctList = CSVFileHelper.ToKctList(dt); dic = CSVFileHelper.Group(kctList); Boolean updateOk = CSVFileHelper.update(dic, inv_no, constring); string path = Application.StartupPath + "\\data.xml"; byte[] data; FileStream fs = File.OpenRead(path); data = new byte[fs.Length]; fs.Read(data, 0, Convert.ToInt32(fs.Length)); fs.Close(); Boolean XmlisOk = CSVFileHelper.InsertXml(inv_no, data, doo); if (updateOk && XmlisOk) { MessageBox.Show("更新成功!"); this.Close(); } else { MessageBox.Show("更新失败!"); this.Close(); } } else { MessageBox.Show("XML文件生成失败"); this.Close(); } }