public static List<Category> getCategoriesByAreaList(String facilityId, String areaId, 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, facilityId + areaId + keyStroke, Logger.logLevel.Debug);
List<Category> lstCategory = new List<Category>();
SqlConnection conn = null;
SqlDataReader reader = null;
try
{
// create and open a connection object
conn = ConnectionManager.getConnection();
conn.Open();
String query = "";
if (facilityId.Equals("null") && areaId.Equals("null"))
{
query = "Select TOP 100 C.id,C.description from category C where description like '%" + keyStroke + "%'";
}
else if (!facilityId.Equals("null") && !areaId.Equals("null"))
{
query = "Select TOP 100 C.id,C.description,CA.area_id,CA.facility from category C ,area_category CA where C.id = CA.category_id AND CA.area_id = @area_id AND CA.facility = @facility_Id AND C.description like '%" + keyStroke + "%'";
}
else if (!facilityId.Equals("null"))
{
query = "Select TOP 100 C.id,C.description,CA.area_id,CA.facility from category C ,area_category CA where C.id = CA.category_id AND CA.facility = @facility_Id AND C.description like '%" + keyStroke + "%'";
}
else
{
query = "Select TOP 100 C.id,C.description,CA.area_id,CA.facility from category C ,area_category CA where C.id = CA.category_id AND CA.area_id = @area_id AND C.description like '%" + keyStroke + "%'";
}
SqlCommand command = new SqlCommand(query, conn);
command.Parameters.AddWithValue("@area_id", areaId.Trim());
command.Parameters.AddWithValue("@facility_Id", facilityId.Trim());
using (reader = command.ExecuteReader())
{
while (reader.Read())
{
String strCategory = reader.GetSqlValue(1).ToString().Trim();
if (!(strCategory.Equals("") || strCategory.Equals("null") || strCategory.Equals("NULL") || strCategory.Equals("Null")))
{
Category categoryObj = new Category();
categoryObj.CategoryId = reader.GetSqlValue(0).ToString().Trim();
categoryObj.CategoryDesc = strCategory;
lstCategory.Add(categoryObj);
}
}
}
}
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();
}
}
lstCategory = lstCategory.OrderBy(x => x.CategoryDesc).ToList();
Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Exit Point", Logger.logLevel.Debug);
return lstCategory;
}
public static List<Category> getAllCategoriesList()
{
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<Category> lstCategory = new List<Category>();
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 FROM category";
SqlCommand command = new SqlCommand(query, conn);
using (reader = command.ExecuteReader())
{
while (reader.Read())
{
String strCategory = reader.GetSqlValue(1).ToString().Trim();
if (!(strCategory.Equals("") || strCategory.Equals("null") || strCategory.Equals("NULL") || strCategory.Equals("Null")))
{
Category categoryObj = new Category();
categoryObj.CategoryId = reader.GetSqlValue(0).ToString().Trim();
categoryObj.CategoryDesc = strCategory;
lstCategory.Add(categoryObj);
}
}
}
}
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();
}
}
lstCategory = lstCategory.OrderBy(x => x.CategoryDesc).ToList();
Logger.LogDebug(MethodBase.GetCurrentMethod().DeclaringType.ToString(), MethodBase.GetCurrentMethod().Name, "Exit Point", Logger.logLevel.Debug);
return lstCategory;
}