public int RowCount(PropertyDescriptionCodeDTO propertyDescriptionCodeDTO)
{
int i = 0;
string sql = "Select count(ID) CT from [dbo].[r_Property_Desc_Code] Where 1=1 ";
// if property description code is not null then filter with it too.
if (!ValidationUtility.IsNull(propertyDescriptionCodeDTO.PropertyDescCodeDescription))
{
sql = String.Format(sql + " AND Property_Description_Code_Description = '{0}'", propertyDescriptionCodeDTO.PropertyDescCodeDescription);
}
if (!ValidationUtility.IsNull(propertyDescriptionCodeDTO.PropertyDescCode))
{
sql = String.Format(sql + " AND Property_Description_Code = '{0}'", propertyDescriptionCodeDTO.PropertyDescCode);
}
if (propertyDescriptionCodeDTO.LoanProgram > 0)
{
sql = String.Format(sql + " AND Loan_Program = '{0}'", propertyDescriptionCodeDTO.LoanProgram);
}
using (SqlConnection con = OpenConnection())
{
using (SqlDataReader dataReader = ExecuteQuery(sql, con))
{
while (dataReader.Read())
{
i = ValidationUtility.ToInteger(dataReader["CT"].ToString());
}
}
}
return(i);
}
public List <PropertyDescriptionCodeDTO> Search(int pageNo, PropertyDescriptionCodeDTO propertyDescriptionCodeDTO)
{
int pageSize = 10;
int rowStart = (pageNo * pageSize) + 1;
int rowEnd = rowStart + pageSize - 1;
List <PropertyDescriptionCodeDTO> list = new List <PropertyDescriptionCodeDTO>();
string sql = "SELECT * FROM " +
"(SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNum, * FROM [dbo].[r_Property_Desc_Code] where 1=1 {0}) AS RowConstrainedResult" +
" WHERE RowNum >= {1} AND RowNum< {2} ORDER BY RowNum";
string where = "";
// if property description code is not null then filter with it too.
if (!ValidationUtility.IsNull(propertyDescriptionCodeDTO.PropertyDescCodeDescription))
{
where = String.Format(where + " AND Property_Description_Code_Description = '{0}'", propertyDescriptionCodeDTO.PropertyDescCodeDescription);
}
if (!ValidationUtility.IsNull(propertyDescriptionCodeDTO.PropertyDescCode))
{
where = String.Format(where + " AND Property_Description_Code = '{0}'", propertyDescriptionCodeDTO.PropertyDescCode);
}
if (propertyDescriptionCodeDTO.LoanProgram > 0)
{
where = String.Format(where + " AND Loan_Program = '{0}'", propertyDescriptionCodeDTO.LoanProgram);
}
sql = String.Format(sql, where, rowStart, rowEnd);
using (SqlConnection con = OpenConnection())
{
using (SqlDataReader dataReader = ExecuteQuery(sql, con))
{
while (dataReader.Read())
{
list.Add(MakeDTO(null, dataReader));
}
}
}
return(list);
}
public List <PropertyDescriptionCodeDTO> Search(PropertyDescriptionCodeDTO propertyDescriptionCodeDTO)
{
List <PropertyDescriptionCodeDTO> list = new List <PropertyDescriptionCodeDTO>();
string sql = "Select * from [dbo].[r_Property_Desc_Code] Where 1=1 ";
sql = string.Format(sql, propertyDescriptionCodeDTO.PropertyDescCode);
// if property description code is not null then filter with it too.
if (!ValidationUtility.IsNull(propertyDescriptionCodeDTO.PropertyDescCodeDescription))
{
sql = String.Format(sql + " AND Property_Description_Code_Description = '{0}'", propertyDescriptionCodeDTO.PropertyDescCodeDescription);
}
if (!ValidationUtility.IsNull(propertyDescriptionCodeDTO.PropertyDescCode))
{
sql = String.Format(sql + " AND Property_Description_Code = '{0}'", propertyDescriptionCodeDTO.PropertyDescCode);
}
if (propertyDescriptionCodeDTO.LoanProgram > 0)
{
sql = String.Format(sql + " AND Loan_Program = '{0}'", propertyDescriptionCodeDTO.LoanProgram);
}
using (SqlConnection con = OpenConnection())
{
using (SqlDataReader dataReader = ExecuteQuery(sql, con))
{
while (dataReader.Read())
{
list.Add(MakeDTO(null, dataReader));
}
}
}
return(list);
}