C#调用Oracle Procedure方法

C#调用Oracle Procedure，Oracle 的调用与MSSql的调用还是有些区别的，Function:

• 调用Oracle Procedure ，返回数据集

/// <summary>
/// 调用Oracle Procedure ，返回数据集
/// </summary>
/// <param name=”ds”>返回的数据集</param>
/// <param name=”strProcedureName”>存储过程名</param>
/// <param name=”connectionString”>连接字符串</param>
/// <param name=”ht”>参数列表</param>
/// <param name=”OutParaName”>参数名，必须是Procedure内接收游标类型的返回值参数名，否则会有异常</param>
/// <returns>是否执行成功</returns>
public static bool RunProcedure(ref DataSet ds, string strProcedureName, Hashtable ht, string OutParaName)
{

IDictionaryEnumerator enumerator = ht.GetEnumerator();

OracleConnection oraConn = new OracleConnection(ConnectionString);
OracleCommand oraCmd = new OracleCommand(strProcedureName, oraConn);
oraConn.Open();
oraCmd.CommandType = CommandType.StoredProcedure;
OracleParameter oracleparam = new OracleParameter(OutParaName, OracleType.Cursor);
oracleparam.Direction = ParameterDirection.Output;
oraCmd.Parameters.Add(oracleparam);
while (enumerator.MoveNext())
oraCmd.Parameters.Add(new OracleParameter(enumerator.Key.ToString(), enumerator.Value));
OracleDataAdapter odadapter = new OracleDataAdapter(oraCmd);
try
{
odadapter.Fill(ds);
return true;
}
catch (Exception ex)
{
return false;
}
finally
{
oraCmd.Parameters.Clear();
oraConn.Close();
}
}

• 调用Oracle Procedure ，返回字符串

/// <summary>
/// 调用Oracle Procedure ，返回字符串
/// </summary>
/// <param name=”conn”>连接字符串</param>
/// <param name=”trans”>事务</param>
/// <param name=”strProcedureName”>存储过程名</param>
/// <param name=”ht”>参数列表</param>
/// <param name=”OutParaName”>参数名，必须是Procedure内接收返回值的参数名，否则会有异常</param>
/// <param name=”OutParaValue”>Procedure的返回值，字符串</param>
/// <returns>是否执行成功</returns>
public static bool RunProcedure(OracleConnection conn, OracleTransaction trans, string strProcedureName, Hashtable ht, string OutParaName, ref string OutParaValue)
{
IDictionaryEnumerator enumerator = ht.GetEnumerator();

OracleCommand oraCmd = new OracleCommand(strProcedureName, conn);
oraCmd.CommandType = CommandType.StoredProcedure;
OracleParameter oracleparam = new OracleParameter(OutParaName, OracleType.VarChar, 1000);
oracleparam.Direction = ParameterDirection.Output;
oraCmd.Parameters.Add(oracleparam);
oraCmd.Transaction = trans;
while (enumerator.MoveNext())
oraCmd.Parameters.Add(new OracleParameter(enumerator.Key.ToString(), enumerator.Value));

try
{
oraCmd.ExecuteNonQuery();
OutParaValue = oraCmd.Parameters[OutParaName].Value.ToString().Trim();
return true;
}
catch (Exception ex)
{
return false;
}
finally
{
oraCmd.Parameters.Clear();
}
}

public static bool RunProcedure(string strProcedureName, Hashtable ht, string OutParaName, ref string OutParaValue)
{
OracleConnection oraConn = OpenConnection(ConnectionString);
OracleTransaction oraTran = oraConn.BeginTransaction();
bool reVal = false;
reVal=RunProcedure(oraConn, oraTran, strProcedureName, ht, OutParaName, ref OutParaValue);
oraTran.Commit();
return reVal;
}

public static int RunProcedure(string strProcedureName, Hashtable ht)
{
OracleConnection oraConn = OpenConnection(ConnectionString);
OracleTransaction oraTran = oraConn.BeginTransaction();
int reVal = 0;
reVal = RunProcedure(oraConn, oraTran, strProcedureName, ht);
oraTran.Commit();
return reVal;
}

• 执行存储过程，返回受影响的行数

/// <summary>
/// 执行存储过程，返回受影响的行数
/// </summary>
/// <param name=”conn”>连接方式</param>
/// <param name=”trans”>事务</param>
/// <param name=”strProcedureName”>存储过程名</param>
/// <param name=”ht”>参数集</param>
/// <returns>受影响的行数,-1 表示执行有Error，其他都是受影响的行数</returns>
public static int RunProcedure(OracleConnection conn, OracleTransaction trans, string strProcedureName, Hashtable ht)
{
int ReNum = 0;
IDictionaryEnumerator enumerator = ht.GetEnumerator();
OracleCommand oraCmd = new OracleCommand(strProcedureName, conn);
oraCmd.CommandType = CommandType.StoredProcedure;
oraCmd.Transaction = trans;
while (enumerator.MoveNext())
oraCmd.Parameters.Add(new OracleParameter(enumerator.Key.ToString(), enumerator.Value));

try
{
ReNum = oraCmd.ExecuteNonQuery();
return ReNum;
}
catch (Exception ex)
{
ReNum = -1;
return ReNum;
}
finally
{
oraCmd.Parameters.Clear();
}
}