/// <summary>
/// Добавление наград
/// </summary>
/// <param name="newR">Новая награда</param>
/// <returns></returns>
public bool AddReward(Reward newR)
{
using (var connection = new SqlConnection(ConnectionString()))
{
if (newR.Image == null)
{
var command = new SqlCommand(
"INSERT INTO dbo.Rewards (Title, Description) " +
"VALUES (@Title, @Description)", connection);
command.Parameters.AddWithValue("@Title", newR.Title);
command.Parameters.AddWithValue("@Description", newR.Description);
connection.Open();
return command.ExecuteNonQuery() == 1;
}
else
{
var command = new SqlCommand(
"INSERT INTO dbo.Rewards (Title, Description, Image) " +
"VALUES (@Title, @Description, @Image)", connection);
command.Parameters.AddWithValue("@Title", newR.Title);
command.Parameters.AddWithValue("@Description", newR.Description);
command.Parameters.AddWithValue("@Image", newR.Image);
connection.Open();
return command.ExecuteNonQuery() == 1;
}
}
}
/// <summary>
/// добавление наград
/// </summary>
/// <param name="newR">новая награда</param>
/// <returns></returns>
public bool AddReward(RewardDTO newR)
{
try
{
Reward RewardDAL = new Reward();
RewardDAL.Title = newR.Title;
RewardDAL.Description = newR.Description;
RewardDAL.Image = newR.Image;
if (Data.AddReward(RewardDAL) == true)
return true;
else throw new ValidationException("Ваш запрос не был обработан", "");
}
catch (ArgumentException ex)
{
throw new ValidationException(ex.Message, ex.ParamName);
}
}
/// <summary>
/// список юзеров и их наград
/// </summary>
/// <returns></returns>
public List<UserRewards> UserRewardsList()
{
var userRewards = new List<UserRewards>();
using (var connection = new SqlConnection(ConnectionString()))
{
var command = new SqlCommand(
"SELECT u.UserID, u.Name, u.Photo, r.RewardID, r.Title, r.Description, r.Image FROM Users AS u INNER JOIN" +
" UserRewards as ur ON u.UserID = ur.UserID LEFT " +
"JOIN Rewards as r ON r.RewardID = ur.RewardID" +
" Order BY ur.UserID", connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
UserRewards ur = new UserRewards();
User u = new User();
Reward r = new Reward();
u.UserID = Convert.ToInt32(reader.GetValue(0));
u.Name = Convert.ToString(reader.GetValue(1));
u.Photo = reader.GetValue(2) as byte[];
r.RewardID = Convert.ToInt32(reader.GetValue(3));
r.Title = Convert.ToString(reader.GetValue(4));
r.Description = Convert.ToString(reader.GetValue(5));
r.Image = reader.GetValue(6) as byte[];
ur.User = u;
ur.Reward = r;
userRewards.Add(ur);
}
return userRewards;
}
}
/// <summary>
/// Список наград
/// </summary>
/// <returns></returns>
public List<Reward> RewardsList()
{
var rewards = new List<Reward>();
using (var connection = new SqlConnection(ConnectionString()))
{
var command = new SqlCommand(
"SELECT RewardID, Title, Description, Image FROM dbo.Rewards", connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Reward reward = new Reward();
reward.RewardID = Convert.ToInt32(reader.GetValue(0));
reward.Title = Convert.ToString(reader.GetValue(1));
reward.Description = Convert.ToString(reader.GetValue(2));
reward.Image = reader.GetValue(3) as byte[];
rewards.Add(reward);
}
return rewards;
}
}
public bool EditReward(Reward R)
{
using (var connection = new SqlConnection(ConnectionString()))
{
var command = new SqlCommand(
"UPDATE dbo.Rewards SET Title = @Title, Description = @Description, " +
"Image = @Image WHERE RewardID = @RewardID", connection);
command.Parameters.AddWithValue("@RewardID", R.RewardID);
command.Parameters.AddWithValue("@Title", R.Title);
command.Parameters.AddWithValue("@Description", R.Description);
command.Parameters.AddWithValue("@Image", R.Image);
connection.Open();
return command.ExecuteNonQuery() == 1;
}
}
