private string CompleteQueryString(String querySoFar, dto.SearchDTO dto, bool isFormatValid)
{
string clausePrefix = " WHERE ";
if (isFormatValid && dto.JobIdIsRequired)
{
querySoFar += clausePrefix + " j.job_id = @job_id ";
clausePrefix = " OR ";
}
if (dto.AddressIsRequired)
{
querySoFar += clausePrefix + " d.district_address LIKE @district_address OR j.job_address LIKE @district_address ";
clausePrefix = " OR ";
}
if (dto.ContractIsRequired)
{
querySoFar += clausePrefix + " c.contract_code LIKE @contract_code OR c.contract_desc LIKE @contract_code";
clausePrefix = " OR ";
}
if (dto.DistrictIsRequired)
{
querySoFar += clausePrefix + " d.district_code LIKE @district_code OR d.district_desc LIKE @district_code ";
}
return(querySoFar);
}
public IEnumerable <dto.ResultsDTO> List(dto.SearchDTO dto)
{
string query = SEARCH_QUERY;
string connectionString = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
List <dto.ResultsDTO> resultsCol = new List <dto.ResultsDTO>();
Guid validGuid;
bool isFormatValid = Guid.TryParse(dto.SearchValue, out validGuid);
if (dto.IsJobOnlySearch() && !isFormatValid)
{
return(resultsCol);
}
query = CompleteQueryString(query, dto, isFormatValid);
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(query, con);
cmd.CommandType = CommandType.Text;
LoadParameters(cmd, dto, isFormatValid);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Guid jobId = rdr.GetGuid(JOBID);
string applicationId = rdr.IsDBNull(APPLICATIONID) ? "" : rdr.GetString(APPLICATIONID);
int contractId = rdr.GetInt32(CONTRACTID);
string contractCode = rdr.IsDBNull(CONTRACTCODE) ? "" : rdr.GetString(CONTRACTCODE);
string contractDesc = rdr.IsDBNull(CONTRACTDESC) ? "" : rdr.GetString(CONTRACTDESC);
int districtId = rdr.GetInt32(DISTRICTID);
string districtCode = rdr.IsDBNull(DISTRICTCODE) ? "" : rdr.GetString(DISTRICTCODE);
string districtDesc = rdr.IsDBNull(DISTRICTDESC) ? "" : rdr.GetString(DISTRICTDESC);
int masterjobId = rdr.GetInt32(MASTERJOBID);
string jobAddress = rdr.IsDBNull(JOBADDRESS) ? "" : rdr.GetString(JOBADDRESS);
string districtAddress = rdr.IsDBNull(DISTRICTADDRESS) ? "" : rdr.GetString(DISTRICTADDRESS);
string districtPostCode = rdr.IsDBNull(DISTRICTPOSTCODE) ? "" : rdr.GetString(DISTRICTPOSTCODE);
resultsCol.Add(new dto.ResultsDTO(jobId,
applicationId,
contractId,
contractCode,
contractDesc,
districtId,
districtCode,
districtDesc,
masterjobId,
jobAddress,
districtAddress,
districtPostCode));
}
}
return(resultsCol);
}
private void LoadParameters(SqlCommand cmd, dto.SearchDTO dto, bool isFormatValid)
{
if (isFormatValid && dto.JobIdIsRequired)
{
cmd.Parameters.AddWithValue("@job_id", dto.SearchValue);
}
if (dto.AddressIsRequired)
{
cmd.Parameters.AddWithValue("@district_address", "%" + dto.SearchValue + "%");
}
if (dto.ContractIsRequired)
{
cmd.Parameters.AddWithValue("@contract_code", "%" + dto.SearchValue + "%");
}
if (dto.DistrictIsRequired)
{
cmd.Parameters.AddWithValue("@district_code", "%" + dto.SearchValue + "%");
}
}
