public IEnumerable <ListingShortItem> GetRecent() { List <ListingShortItem> listings = new List <ListingShortItem>(); using (var cn = new SqlConnection(Settings.GetConnectionString())) { SqlCommand cmd = new SqlCommand("ListingsSelectRecent", cn); cmd.CommandType = CommandType.StoredProcedure; cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { ListingShortItem row = new ListingShortItem(); row.ListingId = (int)dr["ListingId"]; row.UserId = dr["UserId"].ToString(); row.StateId = dr["StateId"].ToString(); row.City = dr["City"].ToString(); row.Rate = (decimal)dr["Rate"]; if (dr["ImageFileName"] != DBNull.Value) { row.ImageFileName = dr["ImageFileName"].ToString(); } listings.Add(row); } } } return(listings); }
private ListingShortItem PopulateListingShortItemFromDataReader(SqlDataReader dr) { var listingShortItem = new ListingShortItem(); listingShortItem.ListingId = (int)dr["ListingId"]; listingShortItem.UserId = dr["UserId"].ToString(); listingShortItem.StateId = dr["StateId"].ToString(); listingShortItem.City = dr["City"].ToString(); listingShortItem.Rate = (decimal)dr["Rate"]; if (dr["ImageFileName"] != DBNull.Value) { listingShortItem.ImageFileName = dr["ImageFileName"].ToString(); } return(listingShortItem); }
public IEnumerable <ListingShortItem> Search(ListingSearchParameters parameters) { List <ListingShortItem> listings = new List <ListingShortItem>(); using (var cn = new SqlConnection(Settings.GetConnectionString())) { string query = "SELECT TOP 12 ListingId, UserId, StateId, City, Rate, ImageFileName FROM Listings WHERE 1 = 1 "; SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; if (parameters.MinRate.HasValue) { query += "AND Rate >= @MinRate "; cmd.Parameters.AddWithValue("@MinRate", parameters.MinRate.Value); } if (parameters.MaxRate.HasValue) { query += "AND Rate <= @MaxRate "; cmd.Parameters.AddWithValue("@MaxRate", parameters.MaxRate.Value); } if (!string.IsNullOrEmpty(parameters.City)) { query += "AND City LIKE @City "; cmd.Parameters.AddWithValue("@City", parameters.City + '%'); } if (!string.IsNullOrEmpty(parameters.StateId)) { query += "AND StateId = @StateId "; cmd.Parameters.AddWithValue("@StateId", parameters.StateId); } query += "ORDER BY CreatedDate DESC"; cmd.CommandText = query; cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { ListingShortItem row = new ListingShortItem(); row.ListingId = (int)dr["ListingId"]; row.UserId = dr["UserId"].ToString(); row.StateId = dr["StateId"].ToString(); row.City = dr["City"].ToString(); row.Rate = (decimal)dr["Rate"]; if (dr["ImageFileName"] != DBNull.Value) { row.ImageFileName = dr["ImageFileName"].ToString(); } listings.Add(row); } } } return(listings); }