public SimpleAd GetById(int id) { using (SqlConnection connection = new SqlConnection(_connectionString)) using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "SELECT a.*, i.FileName, i.Id as ImageId FROM Ads a " + "LEFT JOIN Images i " + "ON a.Id = i.AdId " + "WHERE a.Id = @id"; command.Parameters.AddWithValue("@id", id); connection.Open(); SqlDataReader reader = command.ExecuteReader(); SimpleAd ad = null; while (reader.Read()) { if (ad == null) { ad = GetAdFromReader(reader); } List <Image> images = ad.Images as List <Image>; Image image = GetImageFromReader(reader); if (image != null) { images.Add(image); } } return(ad); } }
public IEnumerable <SimpleAd> GetAds() { using (SqlConnection connection = new SqlConnection(_connectionString)) using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "SELECT a.*, i.FileName, i.Id as ImageId FROM Ads a " + "LEFT JOIN Images i " + "ON a.Id = i.AdId " + "ORDER BY a.Date DESC"; connection.Open(); Dictionary <int, SimpleAd> ads = new Dictionary <int, SimpleAd>(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { int adId = (int)reader["Id"]; if (!ads.ContainsKey(adId)) { SimpleAd ad = GetAdFromReader(reader); ads.Add(adId, ad); } SimpleAd simpleAd = ads[adId]; List <Image> images = simpleAd.Images as List <Image>; Image image = GetImageFromReader(reader); if (image != null) { images.Add(image); } } return(ads.Values); } }
public void AddSimpleAd(SimpleAd ad) { using (SqlConnection connection = new SqlConnection(_connectionString)) using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "INSERT INTO Ads (Title, Description, Name, PhoneNumber, Date) " + "VALUES (@title, @desc, @name, @phone, GETDATE()) SELECT @@Identity"; command.Parameters.AddWithValue("@title", ad.Title); command.Parameters.AddWithValue("@desc", ad.Description); object name = ad.Name; if (name == null) { name = DBNull.Value; } command.Parameters.AddWithValue("@name", name); command.Parameters.AddWithValue("@phone", ad.PhoneNumber); connection.Open(); int id = (int)(decimal)command.ExecuteScalar(); ad.Id = id; connection.Close(); if (ad.Images != null) { AddImages(ad.Images, id); } } }
private SimpleAd GetAdFromReader(SqlDataReader reader) { var ad = new SimpleAd { Name = reader.Get <string>("Name"), Description = reader.Get <string>("Description"), Date = reader.Get <DateTime>("DateCreated"), PhoneNumber = reader.Get <string>("PhoneNumber"), Id = reader.Get <int>("Id") }; return(ad); }
private SimpleAd GetAdFromReader(SqlDataReader reader) { SimpleAd ad = new SimpleAd { Name = reader.Get <string>("Name"), Description = reader.Get <string>("Description"), Date = reader.Get <DateTime>("Date"), PhoneNumber = reader.Get <string>("PhoneNumber"), Id = reader.Get <int>("Id"), Images = new List <Image>(), Title = reader.Get <string>("Title") }; return(ad); }
public void AddSimpleAd(SimpleAd ad) { using var connection = new SqlConnection(_connectionString); using var command = connection.CreateCommand(); command.CommandText = "INSERT INTO Ads (Description, Name, PhoneNumber, DateCreated) " + "VALUES (@desc, @name, @phone, GETDATE()) SELECT SCOPE_IDENTITY()"; command.Parameters.AddWithValue("@desc", ad.Description); object name = ad.Name; if (name == null) { name = DBNull.Value; } command.Parameters.AddWithValue("@name", name); command.Parameters.AddWithValue("@phone", ad.PhoneNumber); connection.Open(); ad.Id = (int)(decimal)command.ExecuteScalar(); }