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