public void AddReward(int idUser, string reward) { RewardsBdDAO rewardsBd = new RewardsBdDAO(); List <int> rewardsOfUser = new List <int>(); for (int i = 0; i < rewardsBd.GetList().Count(); i++) { if (reward.Contains(rewardsBd.GetList().ToList()[i].Title)) { rewardsOfUser.Add(rewardsBd.GetList().ToList()[i].ID); } } connection.Open(); for (int i = 0; i < rewardsOfUser.Count(); i++) { using (var command = new SqlCommand()) { command.CommandText = "AddRewardForUser"; command.CommandType = CommandType.StoredProcedure; command.Connection = connection; command.Parameters.AddWithValue("@idUser", idUser); command.Parameters.AddWithValue("@idReward", rewardsOfUser[i]); command.ExecuteNonQuery(); } } connection.Close(); }
public IEnumerable <Users> GetList() { List <int> valuesOfRewards = new List <int>(); RewardsBdDAO rewards = new RewardsBdDAO(); string title = string.Empty; connection.Open(); users.Clear(); using (SqlCommand command = new SqlCommand("SELECT * FROM Users", connection)) { SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Users user = new Users(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetDateTime(3)); users.Add(user); } reader.Close(); } connection.Close(); for (int i = 0; i < users.Count(); i++) { connection.Open(); using (SqlCommand command = new SqlCommand($"SELECT AwardId FROM UsersAndAwards WHERE UserId = {users[i].ID}", connection)) { SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { valuesOfRewards.Add(reader.GetInt32(0)); } reader.Close(); } connection.Close(); for (int j = 0; j < valuesOfRewards.Count(); j++) { for (int k = 0; k < rewards.GetList().Count(); k++) { if (rewards.GetList().ToList()[k].ID == valuesOfRewards[j]) { title = rewards.GetList().ToList()[k].Title; } } users[i].Rewards = users[i].Rewards + " " + title; } valuesOfRewards.Clear(); } return(users); }