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 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);
}
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 abstract ReturnObject GetRecordset(CommandParam cmdParam, bool returnFields = false, bool withFields = false,
long pageNumber = 0, long pageSize = 0, JArray lookupParams = null);
public abstract ReturnObject Excute(CommandParam cmdParam, dynamic cmdConnectDynamic = null, dynamic cmdTransactDynamic = null);
public abstract dynamic GetDataReaderCommand(CommandParam cmdParam);
public abstract DataTable GetDataTable(CommandParam cmdParam);
public abstract JArray GetJSONArray(CommandParam cmdParam);
public abstract List <Dictionary <string, dynamic> > GetDictionaryArray(CommandParam cmdParam);
public static List <CommandParam> BuildCommandParamsListFromData(JObject values, JObject args = null)
{
List <CommandParam> ret = new List <CommandParam>();
DALTableLink tableLink = new DALTableLink(_g.TKVStr(values, _g.KEY_TABLE_CODES), AppTables, args);
JProperty data = _g.GetJPropery(values, "data");
if (data == null)
{
return(null);
}
string dataType = data.Value.Type.ToString().ToLower();
JArray dataArr = new JArray();
if (dataType == "array")
{
dataArr = (JArray)data.Value;
}
else
{
dataArr.Add((JObject)data.Value);
}
//JArray dataArr = (dataType == "array" ? data : new JArray() { data };
foreach (JObject jo in dataArr)
{
Int64 parentKey = _g.TKV64(jo, tableLink.key);
string[] action = tableLink.table.GetActionFromData(jo).Split('|');
bool isNoAction = action[0] == _g.RES_NO_ACTION;
bool isInsert = action[0] == "insert";
bool isUpdate = action[0] == "update";
bool isDelete = action[0] == "delete";
if (isInsert)
{
jo.Add(_g.KEY_NEWREC_TEMP_ID, parentKey);
// set new parent key
parentKey = Convert.ToInt64(action[1]);
jo[tableLink.key] = parentKey;
}
CommandParam cmdParam = new CommandParam();
if (!isNoAction)
{
cmdParam.cmdInput = new JObject();
foreach (JProperty jp in (JToken)jo)
{
if (jp.Name != tableLink.childCode)
{
cmdParam.cmdInput.Add(jp.Name, jp.Value);
}
}
if (isDelete)
{
// create command parameter if action is to be performed
cmdParam.cmdText = tableLink.table.SQLText(action[0]);
cmdParam.cmdParams = new Dictionary <string, dynamic>()
{
[tableLink.key] = parentKey
};
}
else
{
DALTableFieldParams tblParams = new DALTableFieldParams(jo, tableLink.stamps, tableLink.table, isUpdate, null);
//cmdParam.tmpCols = tblParams.columns;
//cmdParam.tmpKeys = tblParams.keyFields;
cmdParam.cmdText = tblParams.SQLText;
cmdParam.cmdParams = tblParams.parameters;
if (isInsert)
{
cmdParam.cmdOutput = new JObject();
cmdParam.cmdOutput.Add(_g.KEY_NEWREC_TEMP_ID, _g.TKV64(jo, _g.KEY_NEWREC_TEMP_ID));
cmdParam.cmdOutput.Add(tableLink.key, parentKey);
}
}
} // end of NOT isNoAction
else
{
cmdParam.cmdText = "no action!";
cmdParam.cmdParams = new Dictionary <string, dynamic>()
{
[tableLink.key] = parentKey
};
}
ret.Add(cmdParam);
if (tableLink.hasChild && jo.ContainsKey(tableLink.childCode))
{
// if parent JObject contains a property with name same as the child table code
JArray childArr = _g.TKVJArr(jo, tableLink.childCode); // get children array
foreach (JObject cJo in childArr)
{
string[] childAction = tableLink.childTable.GetActionFromData(cJo).Split('|');
bool isChildNoAction = childAction[0] == _g.RES_NO_ACTION;
bool isChildInsert = childAction[0] == "insert";
bool isChildUpdate = childAction[0] == "update";
bool isChildDelete = childAction[0] == "delete";
Int64 childKey = _g.TKV64(cJo, tableLink.childKey);
if (isChildInsert)
{
// childAction[0]:action, childAction[1]:new AutoId
if (cJo.ContainsKey(tableLink.childParentKey))
{
cJo[tableLink.childParentKey] = parentKey; // update parent key value
}
else
{
cJo.Add(tableLink.childParentKey, parentKey); // create new field token
}
cJo.Add(_g.KEY_NEWREC_TEMP_ID, childKey); // add property containing the temporary id which will be used to find the matching record in the client-side
// set new child key
childKey = Convert.ToInt64(childAction[1]);
cJo[tableLink.childKey] = childKey;
}
// generate sql statements and parameters
CommandParam cmdChildParam = new CommandParam();
if (!isChildNoAction)
{
cmdChildParam.cmdInput = cJo;
if (isChildDelete)
{
// create command parameter if action is to be performed
cmdChildParam.cmdText = tableLink.table.SQLText(childAction[0]);
cmdChildParam.cmdParams = new Dictionary <string, dynamic>()
{
[tableLink.childKey] = childKey
};
}
else
{
DALTableFieldParams tblChildParams = new DALTableFieldParams(cJo, tableLink.childStamps, tableLink.childTable, isChildUpdate, null);
//cmdChildParam.tmpCols = tblChildParams.columns;
//cmdChildParam.tmpKeys = tblChildParams.keyFields;
cmdChildParam.cmdText = tblChildParams.SQLText;
cmdChildParam.cmdParams = tblChildParams.parameters;
if (isChildInsert)
{
cmdChildParam.cmdOutput = new JObject();
cmdChildParam.cmdOutput.Add(_g.KEY_NEWREC_TEMP_ID, _g.TKV64(cJo, _g.KEY_NEWREC_TEMP_ID));
cmdChildParam.cmdOutput.Add(tableLink.childKey, childKey);
}
}
} // end of NOT isNoAction
else
{
cmdChildParam.cmdText = "no action!";
cmdChildParam.cmdParams = new Dictionary <string, dynamic>()
{
[tableLink.childKey] = childKey
};
}
ret.Add(cmdChildParam);
} // end of child foreach
} // end of if hasChild and parent object contains a property named similar to the child table code
} // end of parent foreach
return(ret);
}
public static List <CommandParam> MergeCommandParams(List <CommandParam> master, CommandParam additionalParam)
{
List <CommandParam> retVal = master;
if (retVal == null)
{
retVal = new List <CommandParam>();
}
retVal.Add(additionalParam);
return(retVal);
}
public override DataTable GetDataTable(CommandParam cmdParam)
{
return(null);
}
public override dynamic GetDataReaderCommand(CommandParam cmdParam)
{
return(null);
}
public override List <Dictionary <string, dynamic> > GetDictionaryArray(CommandParam cmdParam)
{
return(null);
}
public override JArray GetJSONArray(CommandParam cmdParam)
{
return(null);
}
public override ReturnObject Excute(CommandParam cmdParam, dynamic cmdConnectDynamic = null, dynamic cmdTransactDynamic = null)
{
return(null);
}