esb实现sql查询Oracle数据库,esb流程的逻辑处理连接数据库//访问web service接口

HL7V2Service(xml文件5-21)

DataConn conn = new ConnOracle();

conn.ConnString = this.setting["ESB数据库连接"];

BusinessFlow.HL7Msg msg = new BusinessFlow.HL7Msg();

msg.load(msg_in.get_str("hl7v2"));

string sql = msg.make_sql_hl7_send();

string err_code = "";

string err = "";

conn.exec(sql,out err);

if(err!="")

{

err_code="999";

err=err+ this.setting["ESB数据库连接"] ;

}

string ack = msg.make_ack(err_code,err) ;

msg_out.add_str("result",ack);

=======his_dept 连接数据库查dt

System.Data.DataTable dt = (System.Data.DataTable) (msg_in.get_parm("his_dept"));

DataConn conn = new ConnOracle();

if(dt==null||dt.Rows.Count<=0){

string Deptcode = (string)(msg_in.get_parm("Dept_code"));

string Deptname = (string)(msg_in.get_parm("Dept_name"));

conn.ConnString = this.setting["HIS数据库连接"];

string sql = "select * from his_dept where 1=1 ";

if(!string.IsNullOrEmpty( Deptcode)) {

sql+=" and dept_code ="+Deptcode;

}

if(!string.IsNullOrEmpty( Deptname)) {

sql+=" and dept_name ='"+Deptname+"'";

}

string err_code = "";

string err = "";

dt =conn.select(sql,out err);

if(err!="")

{

err_code="999";

err=err ;

msg_out.add_str("result",err);

}

else{

string xml = BusinessFlow.PubUtils.dt2xml(dt,"res","View_name","Infos");

xml = BusinessFlow.PubUtils.FormatXml(xml);

msg_out.add_str("sql",sql );

msg_out.add_str("result",xml );

}

}

else{

string xml = BusinessFlow.PubUtils.dt2xml(dt,"res","View_name","Infos");

xml = BusinessFlow.PubUtils.FormatXml(xml);

msg_out.add_str("result",xml );

}

=======

View_ZTBT_PATIENT_INFO(xml文件6-11)

System.Data.DataTable dt = (System.Data.DataTable) (msg_in.get_parm("View_ZTBT_PATIENT_INFO"));

//string xml = BusinessFlow.PubUtils.dt2xml(dt,"res","View_ZTBT_PATIENT_INFO","Infos");

//xml = BusinessFlow.PubUtils.FormatXml(xml);

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();

System.Xml.XmlElement data = doc.CreateElement("req");

System.Xml.XmlElement msgVersion = doc.CreateElement("msgVersion");

msgVersion.InnerText = "1.0.0";

System.Xml.XmlElement msgTime = doc.CreateElement("msgTime");

msgTime.InnerText = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

System.Xml.XmlElement msgType = doc.CreateElement("msgType");

msgType.InnerText = "xml";

data.AppendChild(msgVersion);

data.AppendChild(msgTime);

data.AppendChild(msgType);

System.Xml.XmlElement msgBody = doc.CreateElement("msgBody");

foreach (System.Data.DataRow dr in dt.Rows)

{

System.Xml.XmlElement row = doc.CreateElement("PatientData");

foreach (System.Data.DataColumn dc in dt.Columns)

{

string name = dc.ColumnName;

string v = "";

{

v = dr[name].ToString();

}

System.Xml.XmlElement cell = doc.CreateElement(name);

cell.InnerText = v;

row.AppendChild(cell);

}

msgBody.AppendChild(row);

}

data.AppendChild(msgBody);

doc.AppendChild(data);

string xml = doc.InnerXml;

Dictionary data2 = new Dictionary();

string err = "";

string xml_t=@"<?xml version=""1.0"" encoding=""utf-8""?>

";

data2["InputPara"]= xml;

string sss =BusinessFlow.HttpUtils.GetSOAP("http://192.168.1.103:8080/empi/empiApi.asmx","http://ztbtempi.com/webservices/PIXAdd",xml_t,data2,ref err);

msg_out.add_str("sss",sss );

msg_out.add_str("err",err );

msg_out.add_str("result",xml );

同步数据时，查询所有的太麻烦，可直接获取两边数据库的最大时间差之间的数据

select convert(varchar(19),isnull(max(create_date),getdate()),121) as create_date,

convert(varchar(19),isnull(max(update_date),getdate()),121) as update_date,

convert(varchar(19),isnull(max(remove_date),getdate()),121) as remove_date

from ztbt_patient_info

select * from View_ZTBT_PATIENT_INFO where

create_date >= to_date(substr( :create_date ,1,19),'yyyy-mm-dd hh24:mi:ss')

or

update_date >= to_date(substr( :update_date ,1,19),'yyyy-mm-dd hh24:mi:ss')

or

remove_date >= to_date(substr( :remove_date ,1,19),'yyyy-mm-dd hh24:mi:ss')

///

区分C#格式化时间和oracle格式化时间的不同

System.Data.DataTable dt = (System.Data.DataTable) (msg_in.get_parm("View_Item"));

string dtstart = msg_in.get_parm("dtstart").ToString();

string servicename = msg_in.get_parm("servicename").ToString();

string mothedname = msg_in.get_parm("mothedname").ToString();

string xml = BusinessFlow.PubUtils.dt2xml(dt,"res","View_Item","Infos");

xml = BusinessFlow.PubUtils.FormatXml(xml);

DataConn conn = new ConnOracle();

conn.ConnString = this.setting["HIS数据库连接"];

DateTime DtEnd = DateTime.Now;

TimeSpan span = DtEnd.Subtract(Convert.ToDateTime(dtstart)); //算法是dtwo 减去 dtone

string datediff = "";//span.Days + "天" + span.Hours + "小时" + span.Minutes + "分钟" + span.Seconds + "秒";// + span.TotalDays;

if(span.Days>0){

datediff+=span.Days + "天";

}

if(span.Hours>0){

datediff+=span.Hours + "小时";

}

if(span.Minutes>0){

datediff+=span.Minutes + "分钟";

}

if(span.Seconds>0){

datediff+= span.Seconds + "秒";

}

if(span.TotalDays>0){

datediff+= span.TotalDays.ToString().Substring(0,span.TotalDays.ToString().IndexOf('.')) + "毫秒";

}

//Encoding.Default.GetByteCount(message)/1000+"KB"

msg_out.add_str("usedTime",datediff );

string sql = " INSERT INTO servicemonitor (servicename,funname,messageid,messagesize,recievetime,isresponsed,responsedtime,usedtime,datetime) values "

+" ('" + servicename + "','" + mothedname +"', ' ','" + 0 + "',to_date('" +

dtstart + "', 'yyyy-mm-dd hh24:mi:ss'),'true',to_date('" +

DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")+ "', 'yyyy-mm-dd hh24:mi:ss'),'" + datediff + "',sysdate )";

string err_code = "";

string err = "";

int i = conn.exec(sql,out err);

if(err!="")

{

err_code="999";

err=err+ this.setting["HIS数据库连接"] ;

}

if(i>0){

msg_out.add_str("result_sql","插入成功！" );

}

else{

msg_out.add_str("result_sql",err );

}

//msg_out.add_str("esburl",esburl+"?cmd=save_serviceinfo");

//msg_out.add_str("resulthttp",resulthttp);

msg_out.add_str("result",xml );