public override List <ReturnObject> Excute(List <CommandParam> commandParams, bool commit = false)
{
// Execute collection of commands using a single transaction object
List <ReturnObject> retVal = new List <ReturnObject>();
if (commandParams.Count() == 0)
{
return(retVal);
}
// Creat a common connection/transaction object to
// be used when executing commands
DALOracleConnection cnn = new DALOracleConnection(true);
CommandParam cmdPrm = null;
try
{
bool errorEncountered = false;
foreach (CommandParam cp in commandParams)
{
cmdPrm = cp;
// pass common connection and transaction when executing individual commands
ReturnObject ret = Excute(cp, cnn.Connection, cnn.Transaction);
JObject retData = new JObject();
retData.Add("tempKey", cp.tempKey);
retData.Add("newKey", cp.newKey);
retData.Add("cmdOutput", cp.cmdOutput);
ret.result.returnDataParams = retData;
if (cp.table != null)
{
ret.returnCode = cp.table.tableCode;
}
// check if error was encountered when executing individual command,
// If error is enountered, return Exception message and Rollback all
// transactions prior to the error.
retVal.Add(ret);
errorEncountered = ret.result.result == _g.RES_ERROR;
// exit for loop if exception is encountered
if (errorEncountered)
{
break;
}
ret.result.returnObject = null;
}
// if error has not occur during execution of individual commands,
// commit changes and dispose connection and transaction objects
// using the Commit method of the connection object (DALOleDbConnection)
if (commit && !errorEncountered)
{
cnn.Commit(); // commit and dispose
}
else
{
// raise exception
throw new Exception("Error posting record, " + (cmdPrm != null ? cmdPrm.cmdText : ""));
}
}
catch (Exception e)
{
// if error occured, rollback and dispose connection and transaction objects
// by calling the Rollback method of the connection object (DALOleDbConnection)
if (cnn != null)
{
cnn.Rollback();
}
ReturnObject errRet = new ReturnObject();
errRet.returnType = _g.RES_ERROR;
errRet.result.exceptionMessage = e.Message;
errRet.result.result = _g.RES_ERROR;
retVal.Add(errRet);
// return an error message
// return "Error posting multiple updates: " + e.Message;
}
return(retVal);
}
public override ReturnObject Excute(CommandParam cmdParam,
dynamic cmdConnectDynamic = null, dynamic cmdTransactDynamic = null)
{
/*************************************************
* Execute individual command
*************************************************/
ReturnObject ret = new ReturnObject();
DALOracleConnection cnn = null;
ret.result.affectedRecords = -5;
globalError = "";
try
{
// OleDbConnection cmdConnect = null;
OracleConnection cmdConnect = null;
//OleDbTransaction cmdTransact = null;
OracleTransaction cmdTransact = null;
//if (cmdConnectDynamic != null) cmdConnect = (OleDbConnection)cmdConnectDynamic;
if (cmdConnectDynamic != null)
{
cmdConnect = (OracleConnection)cmdConnectDynamic;
}
//if (cmdTransactDynamic != null) cmdTransact = (OleDbTransaction)cmdTransactDynamic;
if (cmdTransactDynamic != null)
{
cmdTransact = (OracleTransaction)cmdTransactDynamic;
}
bool withConnParam = true;
// if common connection and transaction is not supplied
if (cmdConnect == null)
{
// if no connection parameter is passed
// cnn = new DALOleDbConnection();
cmdConnect = cnn.Connection;
cmdTransact = cnn.Transaction;
// set connection flag to be used during cleanup process
withConnParam = false;
}
// open connection if still closed
if (cmdConnect.State != ConnectionState.Open)
{
cmdConnect.Open();
}
// initialize command object
string cmdText = cmdParam.cmdText;
OracleCommand cmd = new OracleCommand(cmdText.TrimEnd(';'), cmdConnect);
// set command transaction object
if (cmdTransact != null)
{
cmd.Transaction = cmdTransact;
}
// add parameters to command object
foreach (string key in cmdParam.cmdParams.Keys)
{
// cmd.Parameters.Add(key, cmdParam.cmdParams[key].Value);
cmd.Parameters.Add(key, ParamValue(cmdParam.cmdParams, key));
}
// if passed command text is not a SQL statement, CommandType is StoredProcedure, else Text
cmd.CommandType = (cmdText.IndexOf(" ") == -1 ? CommandType.StoredProcedure : CommandType.Text);
// Execute Command
ret.result.affectedRecords = cmd.ExecuteNonQuery();
Int64 cnt = cmd.Parameters.Count;
// cleanup
// connection was initiated within this method
if (!withConnParam && cnn != null)
{
cnn.Commit();
}
ret.result.result = _g.RES_SUCCESS;
ret.result.exceptionMessage = "";
}
catch (Exception e)
{
// Execute rollback only if connection was initiated within this method
if (cnn != null)
{
cnn.Rollback();
}
globalError = e.Message;
ret.result.result = _g.RES_ERROR;
ret.result.exceptionMessage = "Execute(...) [single]: " + e.Message;
}
return(ret);
}