private string execProc(DataSet ds, ref string s) { string ret = "1"; int num = 1; string hismsg = "计费成功"; string hisret = ""; tran = proc.Connection.BeginTransaction(); proc.Transaction = tran; for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { string pcode = ds.Tables[0].Rows[i]["iv_patient_id"].ToString(); string visit_time = ds.Tables[0].Rows[i]["in_visit_id"].ToString(); string order_no = ds.Tables[0].Rows[i]["in_order_no"].ToString(); string order_sub_no = ds.Tables[0].Rows[i]["in_order_sub_no"].ToString(); string charge_id = pcode + "_" + visit_time + "-" + order_no + "-" + order_sub_no; string drugcode = ds.Tables[0].Rows[i]["iv_drug_code"].ToString(); string labelno = ds.Tables[0].Rows[i]["iv_bar_code"].ToString(); string log_sql = "insert into charge_log values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')"; string begintime = DateTime.Now.ToString(); string query = "select 1 from charge_log where labelno='" + labelno + "' and hisret=2"; DBClass db = new DBClass(); DataSet ds2 = db.GetDB(query); if (ds2.Tables[0].Rows.Count > 0) { num = 2; break; } proc.Parameters["iv_dispensary"].Value = ds.Tables[0].Rows[i]["iv_dispensary"].ToString();// "5101"; proc.Parameters["iv_patient_id"].Value = ds.Tables[0].Rows[i]["iv_patient_id"].ToString(); proc.Parameters["in_visit_id"].Value = ds.Tables[0].Rows[i]["in_visit_id"].ToString(); proc.Parameters["in_baby_no"].Value = ds.Tables[0].Rows[i]["in_baby_no"].ToString(); proc.Parameters["in_order_no"].Value = ds.Tables[0].Rows[i]["in_order_no"].ToString(); proc.Parameters["in_order_sub_no"].Value = ds.Tables[0].Rows[i]["in_order_sub_no"].ToString(); proc.Parameters["iv_drug_code"].Value = ds.Tables[0].Rows[i]["iv_drug_code"].ToString(); proc.Parameters["iv_drug_spec"].Value = ds.Tables[0].Rows[i]["iv_drug_spec"].ToString(); proc.Parameters["iv_drug_units"].Value = ds.Tables[0].Rows[i]["iv_drug_units"].ToString(); proc.Parameters["iv_firm_id"].Value = ds.Tables[0].Rows[i]["iv_firm_id"].ToString(); proc.Parameters["in_dispense_amount"].Value = ds.Tables[0].Rows[i]["in_dispense_amount"].ToString(); proc.Parameters["iv_DISPENSING_PROVIDER"].Value = ds.Tables[0].Rows[i]["iv_DISPENSING_PROVIDER"].ToString(); proc.Parameters["in_order_sub_sub_no"].Value = ds.Tables[0].Rows[i]["in_order_sub_sub_no"].ToString(); proc.Parameters["iv_bar_code"].Value = ds.Tables[0].Rows[i]["iv_bar_code"].ToString(); proc.Parameters["id_PREFORM_DATETIME"].Value = DateTime.Now; proc.Parameters["in_SINA_MOUNT"].Value = "1"; proc.Parameters["iv_ADD_NURSE"].Value = ds.Tables[0].Rows[i]["iv_ADD_NURSE"].ToString(); proc.Parameters["in_stock_flag"].Value = ds.Tables[0].Rows[i]["in_stock_flag"].ToString(); proc.Parameters["on_price"].Value = ""; proc.Parameters["on_charges"].Value = ""; proc.Parameters["od_DISPENSING_DATE_TIME"].Value = DateTime.Now; proc.Parameters["on_exec_val"].Value = ""; proc.Parameters["ov_exec_txt"].Value = ""; try { proc.ExecuteNonQuery(); string endtime = DateTime.Now.ToString(); string inceptdt = DateTime.Now.ToString(); hisret = proc.Parameters["on_exec_val"].Value.ToString(); string msg = proc.Parameters["ov_exec_txt"].Value.ToString(); DB_Help dbhelp = new DB_Help(); dbhelp.addAndUpdate(string.Format(log_sql, begintime, endtime, inceptdt, labelno, charge_id, drugcode, hisret, msg)); if (hisret == "0" || hisret == "-1") { hismsg = proc.Parameters["ov_exec_txt"].Value.ToString(); // dbhelp.addAndUpdate("update charge_log set hisret=0 where labelno='"+labelno +"'"); num = 0; break; } else { num = 1; } } catch (Exception ex) { DB_Help dbhelp = new DB_Help(); string endtime = DateTime.Now.ToString(); string inceptdt = DateTime.Now.ToString(); hismsg = ex.Message; hisret = "0"; dbhelp.addAndUpdate(string.Format(log_sql, begintime, endtime, inceptdt, labelno, charge_id, drugcode, hisret, hismsg)); //dbhelp.addAndUpdate("update charge_log set hisret=0 where labelno='" + labelno + "'"); num = 0; break; } num = num * num; } s = hismsg; ret = num.ToString(); if (ret == "0") { tran.Rollback(); com.Connection.Close(); return "0"; } else if (ret == "2") { tran.Rollback(); com.Connection.Close(); s = "已计费"; return "1"; } else { tran.Commit(); com.Connection.Close(); string label = ds.Tables[0].Rows[0]["iv_bar_code"].ToString(); string insert = "insert into charge_log (labelno,hisret) values('{0}','{1}')"; DBClass db2 = new DBClass(); db2.SetDB(string.Format(insert, label, 2)); return "1"; } }
private string execProcEx(DataSet ds, ref string s) { string rett = ""; string begindt = DateTime.Now.ToString(); string chargeproc = "PHARMACY.DRUG_TRANS_PKG.TRANS_DRUG_PIVAS"; string connString = "Provider=MSDAORA;Data Source=yzxzh9i;User ID=inter_pivas;Password=pivas_inter; Persist Security Info=True"; OleDbCommand proc = new OleDbCommand(); OleDbConnection conn = new OleDbConnection(connString); proc.CommandText = chargeproc; proc.CommandType = CommandType.StoredProcedure; proc.Parameters.Clear(); proc.Parameters.Add("iv_dispensary", OleDbType.VarChar); proc.Parameters.Add("iv_patient_id", OleDbType.VarChar); proc.Parameters.Add("in_visit_id", OleDbType.VarChar); proc.Parameters.Add("in_baby_no", OleDbType.VarChar); proc.Parameters.Add("in_order_no", OleDbType.VarChar); proc.Parameters.Add("in_order_sub_no", OleDbType.VarChar); proc.Parameters.Add("iv_drug_code", OleDbType.VarChar); proc.Parameters.Add("iv_drug_spec", OleDbType.VarChar); proc.Parameters.Add("iv_drug_units", OleDbType.VarChar); proc.Parameters.Add("iv_firm_id", OleDbType.VarChar); proc.Parameters.Add("in_dispense_amount", OleDbType.VarChar); proc.Parameters.Add("iv_DISPENSING_PROVIDER", OleDbType.VarChar); proc.Parameters.Add("in_order_sub_sub_no", OleDbType.VarChar); proc.Parameters.Add("iv_bar_code", OleDbType.VarChar); proc.Parameters.Add("id_PREFORM_DATETIME", OleDbType.DBDate); proc.Parameters.Add("in_SINA_MOUNT", OleDbType.Integer); proc.Parameters.Add("iv_ADD_NURSE", OleDbType.VarChar); proc.Parameters.Add("in_stock_flag", OleDbType.Integer); for (int i = 0; i < 18; i++) { proc.Parameters[i].Direction = ParameterDirection.Input; } proc.Parameters.Add("on_price", OleDbType.VarChar); proc.Parameters.Add("on_charges", OleDbType.VarChar); proc.Parameters.Add("od_DISPENSING_DATE_TIME", OleDbType.DBDate); proc.Parameters.Add("on_exec_val", OleDbType.VarChar); proc.Parameters.Add("ov_exec_txt", OleDbType.VarChar); for (int i = 18; i < 23; i++) { proc.Parameters[i].Direction = ParameterDirection.Output; proc.Parameters[i].Size = 255; } proc.Connection = conn; if (conn.State != ConnectionState.Open) conn.Open(); OleDbTransaction tran = conn.BeginTransaction(); proc.Transaction = tran; //循环执行存储过程 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { string hisret; string hismsg; string pcode = ds.Tables[0].Rows[i]["iv_patient_id"].ToString(); string visit_time = ds.Tables[0].Rows[i]["in_visit_id"].ToString(); string order_no = ds.Tables[0].Rows[i]["in_order_no"].ToString(); string order_sub_no = ds.Tables[0].Rows[i]["in_order_sub_no"].ToString(); string charge_id = pcode + "_" + visit_time + "-" + order_no + "-" + order_sub_no; string drugcode = ds.Tables[0].Rows[i]["iv_drug_code"].ToString(); string labelno = ds.Tables[0].Rows[i]["iv_bar_code"].ToString(); string log_sql = "insert into charge_log values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')"; string begintime = DateTime.Now.ToString(); string query = "select 1 from charge_log where labelno='" + labelno + "' and hisret=2"; DBClass db = new DBClass(); DataSet ds2 = db.GetDB(query); if (ds2.Tables[0].Rows.Count > 0) { s = "已计费"; tran = null; return "1"; } //存储过程参数赋值 proc.Parameters["iv_dispensary"].Value = ds.Tables[0].Rows[i]["iv_dispensary"].ToString(); proc.Parameters["iv_patient_id"].Value = ds.Tables[0].Rows[i]["iv_patient_id"].ToString(); proc.Parameters["in_visit_id"].Value = ds.Tables[0].Rows[i]["in_visit_id"].ToString(); proc.Parameters["in_baby_no"].Value = ds.Tables[0].Rows[i]["in_baby_no"].ToString(); proc.Parameters["in_order_no"].Value = ds.Tables[0].Rows[i]["in_order_no"].ToString(); proc.Parameters["in_order_sub_no"].Value = ds.Tables[0].Rows[i]["in_order_sub_no"].ToString(); proc.Parameters["iv_drug_code"].Value = ds.Tables[0].Rows[i]["iv_drug_code"].ToString(); proc.Parameters["iv_drug_spec"].Value = ds.Tables[0].Rows[i]["iv_drug_spec"].ToString(); proc.Parameters["iv_drug_units"].Value = ds.Tables[0].Rows[i]["iv_drug_units"].ToString(); proc.Parameters["iv_firm_id"].Value = ds.Tables[0].Rows[i]["iv_firm_id"].ToString(); proc.Parameters["in_dispense_amount"].Value = ds.Tables[0].Rows[i]["in_dispense_amount"].ToString(); proc.Parameters["iv_DISPENSING_PROVIDER"].Value = ds.Tables[0].Rows[i]["iv_DISPENSING_PROVIDER"].ToString(); proc.Parameters["in_order_sub_sub_no"].Value = ds.Tables[0].Rows[i]["in_order_sub_sub_no"].ToString(); proc.Parameters["iv_bar_code"].Value = ds.Tables[0].Rows[i]["iv_bar_code"].ToString(); proc.Parameters["id_PREFORM_DATETIME"].Value = DateTime.Now; proc.Parameters["in_SINA_MOUNT"].Value = "1"; proc.Parameters["iv_ADD_NURSE"].Value = ds.Tables[0].Rows[i]["iv_ADD_NURSE"].ToString(); proc.Parameters["in_stock_flag"].Value = ds.Tables[0].Rows[i]["in_stock_flag"].ToString(); proc.Parameters["on_price"].Value = ""; proc.Parameters["on_charges"].Value = ""; proc.Parameters["od_DISPENSING_DATE_TIME"].Value = DateTime.Now; proc.Parameters["on_exec_val"].Value = ""; proc.Parameters["ov_exec_txt"].Value = ""; //执行存储过程,设定超时时间,过了就终止循环 try { Thread threadToKill = null; Action wrappedAction = () => { threadToKill = Thread.CurrentThread; proc.ExecuteNonQuery(); }; IAsyncResult result = wrappedAction.BeginInvoke(null, null); if (result.AsyncWaitHandle.WaitOne(6000)) { wrappedAction.EndInvoke(result); } else { rett = "0"; s = " 捕获到超时,失败"; //threadToKill.Abort(); //throw new TimeoutException(); break; } string endtime = DateTime.Now.ToString(); string inceptdt = DateTime.Now.ToString(); hisret = proc.Parameters["on_exec_val"].Value.ToString(); string msg = proc.Parameters["ov_exec_txt"].Value.ToString(); s = msg; DB_Help dbhelp = new DB_Help(); dbhelp.addAndUpdate(string.Format(log_sql, begintime, endtime, inceptdt, labelno, charge_id, drugcode, hisret, msg)); if (hisret == "1") { rett = "1"; continue; } else { rett = "0"; break; } } catch (Exception ex) { DB_Help dbhelp = new DB_Help(); string endtime = DateTime.Now.ToString(); string inceptdt = DateTime.Now.ToString(); hismsg = " 捕获到异常 ," + ex.Message; s = hismsg; hisret = "0"; dbhelp.addAndUpdate(string.Format(log_sql, begintime, endtime, inceptdt, labelno, charge_id, drugcode, hisret, hismsg)); rett = "0"; break; } } //如果正常计费 if (rett == "1") { //事务提交 if (conn != null && tran != null) { tran.Commit(); tran = null; } //写入日志 string label = ds.Tables[0].Rows[0]["iv_bar_code"].ToString(); string insert = "insert into charge_log ( begindt,enddt, labelno,hisret) values('{0}','{1}','{2}','{3}')"; DBClass db2 = new DBClass(); db2.SetDB(string.Format(insert, begindt, DateTime.Now.ToString(), label, 2)); //释放连接 if (conn.State == ConnectionState.Open) { conn.Close(); } conn.Dispose(); proc.Parameters.Clear(); proc.Dispose(); return "1"; } //如果计费失败或者有异常 //事务回滚 if (rett == "0") { if (conn != null && tran != null) { tran.Rollback(); tran = null; } //释放连接 if (conn.State == ConnectionState.Open) { conn.Close(); } conn.Dispose(); proc.Parameters.Clear(); proc.Dispose(); return "0"; } return "1"; }