public bool GetTempGetDocsIDs(ref List <PawnDocRegVO> docList, int fetchCount, bool isFirst, int lastIndex) { bool ret = true; string retVal = ""; OracleDataReader reader = null; if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED) { log.Error(" from docListGet :DB Connection not established: Search aborted"); return(false); } var stopwatch = new Stopwatch(); stopwatch.Start(); try { using (var connection = new OracleConnection()) { connection.ConnectionString = DBConnector.getInstance().databaseServiceName(); connection.Open(); string sql = ""; if (isFirst) { sql = "SELECT REG.ID,REG.STORAGE_ID,CREATIONDATE FROM CCSOWNER.PAWNDOCUMENTREGISTRY REG" + " WHERE REG.CREATIONDATE <= TRUNC(SYSDATE) - 90 AND REG.ARCH_STATUS IS NULL" + " AND ROWNUM<=" + fetchCount + " ORDER BY ID"; } else { sql = "SELECT REG.ID,REG.STORAGE_ID,CREATIONDATE FROM CCSOWNER.PAWNDOCUMENTREGISTRY REG" + " WHERE REG.CREATIONDATE <= TRUNC(SYSDATE) - 90 AND REG.ARCH_STATUS IS NULL AND REG.ID > " + lastIndex + " AND ROWNUM <=" + fetchCount + " ORDER BY REG.ID"; } using (var oracleCommand = new OracleCommand(sql, connection)) { log.Info(string.Format("Executing: {0}", sql)); reader = oracleCommand.ExecuteReader(); if (reader.RowSize > 0 && reader.HasRows) { docList = new List <PawnDocRegVO>(); CouchConsoleApp.vo.PawnDocRegVO vo = null; while (reader.Read()) { vo = new CouchConsoleApp.vo.PawnDocRegVO(); vo.DocID = Utilities.GetIntegerValue(reader["ID"]); vo.StorageID = Utilities.GetStringValue(reader["STORAGE_ID"]); vo.CreationDate = Utilities.GetDateTimeValue(reader["CREATIONDATE"]); docList.Add(vo); } ret = false; } } } } catch (Exception e) { log.Error("Recovery document get failed:", e); log.Error(e.StackTrace); //log.Debug(e.StackTrace.ToString()); } finally { stopwatch.Stop(); log.Info(string.Format("Time Taken for Recovery doc get : {0} Msec", stopwatch.Elapsed)); if (reader != null) { reader.Dispose(); reader.Close(); } //oracleCommand = null; } return(ret); }
public bool GetDocumentSets(int startWith, out List <PawnDocRegVO> docList, out int totalCount, out int errorCode, out string errorMSG) { //OracleDataReader reader = null; totalCount = 0; errorCode = 0; errorMSG = ""; bool ret = false; docList = new List <PawnDocRegVO>(); if (DBConnector.getInstance().getStatus() != DBConnector.Status.INITIALIZED) { log.Error(" from docListGet :DB Connection not established: Search aborted"); return(false); } var stopwatch = new Stopwatch(); stopwatch.Start(); DataTable tbl = new DataTable("ARCHIVEDOCUMENT"); try { using (var connection = new OracleConnection()) { connection.ConnectionString = DBConnector.getInstance().databaseServiceName(); connection.Open(); using (var oracleCommand = new OracleCommand(null, connection)) { oracleCommand.CommandText = "ccsowner.PAWN_GENERATE_DOCUMENTS.getDocumentsToArchive"; oracleCommand.CommandType = CommandType.StoredProcedure; oracleCommand.Parameters.Add("p_current_date", OracleDbType.Varchar2, DateTime.Now.ToShortDateString(), ParameterDirection.Input); oracleCommand.Parameters.Add("p_time_span", OracleDbType.Int32, 90, ParameterDirection.Input); oracleCommand.Parameters.Add("p_record_start", OracleDbType.Int32, startWith, ParameterDirection.Input); oracleCommand.Parameters.Add("o_total_documents", OracleDbType.Int32, DBNull.Value, ParameterDirection.Output); oracleCommand.Parameters.Add("o_documents", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output); oracleCommand.Parameters.Add("o_return_code", OracleDbType.Decimal, DBNull.Value, ParameterDirection.Output); oracleCommand.Parameters.Add("o_return_text", OracleDbType.Varchar2, 32768, DBNull.Value, ParameterDirection.Output); log.Info("Executing..:" + oracleCommand.CommandText); log.Info("Input " + startWith); oracleCommand.ExecuteNonQuery(); totalCount = Utilities.GetIntegerValue(oracleCommand.Parameters[3].Value); errorCode = Utilities.GetIntegerValue(oracleCommand.Parameters[5].Value); errorMSG = Utilities.GetStringValue(oracleCommand.Parameters[6].Value); log.Info("Total Documents " + totalCount); log.Info("o_return_code " + errorCode); log.Info("o_return_text " + errorMSG); if (errorCode != 0) { return(false); } var oraRefCursor = (OracleRefCursor)oracleCommand.Parameters[4].Value; if (oraRefCursor != null && oraRefCursor.IsNull == false) { using (var reader = oraRefCursor.GetDataReader()) { if (reader.RowSize > 0 && reader.HasRows) { CouchConsoleApp.vo.PawnDocRegVO vo = null; while (reader.Read()) { vo = new CouchConsoleApp.vo.PawnDocRegVO(); vo.DocID = Utilities.GetIntegerValue(reader["ID"]); vo.StorageID = Utilities.GetStringValue(reader["STORAGE_ID"]); vo.CreationDate = Utilities.GetDateTimeValue(reader["STORAGE_DATE"]); docList.Add(vo); } ret = true; } } } oraRefCursor.Dispose(); } log.Info("Count from Proc " + docList.Count); } } catch (Exception e) { log.Error("GetDocumentSets", e); errorMSG = e.Message; ret = false; } finally { stopwatch.Stop(); /*if (reader != null) * { * reader.Dispose(); * reader.Close(); * }*/ log.Info(string.Format("Time Taken for GetDocumentSets : {0} Msec", stopwatch.ElapsedMilliseconds)); //oracleCommand = null; } log.Debug("Data Returned.." + tbl); return(ret); }