public static Dictionary <String, String> getAllEntires(int type, String companyId, String divisionId, String keyStroke) { if (type == 1) { return(EmpId.getEmpByCompany(companyId, divisionId, keyStroke)); } else if (type == 2) { return(SSN.getSSNByCompany(companyId, divisionId, keyStroke)); } else if (type == 3) { return(ZipCode.getZipByCompany(companyId, divisionId, keyStroke)); } else if (type == 4) { return(Address.getCityByCompany(companyId, divisionId, keyStroke)); } return(new Dictionary <string, string>()); }
public static Dictionary <String, String> getAllEntires(int type) { if (type == 1) { return(EmpId.getAllEmpId()); } else if (type == 2) { return(SSN.getAllSSN()); } else if (type == 3) { return(ZipCode.getAllZipCode()); } else if (type == 4) { return(Address.getAllCities()); } return(new Dictionary <string, string>()); }
public static List <ZipCode> getZipByCompanyList(String companyId, String divisionId, 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, companyId + divisionId + keyStroke, Logger.logLevel.Debug); List <ZipCode> lstZipCode = new List <ZipCode>(); SqlConnection conn = null; SqlDataReader reader = null; try { // create and open a connection object conn = ConnectionManager.getConnection(); conn.Open(); String query; if (companyId.Equals("null") && divisionId.Equals("null")) { query = "select distinct TOP 50 P.address5 FROM person P WHERE P.address5 LIKE '%" + keyStroke + "%'"; } else if (!companyId.Equals("null") && !divisionId.Equals("null")) { query = "select distinct TOP 50 P.address5 FROM person P, department D,rs_company Comp, rs_division Div"; query = query + " WHERE P.department = D.id AND Comp.companyId = @company AND Div.divisionId = @division AND D.user1 LIKE Comp.companyName AND D.user2 LIKE Div.divisionName AND P.address5 LIKE '%" + keyStroke + "%'"; } else if (companyId.Equals("null")) { query = "select distinct TOP 50 P.address5 FROM person P, department D,rs_division Div"; query = query + " WHERE P.department = D.id AND Div.divisionId = @division AND D.user2 LIKE Div.divisionName AND P.address5 LIKE '%" + keyStroke + "%'"; } else { query = "select distinct TOP 50 P.address5 FROM person P, department D,rs_company Comp"; query = query + " WHERE P.department = D.id AND Comp.companyId = @company AND D.user1 LIKE Comp.companyName AND P.address5 LIKE '%" + keyStroke + "%'"; } SqlCommand command = new SqlCommand(query, conn); command.Parameters.AddWithValue("@company", companyId.Trim()); command.Parameters.AddWithValue("@division", divisionId.Trim()); using (reader = command.ExecuteReader()) { while (reader.Read()) { String strZipCode = reader.GetSqlValue(0).ToString().Trim(); if (!(strZipCode.Equals("null") || strZipCode.Equals("NULL") || strZipCode.Equals("Null") || strZipCode.Equals(""))) { ZipCode zipObj = new ZipCode(); strZipCode = strZipCode.Substring(0, 5).Trim(); int n; bool isNumeric = int.TryParse(strZipCode, out n); if (isNumeric) { zipObj.value = strZipCode; zipObj.key = strZipCode; lstZipCode.Add(zipObj); } } } } } 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(); } } lstZipCode = lstZipCode.OrderBy(x => x.value).ToList(); Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Exit Point", Logger.logLevel.Debug); return(lstZipCode); }
public static List <ZipCode> getAllZipCodeList() { 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 <ZipCode> lstZipCode = new List <ZipCode>(); SqlConnection conn = null; SqlDataReader reader = null; try { // create and open a connection object conn = ConnectionManager.getConnection(); conn.Open(); String query = "select distinct TOP 50 address5 from person"; SqlCommand command = new SqlCommand(query, conn); using (reader = command.ExecuteReader()) { while (reader.Read()) { String strZipCode = reader.GetSqlValue(0).ToString().Trim(); if (!(strZipCode.Equals("null") || strZipCode.Equals("NULL") || strZipCode.Equals("Null") || strZipCode.Equals(""))) { ZipCode zipObj = new ZipCode(); strZipCode = strZipCode.Substring(0, 5).Trim(); int n; bool isNumeric = int.TryParse(strZipCode, out n); if (isNumeric) { zipObj.value = strZipCode; zipObj.key = strZipCode; lstZipCode.Add(zipObj); } } } } } 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(); } } lstZipCode = lstZipCode.OrderBy(x => x.value).ToList(); Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Exit Point", Logger.logLevel.Debug); return(lstZipCode); }
public static List<ZipCode> getAllZipCodeList() { 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<ZipCode> lstZipCode = new List<ZipCode>(); SqlConnection conn = null; SqlDataReader reader = null; try { // create and open a connection object conn = ConnectionManager.getConnection(); conn.Open(); String query = "select distinct TOP 50 address5 from person"; SqlCommand command = new SqlCommand(query, conn); using (reader = command.ExecuteReader()) { while (reader.Read()) { String strZipCode = reader.GetSqlValue(0).ToString().Trim(); if (!(strZipCode.Equals("null") || strZipCode.Equals("NULL") || strZipCode.Equals("Null") || strZipCode.Equals(""))) { ZipCode zipObj = new ZipCode(); strZipCode = strZipCode.Substring(0, 5).Trim(); int n; bool isNumeric = int.TryParse(strZipCode, out n); if (isNumeric) { zipObj.value = strZipCode; zipObj.key = strZipCode; lstZipCode.Add(zipObj); } } } } } 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(); } } lstZipCode = lstZipCode.OrderBy(x => x.value).ToList(); Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Exit Point", Logger.logLevel.Debug); return lstZipCode; }
public static List<ZipCode> getZipByCompanyList(String companyId, String divisionId, 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, companyId + divisionId + keyStroke, Logger.logLevel.Debug); List<ZipCode> lstZipCode = new List<ZipCode>(); SqlConnection conn = null; SqlDataReader reader = null; try { // create and open a connection object conn = ConnectionManager.getConnection(); conn.Open(); String query; if (companyId.Equals("null") && divisionId.Equals("null")) { query = "select distinct TOP 50 P.address5 FROM person P WHERE P.address5 LIKE '%" + keyStroke + "%'"; } else if (!companyId.Equals("null") && !divisionId.Equals("null")) { query = "select distinct TOP 50 P.address5 FROM person P, department D,rs_company Comp, rs_division Div"; query = query + " WHERE P.department = D.id AND Comp.companyId = @company AND Div.divisionId = @division AND D.user1 LIKE Comp.companyName AND D.user2 LIKE Div.divisionName AND P.address5 LIKE '%" + keyStroke + "%'"; } else if (companyId.Equals("null")) { query = "select distinct TOP 50 P.address5 FROM person P, department D,rs_division Div"; query = query + " WHERE P.department = D.id AND Div.divisionId = @division AND D.user2 LIKE Div.divisionName AND P.address5 LIKE '%" + keyStroke + "%'"; } else { query = "select distinct TOP 50 P.address5 FROM person P, department D,rs_company Comp"; query = query + " WHERE P.department = D.id AND Comp.companyId = @company AND D.user1 LIKE Comp.companyName AND P.address5 LIKE '%" + keyStroke + "%'"; } SqlCommand command = new SqlCommand(query, conn); command.Parameters.AddWithValue("@company", companyId.Trim()); command.Parameters.AddWithValue("@division", divisionId.Trim()); using (reader = command.ExecuteReader()) { while (reader.Read()) { String strZipCode = reader.GetSqlValue(0).ToString().Trim(); if (!(strZipCode.Equals("null") || strZipCode.Equals("NULL") || strZipCode.Equals("Null") || strZipCode.Equals(""))) { ZipCode zipObj = new ZipCode(); strZipCode = strZipCode.Substring(0, 5).Trim(); int n; bool isNumeric = int.TryParse(strZipCode, out n); if (isNumeric) { zipObj.value = strZipCode; zipObj.key = strZipCode; lstZipCode.Add(zipObj); } } } } } 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(); } } lstZipCode = lstZipCode.OrderBy(x => x.value).ToList(); Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Exit Point", Logger.logLevel.Debug); return lstZipCode; }