public IList <Company> Select(CompanyCriteria criteria)
{
db.Connect();
DbCommand command = db.CreateCommand(GetSelectSql());
applyCriteria(criteria, command);
DbDataReader reader = db.Select(command);
IList <Company> companies = Read(reader);
reader.Close();
db.Close();
return(companies);
}
protected override void applyCriteria(CompanyCriteria criteria, DbCommand command)
{
SqlCommand sqlCommand = (SqlCommand)command;
if (criteria.City != null)
{
sqlCommand.CommandText += " WHERE LOWER(a.CITY)=LOWER(@c_city)";
sqlCommand.Parameters.Add("@c_city", SqlDbType.VarChar).Value = criteria.City;
}
if (criteria.Trademark != null)
{
sqlCommand.CommandText += " AND Lower(c.TRADEMARK) like '%' || LOWER(@c_trademark) || '%'";
sqlCommand.Parameters.Add("@c_trademark", SqlDbType.VarChar).Value = criteria.Trademark;
}
}
protected override void applyCriteria(CompanyCriteria criteria, DbCommand command)
{
OracleCommand oracleCommand = (OracleCommand)command;
oracleCommand.BindByName = true;
if (criteria.City != null)
{
oracleCommand.CommandText += " WHERE LOWER(a.CITY)=LOWER(:c_city)";
oracleCommand.Parameters.Add("c_city", OracleDbType.Varchar2).Value = criteria.City;
}
if (criteria.Trademark != null)
{
oracleCommand.CommandText += " AND Lower(c.TRADEMARK) like '%' || LOWER(:c_trademark) || '%'";
oracleCommand.Parameters.Add("c_trademark", OracleDbType.Varchar2).Value = criteria.Trademark;
}
}
protected abstract void applyCriteria(CompanyCriteria criteria, DbCommand command);
