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