public static List<Reader> getAllReadersList() { Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Entry Point", Logger.logLevel.Info); Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "", Logger.logLevel.Debug); List<Reader> lstReader = new List<Reader>(); SqlConnection conn = null; SqlDataReader reader = null; try { // create and open a connection object conn = ConnectionManager.getConnection(); conn.Open(); String query = "SELECT TOP 50 id,description,area,facility FROM reader"; SqlCommand command = new SqlCommand(query, conn); using (reader = command.ExecuteReader()) { while (reader.Read()) { String strReader = reader.GetSqlValue(1).ToString().Trim(); if (!(strReader.Equals("") || strReader.Equals("null") || strReader.Equals("NULL") || strReader.Equals("Null"))) { Reader readerObj = new Reader(); readerObj.key = reader.GetSqlValue(0).ToString().Trim(); readerObj.value = reader.GetSqlValue(1).ToString().Trim(); lstReader.Add(readerObj); } } } } catch (Exception ex) { var stackTrace = new StackTrace(ex, true); var line = stackTrace.GetFrame(0).GetFileLineNumber(); Logger.LogExceptions(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, ex.Message, line.ToString(), Logger.logLevel.Exception); } finally { if (conn != null) { conn.Close(); } if (reader != null) { reader.Close(); } } lstReader = lstReader.OrderBy(x => x.ReaderDesc).ToList(); Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Exit Point", Logger.logLevel.Debug); return lstReader; }
public static List<Reader> getReadersByFacilityList(String facility, String area, String categoryId, String keyStroke) { Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Entry Point", Logger.logLevel.Info); Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, facility + area + categoryId + keyStroke, Logger.logLevel.Debug); List<Reader> lstReader = new List<Reader>(); SqlConnection conn = null; SqlDataReader reader = null; try { // create and open a connection object conn = ConnectionManager.getConnection(); conn.Open(); String query = ""; if (facility.Equals("null") && area.Equals("null")) { query = "SELECT TOP 50 id,description,area,facility FROM reader where description like '%" + keyStroke + "%'"; } else if (!facility.Equals("null") && !area.Equals("null")) { query = "SELECT TOP 50 id,description,area,facility FROM reader where facility = @facility_id and area = @area_id AND description like '%" + keyStroke + "%'"; } else if (!facility.Equals("null")) { query = "SELECT TOP 50 id,description,area,facility FROM reader where facility = @facility_id AND description like '%" + keyStroke + "%'"; } else { query = "SELECT TOP 50 id,description,area,facility FROM reader where area = @area_id AND description like '%" + keyStroke + "%'"; } SqlCommand command = new SqlCommand(query, conn); if (!facility.Equals("null")) { command.Parameters.AddWithValue("@facility_id", facility.Trim()); } if (!area.Equals("null")) { command.Parameters.AddWithValue("@area_id", area.Trim()); } //DataTable myTable = new DataTable(); // myTable.Load(reader); using (reader = command.ExecuteReader()) { while (reader.Read()) { String strReader = reader.GetSqlValue(1).ToString().Trim(); if (!(strReader.Equals("") || strReader.Equals("null") || strReader.Equals("NULL") || strReader.Equals("Null"))) { Reader readerObj = new Reader(); readerObj.key = reader.GetSqlValue(0).ToString().Trim(); readerObj.value = reader.GetSqlValue(1).ToString().Trim(); lstReader.Add(readerObj); } } } } catch (Exception ex) { var stackTrace = new StackTrace(ex, true); var line = stackTrace.GetFrame(0).GetFileLineNumber(); Logger.LogExceptions(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, ex.Message, line.ToString(), Logger.logLevel.Exception); } finally { if (conn != null) { conn.Close(); } if (reader != null) { reader.Close(); } } lstReader = lstReader.OrderBy(x => x.ReaderDesc).ToList(); Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Exit Point", Logger.logLevel.Debug); return lstReader; }