/// <summary> /// 根据存储过程获取DataTable /// </summary> /// <param name="ProcName"></param> /// <param name="paras"></param> /// <returns></returns> public DataTable GetTableByProc(string ProcName, CmdParameter[] paras) { OracleParameter[] pas = CFunctions.ConvertToOraParameter(paras); DataTable dt = new DataTable();; using (OracleConnection conn = new OracleConnection(strConn)) { using (OracleDataAdapter da = new OracleDataAdapter(ProcName, conn)) try { da.SelectCommand.CommandType = CommandType.StoredProcedure; if (pas != null) { da.SelectCommand.Parameters.AddRange(pas); } //da.SelectCommand.Parameters.Add(new OracleParameter("PSQL",OracleType.VarChar,500)); //da.SelectCommand.Parameters["PSQL"].Value = " 1=1 "; //da.SelectCommand.Parameters["PSQL"].Direction = ParameterDirection.Input; //da.SelectCommand.Parameters.Add(new OracleParameter("v_cur", OracleType.Cursor)); //da.SelectCommand.Parameters["v_cur"].Direction = ParameterDirection.Output; da.Fill(dt); dt.TableName = ProcName; } catch (Exception ex) { CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex); throw (ex); } } return(dt); }
/// <summary> /// 根据存储过程获取DataTable,参数的修改会被返回 /// </summary> /// <param name="ProcName"></param> /// <param name="paras"></param> /// <returns></returns> public DataTable GetTableByProc(string ProcName, ref CmdParameter[] paras) { OracleParameter[] pas = CFunctions.ConvertToOraParameter(paras); DataTable dt = new DataTable();; using (OracleConnection conn = new OracleConnection(strConn)) { using (OracleDataAdapter da = new OracleDataAdapter(ProcName, conn)) try { da.SelectCommand.CommandType = CommandType.StoredProcedure; if (pas != null) { da.SelectCommand.Parameters.AddRange(pas); } da.Fill(dt); dt.TableName = ProcName; paras = CFunctions.RecoverParameter(pas); } catch (Exception ex) { CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex); throw (ex); } } return(dt); }
/// <summary> /// 执行存储过程,带参数,用于增删改 /// </summary> /// <param name="ProcName">存储过程名称</param> /// <param name="pars">参数集合</param> /// <returns></returns> public int ExcuteProc(string ProcName, CmdParameter[] paras) { OracleParameter[] pars = CFunctions.ConvertToOraParameter(paras); //CFunctions.HandleBlobParam( ref pars); using (OracleConnection connection = new OracleConnection(strConn)) { int result; try { connection.Open(); OracleTransaction tx = connection.BeginTransaction(); OracleCommand command = new OracleCommand(ProcName, connection); command.Transaction = tx; command.CommandType = CommandType.StoredProcedure; foreach (OracleParameter parameter in pars) { if (parameter.OracleType == OracleType.Blob && parameter.Value != null && !string.IsNullOrEmpty(parameter.Value.ToString())) { parameter.Value = getOdb(connection, tx, parameter.Value); } command.Parameters.Add(parameter); } //添加一个名为ReturnValue的Output参数,用于返回值 command.Parameters.Add(new OracleParameter("ReturnValue", OracleType.Int32, 4, ParameterDirection.Output, false, 0, 0, string.Empty, DataRowVersion.Default, null)); //command = BuildIntCommand(connection, ProcName, pars); command.ExecuteNonQuery(); tx.Commit(); result = (int)command.Parameters["ReturnValue"].Value; } catch (Exception ex) { CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex); throw ex; } //Connection.Close(); return(result); } }
public DataSet GetDataSetByProc(string ProcName, CmdParameter[] paras) { OracleParameter[] pas = CFunctions.ConvertToOraParameter(paras); using (OracleConnection connection = new OracleConnection(strConn)) { DataSet dataSet = new DataSet(); try { connection.Open(); OracleDataAdapter sqlDA = new OracleDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, ProcName, pas); sqlDA.Fill(dataSet, ProcName); connection.Close(); } catch (Exception ex) { CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex); throw (ex); } return(dataSet); } }