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);
}
