/// <summary>
/// 查询DataSet
/// </summary>
/// <param name="ConnectionString">连接串</param>
/// <param name="SQLString"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public DataSet Query(string ConnectionString, OraParameter param)
{
using (OracleConnection connection = new OracleConnection(ConnectionString))
{
OracleCommand cmd = new OracleCommand();
cmd.BindByName = true;
PrepareCommand(cmd, connection, null, param.SqlCmdTxt, param.Value.ToArray());
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds);
cmd.Parameters.Clear();
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
return(ds);
}
}
}
/// <summary>
/// 执行有返回值的存储过程（Oracle）
/// 方法结果不为空时，结果为错误信息提示；执行成功是方法返回结果为NULL
/// </summary>
/// <param name="ProcName">过程名称</param>
/// <param name="ProcParam">参数</param>
/// <param name="OutKey">返回值Key</param>
/// <param name="OutKeyValue">实际返回键值</param>
/// <returns></returns>
public string ExecProc(string ConnectionString, string ProcName, OraParameter ProcParam, ref IDictionary <string, object> OutKeyValue)
{
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
using (OracleCommand cmd = new OracleCommand(ProcName, conn))
{
cmd.BindByName = true;
cmd.CommandType = CommandType.StoredProcedure;
foreach (var p in ProcParam.Value)
{
cmd.Parameters.Add(p);
}
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
cmd.ExecuteNonQuery();
tx.Commit();
//值返回
foreach (var pKey in ProcParam.ProcParametersName)
{
if (cmd.Parameters.Contains(pKey))
{
OutKeyValue.Add(pKey, cmd.Parameters[pKey].Value);
}
}
return(string.Empty);
}
catch (Oracle.ManagedDataAccess.Client.OracleException E)
{
tx.Rollback();
return(E.Message.ToString());
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}
}
/// <summary>
/// 执行无返回值的存储过程（Oracle）
/// 结果为空时执行成功，不为空时为执行失败的错误提示
/// </summary>
/// <param name="ProcName"></param>
/// <param name="ProcParam"></param>
/// <returns></returns>
public string ExecProcNoReturn(string ConnectionString, OraParameter ProcParam)
{
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
using (OracleCommand cmd = new OracleCommand(ProcParam.SqlCmdTxt, conn))
{
cmd.BindByName = true;
cmd.CommandType = CommandType.StoredProcedure;
foreach (var p in ProcParam.Value)
{
cmd.Parameters.Add(p);
}
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
cmd.ExecuteNonQuery();
tx.Commit();
return(string.Empty);
}
catch (Oracle.ManagedDataAccess.Client.OracleException E)
{
tx.Rollback();
return(E.Message.ToString());
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}
}