/// <summary> /// Implements the functionality of IExecutant::GetPagedData /// </summary> /// <param name="fields">Comma-separated list of fields</param> /// <param name="fields">Array of fields to get</param> /// <param name="orderByField">Field for sorting</param> /// <param name="orderByASc">true if sort is ASCending</param> /// <param name="where">WHERE clausule</param> /// <param name="rowstart">1st register to fetch</param> /// <param name="rowend">Last register to fetch</param> /// <param name="tableBD">Table of the DataBase to SELECT</param> /// <param name="tableDT">Name of the DataTable returned</param> /// <param name="pk">Array of strings with the primary keys (CANNOT be null)</param> /// <returns>DataTable with the data</returns> protected DataTable DoGetPagedData(string fields, string orderByField, bool orderByAsc, string where, int rowstart, int rowend, string tableBD, string tableDT, string[] pk) { // Converts possible null or "" values if (pk == null) { throw new ArgumentNullException("pk", "PK cannot be null when paging!"); } if (orderByField == string.Empty) { orderByField = null; } if (!orderByAsc && orderByField != null) { orderByField = orderByField + " DESC"; } // if (tableBD == "OPERATIONS") // { // if (where == "OPE_VALID=1 AND OPE_DELETED=0") // { // where = "OPE_VALID=1 AND OPE_DELETED=0 AND OPE_RANK >= " + rowstart + " AND OPE_RANK <= " + rowend; // orderByField = "OPE_RANK ASC"; // } // } // else if ((tableBD == "ALARMS_HIS") && (where == "")) // { // where = "HALA_RANK >= " + rowstart + " AND HALA_RANK <= " + rowend; // orderByField = "HALA_RANK ASC"; // } // else if ((tableBD == "FINES_HIS") && (where == "")) // { // where = "HFIN_RANK >= " + rowstart + " AND HFIN_RANK <= " + rowend; // orderByField = "HFIN_RANK ASC"; // } // else if ((tableBD == "MSGS_HIS") && (where == "")) // { // where = "HMSG_RANK >= " + rowstart + " AND HMSG_RANK <= " + rowend; // orderByField = "HMSG_RANK ASC"; // } // else if ((tableBD == "OPERATIONS_HIS") && (where == "")) // { // where = "HOPE_RANK >= " + rowstart + " AND HOPE_RANK <= " + rowend; // orderByField = "HOPE_RANK ASC"; // } Database d = DatabaseFactory.GetDatabase(); DataTable dt = null; if (rowend == -1) { dt = d.FillDataTable(fields, tableBD, orderByField, where, tableDT, -1); DataColumn [] dtPK = new DataColumn[pk.Length]; for (int i = 0; i < pk.Length; i++) { dtPK[i] = dt.Columns[pk[i]]; } dt.PrimaryKey = dtPK; } else { dt = d.FillPagedDataTable(fields, tableDT, orderByField, where, tableDT, rowstart, rowend, pk); } return(dt); }
/// <summary> /// Get the Status from table Commands /// </summary> /// <param name="idUnit"></param> /// <returns></returns> public int GetStatusCmdFromUnit(int idUnit, int idCommandDef, ref string szResult, ref int nStatus, ref DateTime dtTime, ref int nCmdID) { int nRes = -1; IDbConnection con = null; ILogger localLogger = null; try { localLogger = DatabaseFactory.Logger; szResult = ""; nStatus = -1; dtTime = DateTime.MinValue; // Getting Databasse, open connection Database d = DatabaseFactory.GetDatabase(); con = d.GetNewConnection(); con.Open(); string ssql = "SELECT CMD_RESULT,CMD_STATUS,CMD_DATE_RESULT,CMD_ID FROM COMMANDS " + "WHERE CMD_ID = " + "(SELECT NVL(MAX(CMD_ID),0) FROM COMMANDS WHERE CMD_COMMAND_ID = @COMMANDS.CMD_COMMAND_ID@)" + "AND CMD_UNI_ID = @COMMANDS.CMD_UNI_ID@"; DataTable dtUnit = d.FillDataTable(ssql, "COMMANDS", idCommandDef, idUnit); foreach (DataRow dr in dtUnit.Rows) { if (!(dr["CMD_RESULT"] == DBNull.Value)) { szResult = Convert.ToString(dr["CMD_RESULT"]); } if (!(dr["CMD_STATUS"] == DBNull.Value)) { nStatus = Convert.ToInt32(dr["CMD_STATUS"]); } if (!(dr["CMD_DATE_RESULT"] == DBNull.Value)) { dtTime = Convert.ToDateTime(dr["CMD_DATE_RESULT"]); } if (!(dr["CMD_ID"] == DBNull.Value)) { nCmdID = Convert.ToInt32(dr["CMD_ID"]); } nRes++; } con.Close(); } catch (Exception e) { // Some error... if (localLogger != null) { localLogger.AddLog("[CmpCommandsDB::InsertCommand]: Closing...", LoggerSeverities.Debug); } con.Close(); throw e; // Propagate the error back! } finally { // Close connection... try { con.Close(); } catch (Exception) { } } return(nRes); }
/// <summary> /// Gets DataTable with the SQL passed. /// </summary> /// <param name="rawsql">SQL string to run against database</param> /// <param name="values">values of parameters</param> /// <returns>DataTable with data</returns> public static DataTable GetRawData(string rawsql, object[] values) { Database d = DatabaseFactory.GetDatabase(); return(d.FillDataTable(rawsql, "TABLE", values)); // by default TABLE is used as table name. }
public int GetCardData(int nTransId, out string sCCNumber, out string sCCName, out DateTime dtExprtnDate, out int iCCCodServ, out string strCCDiscData) { Database d = null; // Database ILogger localLogger = null; // Logger to trace DataTable dt = null; int res = -1; sCCNumber = ""; sCCName = ""; dtExprtnDate = DateTime.Now; iCCCodServ = -999; strCCDiscData = ""; try { // Getting Database d = DatabaseFactory.GetDatabase(); if (d == null) { return(-1); } // Getting Logger localLogger = DatabaseFactory.Logger; if (localLogger != null) { localLogger.AddLog("[CmpCreditCardsTransactionsDB ]: Getting Connection...", LoggerSeverities.Debug); } if (localLogger != null) { localLogger.AddLog("[CmpCreditCardsTransactionsDB ]: Opening...", LoggerSeverities.Debug); } dt = d.FillDataTable(string.Format("select " + "CCT_NUMBER," + "CCT_NAME," + "TO_CHAR(CCT_EXPRTN_DATE,'hh24missddmmyy') CCT_EXPRTN_DATE," + "CCT_CODSERV," + "CCT_DISC_DATA " + "from CREDIT_CARDS_TRANSACTIONS " + "where CCT_ID={0}", nTransId), "CARDS"); if (dt.Rows.Count == 1) { sCCNumber = Decrypt(nTransId.ToString(), dt.Rows[0].ItemArray[dt.Columns.IndexOf("CCT_NUMBER")].ToString()); sCCName = Decrypt(nTransId.ToString(), dt.Rows[0].ItemArray[dt.Columns.IndexOf("CCT_NAME")].ToString()); dtExprtnDate = OPS.Comm.Dtx.StringToDtx(dt.Rows[0].ItemArray[dt.Columns.IndexOf("CCT_EXPRTN_DATE")].ToString()); iCCCodServ = Convert.ToInt32(dt.Rows[0].ItemArray[dt.Columns.IndexOf("CCT_CODSERV")].ToString()); strCCDiscData = Decrypt(nTransId.ToString(), dt.Rows[0].ItemArray[dt.Columns.IndexOf("CCT_DISC_DATA")].ToString()); res = 1; } } catch (Exception e) { res = -1; if (localLogger != null) { localLogger.AddLog("[CmpCreditCardsTransactionsDB ]: RollBack" + e.Message, LoggerSeverities.Debug); } throw e; // Propagate the error back! } finally { // Close connection... and release the mutex! try { if (localLogger != null) { localLogger.AddLog("[CmpCreditCardsTransactionsDB ]: Closing...", LoggerSeverities.Debug); } } catch (Exception e) { res = -1; if (localLogger != null) { localLogger.AddLog("[CmpCreditCardsTransactionsDB ]: RollBack" + e.Message, LoggerSeverities.Debug); } } } return(res); }