public List <PersonProductItem> GetAd(int Id)
{
using (SqlConnection connection = new SqlConnection(@"Data Source=localhost\sqlexpress;Initial Catalog=Craigslist;Integrated Security=True"))
using (SqlCommand command = connection.CreateCommand())
{
List <PersonProductItem> Ad = new List <PersonProductItem>();
command.CommandText = @"select p.*, i.*
FROM PersonProduct p
JOIN Item i
ON p.Id = i.PersonProductId
WHERE i.personproductId = @Id";
command.Parameters.AddWithValue("@Id", Id);
connection.Open();
SqlDataReader Reader = command.ExecuteReader();
while (Reader.Read())
{
PersonProductItem P = new PersonProductItem();
P.PersonProductId = (int)Reader["id"];
P.FirstName = (string)Reader["FirstName"];
P.LastName = (string)Reader["LastName"];
P.PhoneNumber = (string)Reader["PhoneNumber"];
P.Title = (string)Reader["Title"];
P.Description = (string)Reader["Description"];
P.FileName = (string)Reader["FileName"];
P.ItemId = (int)Reader["id"];
Ad.Add(P);
}
return(Ad);
}
}
public IEnumerable <PersonProductItem> GetAllAds()
{
using (SqlConnection connection = new SqlConnection(@"Data Source=localhost\sqlexpress;Initial Catalog=Craigslist;Integrated Security=True"))
using (SqlCommand command = connection.CreateCommand())
{
List <PersonProductItem> Ad = new List <PersonProductItem>();
command.CommandText = @"select p.*, i.*
FROM PersonProduct p
JOIN Item i
ON p.Id = i.PersonProductId
WHERE i.id IN
(SELECT MIN(id) FROM item GROUP BY personproductid)";
connection.Open();
SqlDataReader Reader = command.ExecuteReader();
while (Reader.Read())
{
PersonProductItem P = new PersonProductItem();
P.PersonProductId = (int)Reader["id"];
P.FirstName = (string)Reader["FirstName"];
P.LastName = (string)Reader["LastName"];
P.PhoneNumber = (string)Reader["PhoneNumber"];
P.Title = (string)Reader["Title"];
P.Description = (string)Reader["Description"];
P.FileName = (string)Reader["FileName"];
Ad.Add(P);
}
return(Ad);
}
}
