public ReturnObject Get(JObject args = null, int objOrder = -1)
{
// args - combined parameters from querystring and internally generated parameters
// objOrder - argument set ordinal value
JObject cmdArgs;
if (objOrder != -1)
{
cmdArgs = new JObject();
}
else
{
cmdArgs = args;
}
Dictionary <string, dynamic> prms = DALData.DAL.DALBuildParams(viewParams, cmdArgs);
ReturnObject ret = DALData.DAL.GetRecordset(new CommandParam()
{
cmdText = viewName,
cmdParams = prms
});
return(ret);
}
public override ReturnObject GetRecordset(CommandParam cmdParam,
bool returnFields = false,
bool withFields = false,
long pageNumber = 0, long pageSize = 0, JArray lookupParams = null)
{
DALOracleConnection cnn = new DALOracleConnection();
OracleConnection cmdConnect = cnn.Connection;
bool withConnParam = true;
ReturnObject returnValue = new ReturnObject();
string[] inlineLookupFieldsArr = InlineLookupFields(lookupParams);
try
{
if (cmdConnect == null)
{
withConnParam = false;
cmdConnect = new OracleConnection(connectionString);
}
if (cmdConnect.State != ConnectionState.Open)
{
cmdConnect.Open();
}
string cmdText = cmdParam.cmdText;
OracleCommand cmd = new OracleCommand(cmdText.TrimEnd(';'), cmdConnect);
bool isUsersTable = (cmdText.IndexOf("IMSA_TBL_USERS") != -1);
if (isUsersTable)
{
string UU = "UT";
}
// create parameters
if (cmdParam.cmdParams != null)
{
foreach (string key in cmdParam.cmdParams.Keys)
{
// cmd.Parameters.Add(key, cmdParam.cmdParams[key]);
cmd.Parameters.Add(key, ParamValue(cmdParam.cmdParams, key));
}
}
if (cmdText.IndexOf(" ") == -1)
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
OracleDataReader rdr = cmd.ExecuteReader();
// build fields list and return as .fields parameter
if (cmd.CommandType == CommandType.StoredProcedure || returnFields)
{
returnValue.result.fields = new List <FieldInfo>();
returnValue.result.returnString = "=>";
try
{
using (var schemaTable = rdr.GetSchemaTable())
{
returnValue.result.debugStrings.Add("Table Schema:");
returnValue.result.debugStrings.Add("- Schema column count:" + schemaTable.Columns.Count.ToString());
returnValue.result.debugStrings.Add("- Table column count:" + schemaTable.Rows.Count.ToString());
returnValue.result.debugStrings.Add("- Columns Information:");
foreach (DataColumn c in schemaTable.Columns)
{
returnValue.result.debugStrings.Add(" p:" + c.ColumnName + ", " + c.DataType.ToString());
}
foreach (DataColumn c in schemaTable.Columns)
{
returnValue.result.returnString += c.ColumnName + ", ";
}
foreach (DataRow row in schemaTable.Rows)
{
string ColumnName = row.Field <string>("ColumnName");
dynamic DataTypeName = row.Field <dynamic>("DataType").Name;
//short NumericPrecision = row.Field<short>("NumericPrecision");
//short NumericScale = row.Field<short>("NumericScale");
int ColumnSize = row.Field <int>("ColumnSize");
bool IsLong = row.Field <bool>("IsLong");
returnValue.result.debugStrings.Add("**** " + ColumnName + " ****");
returnValue.result.debugStrings.Add("- DataTypeName-" + DataTypeName);
//returnValue.result.debugStrings.Add("- NumericPrecision-" + NumericPrecision);
//returnValue.result.debugStrings.Add("- NumericScale-" + NumericScale);
returnValue.result.debugStrings.Add("- ColumnSize-" + ColumnSize);
returnValue.result.debugStrings.Add("- IsLong-" + _g.BlnToStr(IsLong));
//returnValue.result.returnString += row.Table.Columns.Count.ToString();
returnValue.result.fields.Add(new FieldInfo()
{
isParameter = false,
isLong = IsLong,
name = ColumnName,
type = DataTypeName,
size = ColumnSize
});
}
}
}
catch (Exception e)
{
returnValue.result.returnString += e.Message;
}
returnValue.result.returnString += "<=";
}
for (int fidx = 0; fidx < rdr.FieldCount; fidx++)
{
// this value will be assigned to the result's fieldNames object.
// only add fields that is not a displayField in a lookup definition
string fldName = rdr.GetName(fidx);
if (lookupParams != null)
{
//add only the fields that do not belong to the inline lookup
//if (lookupParams.SelectToken("$[?(@.displayField == '" + fldName + "')]") == null)
// returnValue.result.fieldsNames.Add(fldName);
if (!inlineLookupFieldsArr.Contains(fldName))
{
returnValue.result.fieldsNames.Add(fldName);
}
}
else
{
// add all field names from the dataReader
returnValue.result.fieldsNames.Add(fldName);
}
}
if (withFields)
{
returnValue.result.jsonReturnData = DALReaderToJSON(rdr);
returnValue.result.recordCount = returnValue.result.jsonReturnData.Count;
}
else
{
List <List <object> > retList = DALReaderToList(rdr, pageNumber, pageSize, lookupParams);
if (retList.Count > 0)
{
returnValue.result.recordCount = retList.Count - 1;
returnValue.result.returnDataParams = JObject.Parse(retList.ElementAt(0).ElementAt(0).ToString());
}
else
{
returnValue.result.recordCount = 0;
returnValue.result.returnDataParams = new JObject();
}
if (returnValue.result.recordCount > 0)
{
returnValue.result.returnData = retList.GetRange(1, (int)returnValue.result.recordCount);
}
// get field names
}
}
catch (Exception e)
{
//returnValue = mark +", "+ e.Message;
returnValue.result.result = _g.RES_ERROR;
returnValue.result.error = e.Message;
}
finally
{
// cleanup
//if (cmdConnect != null)
// if (cmdConnect.State == ConnectionState.Open)
// if (!withConnParam) cmdConnect.Close();
if (cnn != null)
{
cnn.Dispose();
}
}
return(returnValue);
}
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);
}