public object ExecuteScale(string strSql, out string strErrMsg) { strErrMsg = null; try { if (this._cmd == null) { return(null); } if (this._conn == null || this._conn.State == ConnectionState.Closed) { this.OpenConnection(); } _cmd.CommandText = strSql; return(_cmd.ExecuteOracleScalar()); } catch (Exception ex) { strErrMsg = "获取数据库数据时发生错误!请尝试再次操作,或者重新刷新页面。若仍存在错误,请与管理员联系。详细信息:" + ex.Message; log.ErrorFormat("oracle数据错误:{0}", ex.Message); return(null); } finally { this.CloseConnection(); } }
/// <summary> /// 获取某段时间内所在的科室 /// </summary> /// <param name="patNoofHis"></param> /// <param name="starttime"></param> /// <param name="endtime"></param> /// <returns></returns> public static string GetChangedDeptName(string patNoofHis, DateTime starttime, DateTime endtime, PatientInfo patientInfo) { string returnValue = string.Empty; using (OracleConnection conn = new OracleConnection(DataAccessFactory.GetSqlDataAccess("HISDB").GetDbConnection().ConnectionString)) { if (conn.State != ConnectionState.Open) { conn.Open(); } OracleCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = string.Format(@"select OLD_DATA_NAME from changeddeptrecord where INPATIENT_NO='{0}' and OPER_DTIME >= '{1}' and OPER_DTIME <= '{2}' order by OPER_DTIME", patNoofHis, starttime.ToString("yyyy-MM-dd 00:00:00"), endtime.ToString("yyyy-MM-dd 23:59:59")); try { Object obj = cmd.ExecuteOracleScalar(); if (null != obj) { returnValue = obj.ToString(); } else { cmd.CommandText = string.Format(@"select new_data_name from changeddeptrecord where INPATIENT_NO='{0}' and OPER_DTIME < '{1}' order by OPER_DTIME desc", patNoofHis, starttime.ToString("yyyy-MM-dd 00:00:00")); Object ob = cmd.ExecuteOracleScalar(); if (null != ob) { returnValue = ob.ToString(); } else { cmd.CommandText = string.Format(@"select OLD_DATA_NAME from changeddeptrecord where INPATIENT_NO='{0}' and OPER_DTIME > '{1}' order by OPER_DTIME asc", patNoofHis, endtime.ToString("yyyy-MM-dd 23:59:59")); Object oj = cmd.ExecuteOracleScalar(); if (null != oj) { returnValue = oj.ToString(); } } } if (string.IsNullOrEmpty(returnValue)) { returnValue = patientInfo.Section; } } catch (Exception ex) { m_app.CustomMessageBox.MessageShow("调用HIS接口报错!" + ex.Message, CustomMessageBoxKind.ErrorOk); } } return(returnValue); }
/// <summary> /// This function is used to execute select queires /// </summary> /// <param name="pQuery"></param> /// <returns></returns> public string FetchDataFromDBUsingExecuteScaler(string pQuery) { OracleConnection myConnection; OracleCommand myCommand = null; string lResult = string.Empty; myConnection = new OracleConnection(ReadConfigFile("ConnectionString")); try { myConnection.Open(); myCommand = new OracleCommand(pQuery, myConnection); lResult = myCommand.ExecuteOracleScalar().ToString(); return(lResult); } catch (Exception ex) { return(string.Empty); } finally { if (myCommand != null) { myCommand.Connection.Close(); myCommand.Dispose(); } } }
/// <summary> /// 获取NR标识 /// </summary> /// <param name="dateString">日期yyyymmdd</param> /// <param name="oraId">数据库名</param> /// <param name="username"></param> /// <param name="password"></param> /// <returns></returns> public string NrStatusCheck(string dateString, string oraId, string username, string password) { string oraStr = string.Format("Persist Security Info=False;Data Source={0};User ID={1};password={2}", oraId, username, password); using (OracleConnection oraConn = new OracleConnection(oraStr)) { string result = "n/a"; try { oraConn.Open(); string queryString = string.Format("select flag from nr_status where run_date=to_date('{0}','yyyymmdd')", dateString); OracleCommand oraComm = new OracleCommand(queryString, oraConn); var rd = oraComm.ExecuteOracleScalar(); result = rd.ToString(); //oraComm.Dispose(); //oraConn.Close(); } catch (Exception ex) { result = oraId + " "; result += ex.Message.ToString(); if ((ex.ToString()).IndexOf("NullReferenceException") != -1) { result = "no row selected"; } if ((ex.ToString()).IndexOf("ORA-12535") != -1) { result = "TNS超时"; } //throw; } return(result); } }
public Boolean BuscarAlumno(int clave) { try { conexion = new OracleConnection(cadena); conexion.Open(); cmd = new OracleCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "Select * from Admin where Id= " + clave + ""; cmd.Connection = conexion; resultado = new object(); resultado = cmd.ExecuteOracleScalar(); conexion.Close(); LlenarGridView(); if (resultado != null) { return(true); } else { return(false); } } catch (Exception ex) { Response.Write("" + ex.Message); return(true); } }
public string ExecuteSQL(string sql) { string user_lang = Session["SESSION_LANG"].ToString(); string dbpass = Session["Ctl"].ToString(); string TCO_BSUSER_PK = Session["USER_PK"].ToString(); string sqlResult = ""; //$1 TCO_BSUSER_PK parameter //$2 user language(ENG,VIE,KOR) string queryString = sql.Replace("$1", TCO_BSUSER_PK); queryString = sql.Replace("$2", user_lang); try { string conString = ConfigurationManager.AppSettings["strConn"]; conString += "User id=Ctl;Password="******":" + queryString); } return(sqlResult); }
public static object Scalar(string sql) { log.Debug(string.Format("\t{0}", sql)); sql = Regex.Replace(sql, @"[\n\r]", " "); object result = null; OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["oracle"].ConnectionString); try { conn.Open(); OracleCommand cmd = new OracleCommand(sql, conn); result = cmd.ExecuteOracleScalar(); } catch (System.Exception ex) { log.Debug(string.Format("\t{0}", ex.ToString())); throw ex; } finally { conn.Close(); } return(result); }
///<summary> ///连接数据库并执行SQL语句获得最大行数 ///</summary> public static object GetScalar(string sql_str, string con_str) { OracleConnection conn = new OracleConnection(con_str); object r = null; try { //OracleConnection conn = new OracleConnection(con_str); conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = sql_str; r = cmd.ExecuteOracleScalar(); cmd.Dispose(); //conn.Close(); return(r); } catch (OracleException ex) { MessageBox.Show(ex.Message.ToString()); } finally { conn.Close(); } return(r); }
/// <summary> /// 用于测试数据库连接是否有效 /// </summary> /// <returns></returns> public static bool ExecuteTest() { bool bln = false; try { using (OracleConnection con = new OracleConnection(DbConnectStr)) { using (OracleCommand cmd = new OracleCommand("SELECT SYSDATE FROM DUAL", con)) { if (con.State != ConnectionState.Open) { con.Open(); } cmd.CommandType = CommandType.Text; object obj = cmd.ExecuteOracleScalar(); if (obj != null && obj != DBNull.Value) { bln = true; } } } } catch (Exception e) { bln = false; LogUtility.DataLog.WriteLog(LogLevel.Info, e.Message, new RunningPlace("OracleHelper", "ExecuteTest"), "DBErr"); } return(bln); }
//保存 protected void btnSaveData_Click(object sender, EventArgs e) { string strDate = ddlYear.Text.ToString() + "-" + ddlMonth.Text.ToString(); OracleConnection conn = DB.createConn(); conn.Open(); string strSQL = "select count(*) From TP_PLANT_DATA_M where LOGTIME = '" + strDate + "'"; OracleCommand cmd = new OracleCommand(strSQL, conn); int iResult = Convert.ToInt32(cmd.ExecuteOracleScalar().ToString()); if (iResult > 0) { } strSQL = "Delete From TP_PLANT_DATA_M where LOGTIME = '" + strDate + "'"; cmd = new OracleCommand(strSQL, conn); cmd.ExecuteNonQuery(); strSQL = "insert into TP_PLANT_DATA_M(LOGTIME,F_STEEL,P_STEEL,J_STEEL,GREATETIME) " + "values ('" + strDate + "'," + txtFSteel.Text + "," + txtPSteel.Text + "," + txtJSteel.Text + ",'" + DateTime.Now.ToString() + "')"; cmd = new OracleCommand(strSQL, conn); int rsCount = Convert.ToInt32(cmd.ExecuteNonQuery().ToString()); if (rsCount > 0) { this.Page.Visible = false; } else { Response.Write("保存失败!"); } conn.Close(); }
/// <summary> /// 创建NC预定单号 /// </summary> /// <param name="cmd"></param> /// <returns>判断当前物料是否已经存在于客商的填报计划当中</returns> protected string createNCYDBH(OracleCommand cmd, string ydrq) { string strSql = "select count(*)+1 sl from tmp_nc_xsjh t where t.DBILLDATE='" + ydrq + "' and t.pk_corp='" + DplTHGC.Items[DplTHGC.SelectedIndex].Value + "' "; cmd.CommandText = strSql; return("JH" + ydrq.Replace("-", "") + cmd.ExecuteOracleScalar().ToString().PadLeft(4, '0')); }
private void killSessionButton_Click(object sender, EventArgs e) { string sidStr = "", sidCount = "", msgError = "", msgSucc = ""; int returnCount = 0; try { if (this.killSessionTextBox.Text == "") { MessageBox.Show("请输入会话ID"); } else { OracleConnection conn = new OracleConnection(this.killSessionDsnTextBox.Text); conn.Open(); foreach (string linestr in killSessionTextBox.Lines) { sidStr = linestr.Substring(0, linestr.IndexOf(",")); OracleCommand connCount = conn.CreateCommand(); connCount.CommandText = "select count(*) from v$session a where a.sid = '" + sidStr + "'"; sidCount = Convert.ToString(connCount.ExecuteOracleScalar()).Trim(); if (sidCount == "" || sidCount == "0") { MessageBox.Show("没有找到用户会话或用户已退出"); } else if (sidCount == "1") { OracleCommand killSql = conn.CreateCommand(); killSql.CommandText = "alter system kill session '" + this.killSessionTextBox.Text + "'"; returnCount = killSql.ExecuteNonQuery(); if (returnCount == 0) { msgSucc += linestr + " KILL用户会话成功" + "\r\n"; //MessageBox.Show("KILL用户会话成功"); } else { msgError += linestr + " KILL用户会话失败" + "\r\n"; //MessageBox.Show("KILL用户会话失败"); } } } conn.Close(); if (msgSucc.Length > 0) { MessageBox.Show(msgSucc); } if (msgError.Length > 0) { MessageBox.Show(msgError); } } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public static string GetReturnValue(string str, string sql) { OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr); conn.Open(); OracleCommand command = conn.CreateCommand(); command.CommandText = sql + str; string value = command.ExecuteOracleScalar().ToString(); conn.Close(); return(value); }
/// <summary> /// 创建销售计划表体ID /// </summary> /// <param name="cmd"></param> /// <returns>判断当前物料是否已经存在于客商的填报计划当中</returns> protected string createPK_XSJH_B(OracleCommand cmd, string chglda, string ydrq) { //string strSql = "select b.FREE1 from tmp_nc_xsjh t,tmp_nc_xsjh_b b where t.PK_TMP_NC_XSJH=b.PK_TMP_NC_XSJH and b.cinventoryid='" + chglda + "' and b.dr='0' and t.dr='0' and b.blargessflag='N' and t.DBILLDATE='" + ydrq + "' and t.pk_corp='" + log_corp + "' and t.FREE1='" + log_user + "' "; //cmd.CommandText = strSql; //object objPK_TMP_NC_XSJH = cmd.ExecuteOracleScalar(); //if (objPK_TMP_NC_XSJH == null) //{ cmd.CommandText = "select tmp_nc_xsjh_b_seq.NextVal from dual "; object objPK_TMP_NC_XSJH = cmd.ExecuteOracleScalar(); return(objPK_TMP_NC_XSJH.ToString()); //} //else { // return "exist"+objPK_TMP_NC_XSJH.ToString(); //} }
/// <summary> /// 执行命令,返回一个值 /// </summary> /// <param name="commandText"></param> /// <returns></returns> public override object ExecuteScalar(string commandText) { OracleCommand command = new OracleCommand(commandText, this._connection, this._transaction); object v = null; try { v = command.ExecuteOracleScalar(); } finally { command.Dispose(); command = null; } return(v); }
/// <summary> /// 返回第一行的第一列 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public object ExecScalar(string strSql) { try { using (var conn = new OracleConnection(Connstr)) { conn.Open(); var cmd = new OracleCommand(strSql, conn); var obj = cmd.ExecuteOracleScalar(); conn.Close(); return(obj); } } catch { return(null); } }
public string ExecuteProcedureParam1(string Proc, OracleParameter[] param, string varOut, string result) { con = new OracleConnection(strOracleConn); OracleCommand cmd = new OracleCommand(); try { con.Open(); if (con.State == ConnectionState.Open) { cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(param); cmd.CommandText = Proc; string str = Convert.ToString(cmd.ExecuteOracleScalar()); if (cmd.Parameters[result].Value.ToString() != "") { return(cmd.Parameters[result].Value.ToString()); } else { return(string.Empty); } } else { throw new Exception("database connection not found"); } } catch (Exception ex) { throw new Exception(ex.ToString() + "\n\n" + "Query:" + "\n" + Proc); } finally { if (con.State == ConnectionState.Open) { con.Close(); } cmd = null; con = null; } }
/// <summary> /// 带参数 /// </summary> /// <param name="pDbConn"></param> /// <param name="sSql"></param> /// <param name="pParams"></param> /// <returns></returns> public object ExecuteOracleScalar(string sSql, OracleParameter[] opc) { try { pCommand.CommandText = sSql; pCommand.Parameters.Clear(); if (opc != null) { for (int i = 0; i < opc.Length; i++) { pCommand.Parameters.Add(opc[i]); } } return(pCommand.ExecuteOracleScalar()); } catch (Exception ep) { LogBN.WriteLog(typeof(DbAPI), "ExecuteOracleScalar 程序段的异常" + ep + sSql); return(-1); } }
protected void Delete_Command(object sender, CommandEventArgs e) { string strId = e.CommandArgument.ToString().Trim(); OracleConnection ConnectionStringJXC = new OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringJXC"].ConnectionString); ConnectionStringJXC.Open(); //打开链接 OracleCommand cmd = new OracleCommand(); //创建执行类 cmd.Connection = ConnectionStringJXC; OracleTransaction tx = ConnectionStringJXC.BeginTransaction(); cmd.Transaction = tx;//开启链接事务 try { cmd.CommandText = "update tmp_nc_xsjh_b set dr='1' where PK_TMP_NC_XSJH_B='" + strId + "'"; cmd.ExecuteNonQuery(); cmd.CommandText = "select t.PK_TMP_NC_XSJH from tmp_nc_xsjh_b t where t.dr=0 and t.PK_TMP_NC_XSJH in"; cmd.CommandText += "(select b.PK_TMP_NC_XSJH from tmp_nc_xsjh_b b where b.PK_TMP_NC_XSJH_B='" + strId + "') "; //OracleDataAdapter cmdSQL = new OracleDataAdapter(strSql.ToString(), ConnectionStringJXC); object ret = cmd.ExecuteOracleScalar(); if (ret == null)//如果计划表体已经删光则删除表体 { cmd.CommandText = "update tmp_nc_xsjh t set t.dr='1' where t.PK_TMP_NC_XSJH in (select b.PK_TMP_NC_XSJH from tmp_nc_xsjh_b b where b.PK_TMP_NC_XSJH_B='" + strId + "' )"; cmd.ExecuteNonQuery(); } } catch (System.Data.OracleClient.OracleException E) { tx.Rollback(); ConnectionStringJXC.Close(); MessageBox.ShowAndRedirect(this, "删除销售计划信息失败,请联系系统管理员!", "SalePlanList.aspx"); BindPlanDataList(); } tx.Commit(); ConnectionStringJXC.Close(); BindPlanDataList(); // MessageBox.ShowAndRedirect(this, "删除成功!", "SalePlanList.aspx"); }
public static string GetItemName(string pInventoryItemID) { string strReturn = ""; using (OracleConnection cn = new OracleConnection(clsSystemConfigurations.ConnectionStringOracle)) { using (OracleCommand cmd = cn.CreateCommand()) { cmd.CommandText = "SELECT DESCRIPTION FROM INV.MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID=:INVENTORY_ITEM_ID AND ORGANIZATION_ID = '120'"; cmd.Parameters.Add(new OracleParameter("INVENTORY_ITEM_ID", pInventoryItemID)); cn.Open(); try { strReturn = cmd.ExecuteOracleScalar().ToString(); } catch { strReturn = "None"; } } } return(strReturn); }
//public string readoraclereader(OracleCommand cmd) //{ // try // { // strErrorMsg = ""; // OracleCommand cmd1 = new OracleCommand(); // System.Data.OracleClient.OracleDataReader oraReader; // System.Data.OracleClient.OracleLob myClob; // cmd1 = cmd; // OpenConnection(); // cmd.Connection = oraConn; // oraReader = cmd1.ExecuteReader(); // myClob = cmd1.Parameters["status"].Value; // return myClob.Value.ToString; // } // catch (Exception ex) // { // return ex.Message.ToString(); // } //} /// <summary> /// Execute the oracle command and return a single value. /// /// </summary> /// <param name="cmd">Oracle command to be executed.</param> /// <returns></returns> /// <remarks></remarks> public bool ProcedureExecutescaler(OracleCommand cmd) { try { strErrorMsg = ""; OpenConnection(); cmd.Connection = oraConn; cmd.CommandType = CommandType.StoredProcedure; cmd.ExecuteOracleScalar(); return(true); } catch (Exception ex) { strErrorMsg = ex.Message.ToString(); return(false); } finally { cmd.Dispose(); oraConn.Close(); oraConn.Dispose(); } }
public string ExecuteScalar(string sql, OracleParameterCollection opc) { string sRet = ""; if (null == _conn) { _conn = GetDBConnection(); } try { OracleCommand cmd = new OracleCommand(sql, _conn); foreach (OracleParameter op in opc) { cmd.Parameters.Add(op.ParameterName, op.OracleType, op.Size).Value = op.Value; } if (null != _st) { cmd.Transaction = _st; } sRet = cmd.ExecuteOracleScalar().ToString(); } catch { throw; } finally { if (null == _st) { _conn.Close(); _conn.Dispose(); _conn = null; } } return(sRet); }
static void Main(string[] args) { StreamWriter fstr_out = null; OracleConnection conn = null; OracleCommand cmd=null; OracleDataAdapter da=null; OracleDataReader rdr = null; OracleTransaction tr = null; DataTable tmpDataTable = null; object tmpObject; string FieldName, tmpString; long tmpLong; int tmpInt, i; bool tmpBool; DateTime tmpDateTime; try { try { fstr_out = new StreamWriter("log.log", false, System.Text.Encoding.GetEncoding(1251)); fstr_out.AutoFlush = true; string ConnectionString = "User ID=corp_yk_user;Password=google;Data Source=sm"; //ConnectionString = "User ID=aspnetuser;Password=aspnet;Data Source=ASM"; //ConnectionString = "User ID=sqlpretension;Password=impression;Data Source=SM"; conn = new OracleConnection(ConnectionString); #if TEST_DBMS_OUTPUT conn.StateChange += new StateChangeEventHandler(conn_StateChange); conn.InfoMessage += new OracleInfoMessageEventHandler(conn_InfoMessage); #endif conn.Open(); #if TEST_NULL if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "select * from testtabletypes"; rdr=cmd.ExecuteReader(); do { if (rdr.HasRows) { int idx = rdr.GetOrdinal(FieldName = "FINT"); while (rdr.Read()) { tmpInt = !rdr.IsDBNull(idx) ? Convert.ToInt32(rdr[idx]) : int.MinValue; tmpInt = rdr[idx] != DBNull.Value ? Convert.ToInt32(rdr[idx]) : int.MaxValue; tmpInt = !rdr[idx].Equals(DBNull.Value) ? Convert.ToInt32(rdr[idx]) : int.MinValue; tmpInt = !Convert.IsDBNull(rdr[idx]) ? Convert.ToInt32(rdr[idx]) : int.MaxValue; } } } while (rdr.NextResult()); rdr.Close(); if (da == null) da = new OracleDataAdapter(); da.SelectCommand = cmd; if (tmpDataTable == null) tmpDataTable = new DataTable(); else tmpDataTable.Reset(); da.Fill(tmpDataTable); foreach (DataRow r in tmpDataTable.Rows) { tmpInt = !r.IsNull(FieldName = "FINT") ? Convert.ToInt32(r[FieldName]) : int.MinValue; tmpInt = r[FieldName] != DBNull.Value ? Convert.ToInt32(r[FieldName]) : int.MaxValue; tmpInt = !r[FieldName].Equals(DBNull.Value) ? Convert.ToInt32(r[FieldName]) : int.MinValue; tmpInt = !Convert.IsDBNull(r[FieldName]) ? Convert.ToInt32(r[FieldName]) : int.MaxValue; } #endif #if TEST_NLS_NUMERIC_CHARACTERS if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "select cast('0,123' as number(10,6)) from dual"; tmpObject = cmd.ExecuteScalar(); #endif #if CAST_VS_CONVERT if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "select count(*) from dual"; try { tmpInt = (int)cmd.ExecuteScalar(); } catch (Exception eException) { Console.WriteLine(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace); } tmpObject = cmd.ExecuteScalar(); // decimal tmpInt = Convert.ToInt32(tmpObject); tmpObject = cmd.ExecuteOracleScalar(); // OracleNumber OracleNumber tmpOracleNumber = (OracleNumber)tmpObject; #endif #if TEST_TYPES if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; tmpDateTime = DateTime.Now; cmd.CommandText = @" update testtabletypes set ftimestamp=:ftimestamp_in, ftimestamp_w_tz=:ftimestamp_w_tz_in, ftimestamp_w_l_tz=:ftimestamp_w_l_tz_in "; cmd.Parameters.Clear(); cmd.Parameters.Add("ftimestamp_in", OracleType.Timestamp).Value = tmpDateTime; cmd.Parameters.Add("ftimestamp_w_tz_in", OracleType.TimestampWithTZ).Value = tmpDateTime; cmd.Parameters.Add("ftimestamp_w_l_tz_in", OracleType.TimestampLocal).Value = tmpDateTime; tmpInt=cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.CommandText = "select * from TestTableTypes"; if (da == null) da = new OracleDataAdapter(); da.SelectCommand = cmd; if (tmpDataTable == null) tmpDataTable = new DataTable(); else tmpDataTable.Reset(); da.Fill(tmpDataTable); if (tmpDataTable.Rows.Count > 0) { tmpObject = tmpDataTable.Rows[0]["FNumber_Asterisk_0"]; if(!Convert.IsDBNull(tmpObject)) // System.InvalidCastException: Object cannot be cast from DBNull to other types. tmpBool = Convert.ToBoolean(tmpObject); } cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "UpdateTestTableTypes"; OracleCommandBuilder.DeriveParameters(cmd); cmd.Parameters["ID_IN"].Value = 1; //cmd.Parameters["FCLOB_IN"].Value = new string('я', 2001); //"CLOB"; //cmd.Parameters["FNCLOB_IN"].Value = new string('я', 2001); //"NCLOB"; //cmd.Parameters["FSMALLINT_IN"].Value = false; //cmd.Parameters["FINT_IN"].Value = Enum4TestTypes.Second; cmd.ExecuteNonQuery(); #endif #if TEST_DBMS_OUTPUT if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "TestProcedureWithDBMSOutput"; OracleCommandBuilder.DeriveParameters(cmd); cmd.ExecuteNonQuery(); #endif #if TEST_CLOB if (cmd == null) cmd = conn.CreateCommand(); //tmpString = "<P style=\"FONT-SIZE: large\" align=center>Спасибо, что пользуетесь нашей системой заказов!</P><P style=\"FONT-SIZE: large\" align=center>С 3 декабря изменился интерфейс \"онлайн заказов\" для клиентов компании.</P><P style=\"FONT-SIZE: large\" align=center>Просьба о всех пожеланиях и возможных проблемах сообщать на эл. адрес <A href=\"mailto:[email protected]\">[email protected]</A></P><P>Для создания заказа воспользуйтесь пунктом меню \"Каталог товаров\", либо загрузите заказ из <IMG src=\"http://corp.mtr.com.ua/OrdersApp/images/excel.gif\"> Excel через меню \"Загрузить заказ\"</P><P>Более подробно о том, как делать заказы и пользоваться системой вы можете почитать в <A href=\"http://corp.mtr.com.ua/OrdersApp/client/manual/help%20b2b.doc\">документации</A>.</P><P align=center><B>Уважаемые партнёры:</B></P><P align=center>Не упустите возможность закупить продукцию «Медиа Трейдинг» по очень хорошим ценам.</P><P align=center>Только раз в неделю, каждую пятницу, мы предлагаем специальные цены на несколько позиций из своего ассортимента. Размер партии может быть любой. Условие только одно: <B>Полная оплата закупки в тот же день*.</B><BR><BR>Способ оплаты может быть любой.<BR><SPAN style=\"COLOR: rgb(255,0,0); FONT-WEIGHT: bold\">ВНИМАНИЕ: АССОРТИМЕНТ ТОВАРОВ ОБНОВЛЯЕТСЯ КАЖДЫЙ ЧЕТВЕРГ В 16:00.</SPAN><BR></P><P><B>*Пояснение к условию:</B><BR>Вы можете получить товар по объявленной цене при условии оплаты в этот же день.<BR>Это значит, что нужно <B>«принести» (любым способом: безнал, предоплата) деньги в офис или представительство компании с 9:00 до 17:00 пятницы.</B> При этом <B>у Вас не должно быть вообще никаких задолженностей перед компанией, <U>независимо от размеров и срока давности</U>. Баланс либо нулевой, либо плюс.</B><BR><B><U>Без подтверждения оплаты товар отгружен не будет!</U></B><BR><BR>В случае, если товара нет на складе представительства, заказ будет доставлен с центрального склада ближайшей отгрузкой.</P><p align=\"center\"><b><br></b></p><p align=\"center\"><b><br></b></p><p align=\"center\"><b><br></b></p>"; //tmpString = new string('я', 2000); tmpString = new string('я', 2001); //tmpString = new string('я', 3000); //tmpString = new string('я', 3999); //tmpString = new string('я', 4000); //tmpString = new string('я', 4001); //tmpString = new string('я', 0x0ffff); cmd.CommandType = CommandType.Text; cmd.CommandText = "update TestTableTypes set FClob = :FClob, FNClob = :FNClob"; //cmd.Parameters.Add(":FClob", OracleType.Clob).Value = tmpString; //cmd.Parameters.Add(":FNClob", OracleType.NClob).Value = tmpString; //cmd.Parameters.AddWithValue(":FClob", tmpString); //cmd.Parameters.AddWithValue(":FNClob", tmpString); OracleParameter p; p = new OracleParameter(); p.ParameterName = ":FClob"; p.DbType = DbType.String; p.Value = tmpString; cmd.Parameters.Add(p); p = new OracleParameter(); p.ParameterName = ":FNClob"; p.DbType = DbType.String; p.Value = tmpString; cmd.Parameters.Add(p); tmpInt=cmd.ExecuteNonQuery(); #endif #if TEST_EXECUTE_SCALAR if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "select count(*) from user_objects"; tmpObject = cmd.ExecuteScalar(); // tmpInt=(int)tmpObject; // Wrong!!! tmpInt = Convert.ToInt32(tmpObject); #endif #if TEST_FUNCTION if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "get_actions_count"; OracleCommandBuilder.DeriveParameters(cmd); cmd.Parameters["application_id_in"].Value = 128; cmd.Parameters["date_actual"].Value = new DateTime(1900, 1, 1); tmpObject = cmd.ExecuteNonQuery(); tmpInt = Convert.ToInt32(cmd.Parameters["return_value"].Value); cmd.CommandText = "get_zonder_duration"; OracleCommandBuilder.DeriveParameters(cmd); cmd.Parameters["volume_in"].Value = 0.1; tmpObject = cmd.ExecuteNonQuery(); tmpInt = Convert.ToInt32(cmd.Parameters["return_value"].Value); #endif #if GET_STORED_PROCEDURE_PARAMETERS if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "typhoon.utilfunctions.updategextra"; // "typhoon.Utilization_Util.ImportUtilization"; //"typhoon.pkg_claim.get_doc_status"; // "typhoon.movegoodstoresrevforinetf"; //"typhoon.setdocpropvalue"; // "typhoon.GET_URL_OPENDOC"; //"typhoon.HO_Utils.makeDocument"; OracleCommandBuilder.DeriveParameters(cmd); Console.WriteLine(cmd.CommandText); for (int ii = 0; ii < cmd.Parameters.Count; ++ii) Console.WriteLine("ParameterName: " + cmd.Parameters[ii].ParameterName + " OracleType: " + cmd.Parameters[ii].OracleType.ToString() + " Direction: " + cmd.Parameters[ii].Direction.ToString()); Console.ReadLine(); #endif #if TEST_SEND_E_MAIL if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "typhoon.mail_task_util.sendmail"; //"typhoon.mail_task_util.sendmail2"; OracleCommandBuilder.DeriveParameters(cmd); cmd.Parameters["M_SENDER"].Value = "*****@*****.**"; //"*****@*****.**"; // "*****@*****.**" cmd.Parameters["M_RECIPIENT"].Value = "[email protected]; [email protected]"; // "*****@*****.**"; cmd.Parameters["M_SUBJECT"].Value = "Тест e-mail посредством оракула"; cmd.Parameters["M_MESSAGE"].Value = "<p><center><img src=\"http://b2b.mtr.com.ua/images/logo.gif\" /></center></p><p><center><b>Дистрибуция Медиа Продукции</b></center></p><p>Тест e-mail посредством оракула <a href=\"http://google.com/\">google.com</a></p>"; //cmd.Parameters["M_CC_RECIPIENT"].Value = DBNull.Value; //cmd.Parameters["M_BCC_RECIPIENT"].Value = DBNull.Value; cmd.Parameters["M_ADDRDELIM"].Value = ";"; cmd.ExecuteNonQuery(); #endif #if TEST_STORED_PROCEDURE_IN_CIRCLE if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "TableWithSequenceSave"; OracleCommandBuilder.DeriveParameters(cmd); for (i = 0; i < 10; ++i) { cmd.Parameters["id_cur"].Value = 0; cmd.Parameters["val_new"].Value = i; cmd.ExecuteNonQuery(); tmpInt = Convert.ToInt32(cmd.Parameters["id_new"].Value); } #endif #if TEST_BLOB if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; FileStream fs; byte[] Blob; #if TEST_BLOB_SAVE cmd.CommandText = "update TableWithBLOB set FBlob=:FBlob where Id=:Id"; cmd.Parameters.Clear(); cmd.Parameters.Add("Id", OracleType.Number).Value = 1; cmd.Parameters.Add("FBlob", OracleType.Blob); fs = new FileStream("xpfirewall.bmp", FileMode.Open, FileAccess.Read); Blob = new byte[fs.Length]; fs.Read(Blob, 0, Blob.Length); fs.Close(); cmd.Parameters["FBlob"].Value = Blob; tmpInt = cmd.ExecuteNonQuery(); #endif #if TEST_BLOB_SAVE_BY_SP // http://support.microsoft.com/default.aspx?scid=kb;en-us;322796 fs = new FileStream("xpfirewall.bmp", FileMode.Open, FileAccess.Read); Blob = new byte[fs.Length]; fs.Read(Blob, 0, Blob.Length); fs.Close(); cmd.Transaction = conn.BeginTransaction(); cmd.CommandType = CommandType.Text; cmd.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;"; cmd.Parameters.Clear(); cmd.Parameters.Add(new OracleParameter("tempblob", OracleType.Blob)).Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); OracleLob tmpOracleLob; tmpOracleLob = (OracleLob)cmd.Parameters["tempblob"].Value; tmpOracleLob.BeginBatch(OracleLobOpenMode.ReadWrite); tmpOracleLob.Write(Blob, 0, Blob.Length); tmpOracleLob.EndBatch(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "TableWithBLOBSave"; OracleCommandBuilder.DeriveParameters(cmd); cmd.Parameters["ID_IN"].Value = 2; cmd.Parameters["FBLOB_IN"].Value = tmpOracleLob; tmpInt = cmd.ExecuteNonQuery(); cmd.Transaction.Commit(); cmd.CommandType = CommandType.Text; #endif cmd.CommandText = "select * from TableWithBLOB"; cmd.Parameters.Clear(); rdr = cmd.ExecuteReader(); do { if (rdr.HasRows) { for (int i = 0; i < rdr.FieldCount; ++i) fstr_out.WriteLine(rdr.GetName(i) + " GetDataTypeName(): \"" + rdr.GetDataTypeName(i) + "\" GetFieldType(): \"" + rdr.GetFieldType(i) + "\""); tmpInt = rdr.GetOrdinal("FBlob"); while (rdr.Read()) { tmpString = "FromBlob.bmp"; if (File.Exists(tmpString)) File.Delete(tmpString); Blob = (byte[])rdr["FBlob"]; fs = new FileStream(tmpString, FileMode.Create); fs.Write(Blob, 0, Blob.Length); fs.Close(); tmpString = "FromBlob_1.bmp"; if (File.Exists(tmpString)) File.Delete(tmpString); Blob = new byte[rdr.GetBytes(tmpInt, 0, null, 0, int.MaxValue)]; rdr.GetBytes(tmpInt, 0, Blob, 0, Blob.Length); fs = new FileStream(tmpString, FileMode.Create); fs.Write(Blob, 0, Blob.Length); fs.Close(); } } } while (rdr.NextResult()); rdr.Close(); #endif #if TEST_SELECT /* if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType=CommandType.Text; cmd.CommandText = @" select ew.ew_id as ""Id"", ew.ew_name as ""Description"" from typhoon.tbl_extra_what ew where (ew.ew_fs=typhoon.get_parent_cfo(:cfo)) and (coalesce(ew.ew_is_closed,0)=0) order by ew.ew_id "; cmd.Parameters.Clear(); cmd.Parameters.Add("cfo", OracleType.Number).Value = 150140; if (da == null) da = new OracleDataAdapter(); da.SelectCommand = cmd; if (tmpDataTable == null) tmpDataTable = new DataTable(); else tmpDataTable.Reset(); da.Fill(tmpDataTable); */ /* tmpObject = cmd.ExecuteScalar(); if (tmpObject != null && !Convert.IsDBNull(tmpObject)) tmpLong = Convert.ToInt64(tmpObject); */ if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText="select * from tmp_docs_badrows"; cmd.Parameters.Clear(); if (da == null) da = new OracleDataAdapter(); da.SelectCommand = cmd; if (tmpDataTable == null) tmpDataTable = new DataTable(); else tmpDataTable.Reset(); da.Fill(tmpDataTable); if (tmpDataTable.PrimaryKey == null) tmpDataTable.PrimaryKey = new DataColumn[] { tmpDataTable.Columns["doc_id"], tmpDataTable.Columns["g_id"] }; #endif #if TEST_SELECTED_STORED_PROCEDURE if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "StoredProcedureForPaging"; OracleCommandBuilder.DeriveParameters(cmd); cmd.Parameters["start_in"].Value = 2; cmd.Parameters["limit_in"].Value = 2; if (da == null) da = new OracleDataAdapter(); da.SelectCommand = cmd; if (tmpDataTable == null) tmpDataTable = new DataTable(); else tmpDataTable.Reset(); da.Fill(tmpDataTable); FieldName="id"; foreach(DataRow r in tmpDataTable.Rows) { Console.WriteLine(!r.IsNull(FieldName) ? Convert.ToInt32(r[FieldName]).ToString() : "null"); } #endif #if TEST_STORED_PROCEDURE if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "typhoon.GET_URL_OPENDOC"; OracleCommandBuilder.DeriveParameters(cmd); cmd.Parameters["P_DOCID"].Value = 33711025; cmd.Parameters["P_FULLHTMLCODE"].Value = DBNull.Value; cmd.ExecuteNonQuery(); tmpString = Convert.ToString(cmd.Parameters["RETURN_VALUE"].Value); #endif #if TEST_MULTI_STATEMENTS if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; //cmd.CommandText = "alter session set NLS_TERRITORY = 'CIS';alter session set NLS_SORT = 'RUSSIAN';alter session set NLS_NUMERIC_CHARACTERS = '.,'"; cmd.CommandText = "begin execute immediate 'alter session set NLS_TERRITORY = ''CIS'''; execute immediate 'alter session set NLS_SORT = ''RUSSIAN'''; execute immediate 'alter session set NLS_NUMERIC_CHARACTERS = ''.,'''; end;"; cmd.ExecuteNonQuery(); #endif #if TEST_PARAMETERS if (cmd == null) cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "select * from dual where :a=:b"; cmd.Parameters.Add("a", OracleType.Int32).Value = 1; cmd.Parameters.Add("b", OracleType.Int32).Value = 1; //cmd.Parameters.Add("c", OracleType.Int32).Value = 2; tmpObject = cmd.ExecuteScalar(); cmd.Parameters.Clear(); cmd.CommandText = "select k_id as id, k_name as val from typhoon.tbl_kontragents where k_name like :q order by k_name"; tmpString = "FOTO"; cmd.Parameters.Add("q", OracleType.NVarChar).Value = tmpString + "%"; if (da == null) da = new OracleDataAdapter(); da.SelectCommand = cmd; if (tmpDataTable == null) tmpDataTable = new DataTable(); else tmpDataTable.Reset(); da.Fill(tmpDataTable); cmd.Parameters.Clear(); cmd.CommandText = @" select hoty_id, param_name, param_value from order_params where (hoty_id = :hoty_id) "; cmd.Parameters.Add("hoty_id", OracleType.Number).Value = 2219; if (da == null) da = new OracleDataAdapter(); da.SelectCommand = cmd; if (tmpDataTable == null) tmpDataTable = new DataTable(); else tmpDataTable.Reset(); da.Fill(tmpDataTable); FieldName = "param_name"; foreach (DataRow r in tmpDataTable.Rows) { Console.WriteLine(!r.IsNull(FieldName) ? Convert.ToString(r[FieldName]).ToString() : "null"); } #endif } catch (Exception eException) { Console.WriteLine(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace); } } finally { if (rdr != null && !rdr.IsClosed) rdr.Close(); if (conn != null && conn.State == ConnectionState.Open) conn.Close(); if (fstr_out != null) fstr_out.Close(); } }
public OrganizationModel OracleGetOrg(string UNP) { if (UNP == null) { MessageBox.Show("Заполните УПН!", "Ошибка УНП", MessageBoxButton.OK, MessageBoxImage.Error); return(new OrganizationModel()); } var orgFromOracle = new OrganizationModel(); OrganizationModel oracleOrg = null; using (OracleConnection oracleConnection = new OracleConnection(ConnectionStringOracle)) { oracleConnection.Open(); string oracleCheckUNP = "SELECT COUNT(*) FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP =" + $"'{UNP}'"; OracleCommand oracleCheckUNPComm = new OracleCommand(oracleCheckUNP, oracleConnection); if ((Convert.ToInt32((oracleCheckUNPComm.ExecuteOracleScalar()).ToString()) != 1)) { MessageBox.Show("Организации с таким УПН не существует в базе МНС!", "Ошибка УНП", MessageBoxButton.OK, MessageBoxImage.Error); return(new OrganizationModel()); } string oracleFullNameOrgComm = "SELECT mns_subject.FULL_NAME FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP = " + $"{UNP}"; OracleCommand oracleFullNameOrg = new OracleCommand(oracleFullNameOrgComm, oracleConnection);// Поллное название организации string oracleSortNameOrgComm = "SELECT mns_subject.NAME FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP = " + $"{UNP}"; OracleCommand oracleSortNameOrg = new OracleCommand(oracleSortNameOrgComm, oracleConnection); // Краткое название организации string oracleDateRegistrationComm = "SELECT mns_subject.REG_DATE FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP = " + $"{ UNP}"; OracleCommand oracleDateRegistration = new OracleCommand(oracleDateRegistrationComm, oracleConnection);// Дата регистрации string checkUNPComm = "SELECT COUNT(*) FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP =" + $"'{UNP}'"; OracleCommand CheckUNP = new OracleCommand(checkUNPComm, oracleConnection);// проверка на наличие организации с таким УНП string createDateOrg = oracleDateRegistration.ExecuteOracleScalar().ToString(); string sqlORARESIDENT = "SELECT mns_constitutor.RESIDENT FROM BUTB_SEC.mns_constitutor join BUTB_SEC.mns_subject on mns_constitutor.MNS_SUBJECT_ID = mns_subject.ID WHERE mns_subject.UNP = " + $"{ UNP}"; OracleCommand comORARESIDENT = new OracleCommand(sqlORARESIDENT, oracleConnection); int rez = Convert.ToInt32(comORARESIDENT.ExecuteOracleScalar().ToString()); var rezident = ""; if (rez == 1) { rezident = "Беларусь"; } //string sqlNCOUNTRYID = "SELECT mns_constitutor.NCOUNTRY_ID FROM BUTB_SEC.mns_constitutor join BUTB_SEC.mns_subject on mns_constitutor.MNS_SUBJECT_ID = mns_subject.ID WHERE mns_subject.UNP = " + $"{ UNP}"; var orgModel = new OrganizationModel { CreatedateOrg = Convert.ToDateTime(createDateOrg), // Это не правильно делать через приведение к стрингу и назад, но инече я получаю System.InvalidCastException: "Не удалось привести тип объекта "System.Data.OracleClient.OracleDateTime" к типу "System.IConvertible"." FullNameOrg = oracleFullNameOrg.ExecuteOracleScalar().ToString(), ShortNameOrg = oracleSortNameOrg.ExecuteOracleScalar().ToString(), CountyrOrg = rezident, }; oracleConnection.Close(); oracleOrg = orgModel; } return(oracleOrg ?? new OrganizationModel());// если риск NULL возвращаем пустую модель(чтобы не свалилось в случае чего) }
/// <summary> /// 检查HIS系统中出院卡片是否已审核通过 /// 注:功能未启用、未配置、配置信息错误也视为未通过 /// </summary> /// <auth>Yanqiao.Cai</auth> /// <date>2013-03-04</date> /// <param name="noofinpat"></param> /// <returns></returns> public static bool CheckOutHosCardCheckPassed(int noofinpat) { try { string config = DS_SqlService.GetConfigValueByKey("AuditOutHosCardInHIS"); if (string.IsNullOrEmpty(config)) { return(false); } XmlDocument doc = new XmlDocument(); doc.LoadXml(config); #region 1、出科检查按钮是否显示 XmlNodeList isOutHosCheckBtnOpenList = doc.GetElementsByTagName("IsOutHosCheckBtnOpen"); if (null == isOutHosCheckBtnOpenList || isOutHosCheckBtnOpenList.Count == 0) { return(false); } if (null != isOutHosCheckBtnOpenList[0].InnerText && isOutHosCheckBtnOpenList[0].InnerText.Trim() != "1") { return(false); } #endregion #region 2、此功能是否启用 XmlNodeList isOpenList = doc.GetElementsByTagName("IsOpen"); if (null == isOpenList || isOpenList.Count == 0) { return(false); } if (null != isOpenList[0].InnerText && isOpenList[0].InnerText.Trim() != "1") { return(false); } #endregion #region 3、出科检查是否已通过 ///3.1、EMR中病人基本信息表 XmlNodeList emrBasicInfoTableNameList = doc.GetElementsByTagName("EmrBasicInfoTableName"); if (null == emrBasicInfoTableNameList || emrBasicInfoTableNameList.Count == 0) { return(false); } string emrBasicInfoTableName = null == emrBasicInfoTableNameList[0].InnerText ? string.Empty : emrBasicInfoTableNameList[0].InnerText.Trim(); if (string.IsNullOrEmpty(emrBasicInfoTableName)) { return(false); } ///3.2、EMR病人基本信息表中对应的出院检查标识字段 XmlNodeList emrOutHosCheckFlagFieldNameList = doc.GetElementsByTagName("EmrOutHosCheckFlagFieldName"); if (null == emrOutHosCheckFlagFieldNameList || emrOutHosCheckFlagFieldNameList.Count == 0) { return(false); } string emrOutHosCheckFlagFieldName = null == emrOutHosCheckFlagFieldNameList[0].InnerText ? string.Empty : emrOutHosCheckFlagFieldNameList[0].InnerText.Trim(); if (string.IsNullOrEmpty(emrOutHosCheckFlagFieldName)) { return(false); } ///3.3、EMR病人基本信息表中与病人表关联的字段 XmlNodeList relatedNoofinpatFieldNameList = doc.GetElementsByTagName("RelatedNoofinpatFieldName"); if (null == relatedNoofinpatFieldNameList || relatedNoofinpatFieldNameList.Count == 0) { return(false); } string relatedNoofinpatFieldName = null == relatedNoofinpatFieldNameList[0].InnerText ? string.Empty : relatedNoofinpatFieldNameList[0].InnerText.Trim(); if (string.IsNullOrEmpty(relatedNoofinpatFieldName)) { return(false); } ///3.4、EMR病人基本信息表中出院检查标识字段对应的检查通过的值 XmlNodeList emrOutHosCheckPassedValueList = doc.GetElementsByTagName("EmrOutHosCheckPassedValue"); if (null == emrOutHosCheckPassedValueList || emrOutHosCheckPassedValueList.Count == 0) { return(false); } string emrOutHosCheckPassedValue = null == emrOutHosCheckPassedValueList[0].InnerText ? string.Empty : emrOutHosCheckPassedValueList[0].InnerText.Trim(); if (string.IsNullOrEmpty(emrOutHosCheckPassedValue)) { return(false); } string checkFlagValue = DS_SqlService.GetFieldContent(DS_Common.DeleteSpecialCharacter(emrBasicInfoTableName), DS_Common.DeleteSpecialCharacter(emrOutHosCheckFlagFieldName), DS_Common.DeleteSpecialCharacter(relatedNoofinpatFieldName), DS_Common.DeleteSpecialCharacter(noofinpat.ToString()).Trim()); ///3.5、判断出科检查是否已通过 if (checkFlagValue != emrOutHosCheckPassedValue) { return(false); } #endregion #region 4、EMR中病人表与HIS关联的字段 ///4.1、EMR中病人表与HIS关联的字段 XmlNodeList emrRelatedFieldNameList = doc.GetElementsByTagName("EmrRelatedFieldName"); if (null == emrRelatedFieldNameList || emrRelatedFieldNameList.Count == 0) { return(false); } string emrRelatedFieldName = null == emrRelatedFieldNameList[0].InnerText ? string.Empty : emrRelatedFieldNameList[0].InnerText.Trim(); if (string.IsNullOrEmpty(emrRelatedFieldName)) { return(false); } ///4.2、EMR中病人表与HIS关联的字段的值 DataTable dt = DS_SqlService.GetInpatientByID(noofinpat, 1); emrRelatedFieldName = DS_Common.DeleteSpecialCharacter(emrRelatedFieldName); if (null == dt || dt.Rows.Count == 0 || !dt.Columns.Contains(emrRelatedFieldName.ToUpper())) { return(false); } string emrRelatedFieldValue = dt.Rows[0][emrRelatedFieldName].ToString(); if (string.IsNullOrEmpty(emrRelatedFieldValue)) { return(false); } #endregion #region 5、该病人的出院卡片在HIS系统中是否已经审核通过 ///5.1、获取HIS审核状态表 XmlNodeList auditTableNameList = doc.GetElementsByTagName("AuditTableName"); if (null == auditTableNameList || auditTableNameList.Count == 0) { return(false); } string auditTableName = null == auditTableNameList[0].InnerText ? string.Empty : auditTableNameList[0].InnerText.Trim(); if (string.IsNullOrEmpty(auditTableName)) { return(false); } ///5.2、获取HIS审核状态字段 XmlNodeList auditFieldNameList = doc.GetElementsByTagName("AuditFieldName"); if (null == auditFieldNameList || auditFieldNameList.Count == 0) { return(false); } string auditFieldName = null == auditFieldNameList[0].InnerText ? string.Empty : auditFieldNameList[0].InnerText.Trim(); if (string.IsNullOrEmpty(auditFieldName)) { return(false); } ///5.3、获取HIS审核状态字段对应的审核通过的值(配置项) XmlNodeList auditPassedValueList = doc.GetElementsByTagName("AuditPassedValue"); if (null == auditPassedValueList || auditPassedValueList.Count == 0) { return(false); } string auditPassedValue = null == auditPassedValueList[0].InnerText ? string.Empty : auditPassedValueList[0].InnerText.Trim(); if (string.IsNullOrEmpty(auditPassedValue)) { return(false); } ///5.4、获取HIS中与EMR病人表关联的字段 XmlNodeList hisRelatedFieldNameList = doc.GetElementsByTagName("HisRelatedFieldName"); if (null == hisRelatedFieldNameList || hisRelatedFieldNameList.Count == 0) { return(false); } string hisRelatedFieldName = null == hisRelatedFieldNameList[0].InnerText ? string.Empty : hisRelatedFieldNameList[0].InnerText.Trim(); if (string.IsNullOrEmpty(hisRelatedFieldName)) { return(false); } using (OracleConnection conn = new OracleConnection(DataAccessFactory.GetSqlDataAccess("HISDB").GetDbConnection().ConnectionString)) { if (conn.State != ConnectionState.Open) { conn.Open(); } OracleCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; ///5.5、该病人的出院卡片在HIS系统中是否已经审核通过 auditTableName = DS_Common.DeleteSpecialCharacter(auditTableName); auditFieldName = DS_Common.DeleteSpecialCharacter(auditFieldName); auditPassedValue = DS_Common.DeleteSpecialCharacter(auditPassedValue); hisRelatedFieldName = DS_Common.DeleteSpecialCharacter(hisRelatedFieldName); cmd.CommandText = " select " + auditFieldName + " from " + auditTableName + " where " + hisRelatedFieldName + " = :emrRelatedFieldValue "; cmd.Parameters.Clear(); OracleParameter para = new OracleParameter("emrRelatedFieldValue", OracleType.Char); para.Value = emrRelatedFieldValue; cmd.Parameters.Add(para); object obj = cmd.ExecuteOracleScalar(); if (null != obj && obj.ToString().Trim() == auditPassedValue.Trim()) { return(true); } } #endregion return(false); } catch (Exception ex) { throw new Exception(ex.Message); } }
public AutoFillKontragentViewModel AutoFilKontra(string UNP) { if (UNP == null) { MessageBox.Show("Заполните УПН!", "Ошибка УНП", MessageBoxButton.OK, MessageBoxImage.Error); return(new AutoFillKontragentViewModel()); } var orgFromOracle = new AutoFillKontragentViewModel(); AutoFillKontragentViewModel oracleOrg = null; using (OracleConnection oracleConnection = new OracleConnection(ConnectionStringOracle)) { oracleConnection.Open(); //----------------------------------------------------------------------- string oracleCheckUNP = "SELECT COUNT(*) FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP =" + $"'{UNP}'"; OracleCommand oracleCheckUNPComm = new OracleCommand(oracleCheckUNP, oracleConnection); if ((Convert.ToInt32((oracleCheckUNPComm.ExecuteOracleScalar()).ToString()) != 1)) { MessageBox.Show("Организации с таким УПН не существует в базе МНС!", "Ошибка УНП", MessageBoxButton.OK, MessageBoxImage.Error); return(new AutoFillKontragentViewModel()); } //======================================================================= //----------------------------------------------------------------------- string oracleFullNameOrgComm = "SELECT mns_subject.FULL_NAME FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP = " + $"{UNP}"; OracleCommand oracleFullNameOrg = new OracleCommand(oracleFullNameOrgComm, oracleConnection);// Поллное название организации string fullNameOrg = oracleFullNameOrg.ExecuteOracleScalar().ToString(); //======================================================================= //----------------------------------------------------------------------- string oracleSortNameOrgComm = "SELECT mns_subject.NAME FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP = " + $"{UNP}"; OracleCommand oracleSortNameOrg = new OracleCommand(oracleSortNameOrgComm, oracleConnection); // Краткое название организации string shortNameOrg = oracleSortNameOrg.ExecuteOracleScalar().ToString(); //====================================================================== //---------------------------------------------------------------------- string oracleDateRegistrationComm = "SELECT mns_subject.REG_DATE FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP = " + $"{ UNP}"; OracleCommand oracleDateRegistration = new OracleCommand(oracleDateRegistrationComm, oracleConnection);// Дата регистрации string createDateOrg = oracleDateRegistration.ExecuteOracleScalar().ToString(); //===================================================================== //---------------------------------------------------------------------- string sqlHeadName = "SELECT mns_subject.FIO_CHIEF FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP = " + $"{UNP}"; OracleCommand comHeadName = new OracleCommand(sqlHeadName, oracleConnection);// руководитель string headName = comHeadName.ExecuteOracleScalar().ToString(); //===================================================================== //--------------------------------------------------------------------- string sqlOwneerName = "SELECT mns_constitutor.FULL_NAME FROM BUTB_SEC.mns_constitutor join BUTB_SEC.mns_subject on mns_constitutor.MNS_SUBJECT_ID = mns_subject.ID WHERE mns_subject.UNP =" + $"{ UNP}"; OracleCommand OwneerNameComm = new OracleCommand(sqlOwneerName, oracleConnection); OracleDataReader reader = OwneerNameComm.ExecuteReader(); string ownerName = null; while (reader.Read()) { ownerName = $"{ownerName}" + $"{ Convert.ToString(reader.GetString(0))}; "; } //--------------------------------------------------------------------- string sqlResident = "SELECT mns_constitutor.RESIDENT FROM BUTB_SEC.mns_constitutor join BUTB_SEC.mns_subject on mns_constitutor.MNS_SUBJECT_ID = mns_subject.ID WHERE mns_subject.UNP = " + $"{UNP}"; OracleCommand comResident = new OracleCommand(sqlResident, oracleConnection); string countryPers = null; string countryOrg = null; int rez = 0; try { rez = Convert.ToInt32(comResident.ExecuteOracleScalar().ToString()); } catch (Exception) { countryOrg = "Беларусь"; //MessageBox.Show("В базе данных не содержится признака резидентства. Скорее всего организация очень старая или данные были внесены не корректно. Заполняем ручками из регистратора:)", "Предупреждение!", MessageBoxButton.OK, MessageBoxImage.Error); } if (rez != 0) { countryOrg = "Беларусь"; } //======================================================================= try { string ConnStrCountryID = "SELECT mns_constitutor.NCOUNTRY_ID FROM BUTB_SEC.mns_constitutor join BUTB_SEC.mns_subject on mns_constitutor.MNS_SUBJECT_ID = mns_subject.ID WHERE mns_subject.UNP = " + $"{UNP}"; OracleCommand com_ORA_NCOUNTRY_ID = new OracleCommand(ConnStrCountryID, oracleConnection); string countryID = com_ORA_NCOUNTRY_ID.ExecuteOracleScalar().ToString(); using (SqlConnection sqlConnection = new SqlConnection(connectionStringSQL)) { sqlConnection.Open(); SqlCommand Country_Select = new SqlCommand("Country_Select", sqlConnection);// получение старны Country_Select.CommandType = CommandType.StoredProcedure; Country_Select.Parameters.AddWithValue("Country_Code", countryID); countryPers = Country_Select.ExecuteScalar().ToString(); if (countryPers == null) { countryPers = "Беларусь"; } sqlConnection.Close(); } } catch (Exception) { countryPers = "Беларусь"; } finally { if (countryPers == null) { countryPers = "(не выбрано)"; } } //----------------------------------------------------------------------- //======================================================================= var orgModel = new AutoFillKontragentViewModel(); orgModel.AutoFillViewModelOrg.CreateDateOrg = Convert.ToDateTime(createDateOrg); // Это не правильно делать через приведение к стрингу и назад, но инече я получаю System.InvalidCastException: "Не удалось привести тип объекта "System.Data.OracleClient.OracleDateTime" к типу "System.IConvertible"." orgModel.AutoFillViewModelPers.HeadName = headName; orgModel.AutoFillViewModelOrg.CountryOrg = countryOrg; orgModel.AutoFillViewModelPers.CountryPers = countryPers; if (string.IsNullOrEmpty(ownerName) || string.IsNullOrWhiteSpace(ownerName) || ownerName == "null" || ownerName == "Null") { orgModel.AutoFillViewModelPers.OwnerName = headName; orgModel.AutoFillViewModelOrg.FullNameOrg = $"Индивидивидуальный предприниматель {fullNameOrg}"; orgModel.AutoFillViewModelOrg.ShortNameOrg = $"ИП {shortNameOrg}"; } else { orgModel.AutoFillViewModelPers.OwnerName = ownerName; orgModel.AutoFillViewModelOrg.FullNameOrg = fullNameOrg; orgModel.AutoFillViewModelOrg.ShortNameOrg = shortNameOrg; } var check = new CheckUNP(); if (check.UNPchecher(UNP) == true) { MessageBox.Show("Организация с таким УНП уже внесена в базу", "Внимание!", MessageBoxButton.OK, MessageBoxImage.Information); } oracleConnection.Close(); oracleOrg = orgModel; } return(oracleOrg ?? new AutoFillKontragentViewModel());// если риск NULL возвращаем пустую модель(чтобы не свалилось в случае чего) }
protected void btnUpdate_Click(object sender, EventArgs e) { if (!Page.IsValid) { return; } string[] curupdateDate = hidNewDt.Value.Split(';'); if (curupdateDate.Length == 0 || hidNewDt.Value == "") { return; } using (OracleConnection ConnectionStringJXC = new OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringJXC"].ConnectionString)) { ConnectionStringJXC.Open(); //打开链接 OracleCommand cmd = new OracleCommand(); //创建执行类 cmd.Connection = ConnectionStringJXC; OracleTransaction tx = ConnectionStringJXC.BeginTransaction(); cmd.Transaction = tx;//开启链接事务 string strSql = ""; string strPK_TMP_NC_XSJH = ""; ///插入销售计划表头信息 try { string[] curRowDate = curupdateDate[0].Split(','); /* strSql = "select PK_TMP_NC_XSJH from tmp_nc_xsjh where dr='0' and DBILLDATE='" + curRowDate[9].ToString() + "' and pk_corp='" + log_corp + "' and FREE1='" + log_user + "' "; * cmd.CommandText = strSql; * object objPK_TMP_NC_XSJH = cmd.ExecuteOracleScalar(); * if (objPK_TMP_NC_XSJH == null) * {*/ cmd.CommandText = "select tmp_nc_xsjh_seq.NextVal from dual "; strPK_TMP_NC_XSJH = cmd.ExecuteOracleScalar().ToString(); ////如果不存在插入 strSql = "insert into tmp_nc_xsjh (PK_TMP_NC_XSJH,DBILLDATE,CCUSTOMERID,PK_CORP,DR,VNOTE,"; strSql += " FREE1,FREE2,FREE3,FREE4,FREE5,TS,FLAG,ERRORMESSAGE,VRECEIPTCODE,CUSTCODE,CUSTNAME,CORPNAME)"; strSql += "values('" + strPK_TMP_NC_XSJH + "','" + TextTHRQ.Text + "','" + curRowDate[0].ToString() + "','" + DplTHGC.Items[DplTHGC.SelectedIndex].Value + "',0,'',"; strSql += "'','','','','',to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),'0','','" + createNCYDBH(cmd, TextTHRQ.Text) + "',"; strSql += "'" + curRowDate[1].ToString() + "','" + curRowDate[2].ToString() + "','" + DplTHGC.Items[DplTHGC.SelectedIndex].Text + "')"; cmd.CommandText = strSql; int exIn = cmd.ExecuteNonQuery(); /* } * else //更新 * { * strPK_TMP_NC_XSJH = objPK_TMP_NC_XSJH.ToString(); * strSql = "update tmp_nc_xsjh set ts=to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') where dr='0' and DBILLDATE='" + curRowDate[9].ToString() + "' and pk_corp='" + log_corp + "' and FREE1='" + log_user + "' "; * cmd.CommandText = strSql; * cmd.ExecuteOracleScalar(); * } */ } catch (System.Data.OracleClient.OracleException E) { tx.Rollback(); MessageBox.ShowAndRedirect(this, "操作销售计划表头信息失败,请联系系统管理员!", "SalesPlan.aspx"); return; } ///插入销售计划表体信息 try { for (int i = 0; i < curupdateDate.Length; i++) { string[] curRowDate_b = curupdateDate[i].Split(','); string strPK_TMP_NC_XSJH_B = "";//销售计划表体ID ///校验物料是否存在,并创建销售计划表体ID try { strPK_TMP_NC_XSJH_B = createPK_XSJH_B(cmd, curRowDate_b[4], TextTHRQ.Text); //if (strPK_TMP_NC_XSJH_B.Substring(0, 4).Equals("exist")) //{ // tx.Rollback(); // MessageBox.ShowAndRedirect(this, "当前物料:"+curRowDate_b[7].ToString()+"已经存在,请返回计划列表直接修改!", "SalesPlan.aspx"); // return ; //} } catch (System.Data.OracleClient.OracleException E) { tx.Rollback(); MessageBox.ShowAndRedirect(this, "创建销售表体ID失败,请联系系统管理员!", "SalesPlan.aspx"); return; } ///插入销售计划表体ID strSql = " insert into tmp_nc_xsjh_b (PK_TMP_NC_XSJH_B,PK_TMP_NC_XSJH,BLARGESSFLAG,CINVBASDOCID,CINVENTORYID,FROWNOTE,DR"; strSql += " ,FREE1,FREE2,FREE3,FREE4,FREE5,TS,NUM,INVNAME,UNITWEIGHT)"; strSql += "values('" + strPK_TMP_NC_XSJH_B + "','" + strPK_TMP_NC_XSJH + "','N','" + curRowDate_b[3].ToString() + "','" + curRowDate_b[4].ToString() + "','','0',"; strSql += "'','','','','',to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')," + curRowDate_b[6].ToString() + ",'" + curRowDate_b[5].ToString() + "'," + curRowDate_b[7].ToString() + ")"; cmd.CommandText = strSql; int exIn = cmd.ExecuteNonQuery(); } tx.Commit(); } catch (System.Data.OracleClient.OracleException E) { tx.Rollback(); MessageBox.ShowAndRedirect(this, "插入销售计划表头信息失败,请联系系统管理员!", "SalesPlan.aspx"); return; } ConnectionStringJXC.Close(); BindSalesList(); //MessageBox.ShowAndRedirect(this, "保存操作成功!", "SalesPlan.aspx"); } }