public static List <PollOption> GetPolls(int userId)
{
List <PollOption> pollOptions = new List <PollOption>();
List <Option> options = new List <Option>();
using (MySqlConnection conn = new MySqlConnection(ConnectionString.Build()))
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = @"SELECT * FROM polls ";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
PollOption pollOption = new PollOption
{
Id = Convert.ToInt32(reader["poll_id"]),
Poll = new Poll()
{
Id = Convert.ToInt32(reader["poll_id"]),
Title = reader["title"].ToString(),
Description = reader["description"].ToString(),
Date = (DateTime)reader["date"]
},
Options = new List <Option>(),
Selected = new VoteCheck()
{
OptionId = 0
}
};
pollOptions.Add(pollOption);
}
}
}
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = @"SELECT poll_poll_id,poll_option_id,name,count FROM poll_poll_options
RIGHT JOIN poll_options
ON option_id = poll_option_id";
cmd.Parameters.AddWithValue("@user_id", userId);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int id = Convert.ToInt32(reader["poll_poll_id"]);
Option option = new Option
{
Id = Convert.ToInt32(reader["poll_option_id"]),
Name = reader["name"].ToString(),
Count = Convert.ToInt32(reader["count"])
};
var pollOption = pollOptions.Where(i => i.Id == id).SingleOrDefault();
pollOption.Options.Add(option);
}
}
}
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = @"SELECT poll_poll_id,poll_option_id,resident,name,count FROM poll_poll_options AS ppo
LEFT JOIN votes ON poll_option_id = option_id
INNER JOIN poll_options AS o
ON o.option_id = ppo.poll_option_id
WHERE resident = @user_id";
cmd.Parameters.AddWithValue("@user_id", userId);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int id = Convert.ToInt32(reader["poll_poll_id"]);
int userIdFromDB = Convert.ToInt32(reader["resident"]);
var pollOption = pollOptions.Where(i => i.Id == id).SingleOrDefault();
Vote vote = new Vote();
if (userId == userIdFromDB)
{
pollOption.Selected.Selected = true;
pollOption.Selected.OptionId = Convert.ToInt32(reader["poll_option_id"]);
}
}
}
}
}
return(pollOptions);
}
