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