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