public void AddAward(int indexOfUser, string award) { AwardsBdDAO awardsBd = new AwardsBdDAO(); List <int> awardsOfUser = new List <int>(); for (int i = 0; i < awardsBd.GetList().Count(); i++) { if (award.Contains(awardsBd.GetList().ToList()[i].Title)) { awardsOfUser.Add(awardsBd.GetList().ToList()[i].Id); } } connection.Open(); for (int i = 0; i < awardsOfUser.Count(); i++) { using (var command = new SqlCommand()) { command.CommandText = "AddRewardForUser"; command.CommandType = CommandType.StoredProcedure; command.Connection = connection; command.Parameters.AddWithValue("@idUser", indexOfUser); command.Parameters.AddWithValue("@idReward", awardsOfUser[i]); command.ExecuteNonQuery(); } } connection.Close(); }
public IEnumerable <Users> GetList() { List <int> valuesOfAwards = new List <int>(); AwardsBdDAO awards = new AwardsBdDAO(); 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()) { valuesOfAwards.Add(reader.GetInt32(0)); } reader.Close(); } connection.Close(); for (int j = 0; j < valuesOfAwards.Count(); j++) { for (int k = 0; k < awards.GetList().Count(); k++) { if (awards.GetList().ToList()[k].Id == valuesOfAwards[j]) { title = awards.GetList().ToList()[k].Title; } } users[i].Awards = users[i].Awards + "\n" + title; } valuesOfAwards.Clear(); } return(users); }