/// <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);
}
}