public void storeBallot(Ballot ballot)
{
connection.Open();
int modified = 0;
try
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO Ballot(ballotName, openDate, dueDate) OUTPUT INSERTED.ballotID VALUES(@ballotName, @openDate, @dueDate)";
command.Parameters.AddWithValue("@ballotName", ballot.Name);
command.Parameters.AddWithValue("@openDate", ballot.OpenDate);
command.Parameters.AddWithValue("@dueDate", ballot.DueDate);
modified = (int)command.ExecuteScalar();
}
}
catch
{
}
finally
{
connection.Close();
}
foreach (Question question in ballot.Questions)
{
question.BallotId = modified;
storeQuestion(question);
}
}
private void disableButton_Click(object sender, EventArgs e)
{
DateTime now = DateTime.UtcNow;
Ballot ballot = (Ballot)ballotListBox.SelectedItem;
this.database.changeBallotDueDate(ballot, now);
updateItems();
}
public ViewBallot(Ballot ballot, User user)
{
this.ballot = ballot;
this.user = user;
InitializeComponent();
database = DatabaseInterface.getInstance();
ballotNameLabel.Text = "Ballot: " + ballot.Name;
}
public List <Vote> getVotes(Ballot ballot)
{
connection.Open();
List <Vote> votes = new List <Vote>();
try
{
using (SqlCommand command = connection.CreateCommand())
{
string questionIDs = "";
List <int> questionIDList = new List <int>();
foreach (Question question in ballot.Questions)
{
questionIDList.Add(question.QuestionId);
}
if (questionIDList.Count() > 0)
{
questionIDs = String.Join(", ", questionIDList);
command.CommandText = "SELECT * FROM [Vote] WHERE [questionID] IN (" + questionIDs + ")";
command.Parameters.AddWithValue("@ballotID", ballot.BallotId);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int optionID = reader.GetInt32(0);
int questionID = reader.GetInt32(1);
string state = reader.GetString(2);
int age = reader.GetInt32(3);
string gender = reader.GetString(4);
string race = reader.GetString(5);
int voteID = reader.GetInt32(6);
Vote newVote = new Vote(voteID, optionID, questionID, state, age, gender, race);
votes.Add(newVote);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
return(votes);
}
public CreateBallot()
{
InitializeComponent();
ballot = new Ballot(); // Create a new ballot option to store the questions and options as they're created
database = DatabaseInterface.getInstance(); // Get the instance of the database interface
questionSource.DataSource = ballot.Questions; // Set the ballot's list of questions to the source for the question list box
questionsListBox.DisplayMember = "QuestionText"; // Have the question list box display the question's text instead of an object id
questionsListBox.DataSource = questionSource;
optionsListBox.DisplayMember = "OptionText"; // Have the option list box display the option's text instead of an object id
optionsListBox.DataSource = optionSource;
}
public List <Ballot> getActiveBallots(User user)
{
connection.Open();
List <Ballot> ballots = new List <Ballot>();
try
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM [Ballot] WHERE OpenDate < @now AND DueDate > @now AND BallotId NOT IN " +
"(SELECT BallotId FROM [User_Ballot] WHERE UserId = @userId);";
command.Parameters.AddWithValue("@now", DateTime.UtcNow);
command.Parameters.AddWithValue("@userId", user.UserID);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int ballotId = reader.GetInt32(0);
string ballotName = reader.GetString(1);
DateTime openDate = reader.GetDateTime(2);
DateTime dueDate = reader.GetDateTime(3);
Ballot ballot = new Ballot(ballotId, ballotName, openDate, dueDate);
ballots.Add(ballot);
}
}
}
}
catch
{
}
finally
{
connection.Close();
}
foreach (Ballot ballot in ballots)
{
ballot.Questions = getQuestions(ballot);
}
return(ballots);
}
public List <Ballot> getAllBallots()
{
connection.Open();
List <Ballot> ballots = new List <Ballot>();
try
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM [Ballot]";
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int ballotId = reader.GetInt32(0);
string ballotName = reader.GetString(1);
DateTime openDate = reader.GetDateTime(2);
DateTime dueDate = reader.GetDateTime(3);
Ballot ballot = new Ballot(ballotId, ballotName, openDate, dueDate);
ballots.Add(ballot);
}
}
}
}
catch
{
}
finally
{
connection.Close();
}
foreach (Ballot ballot in ballots)
{
ballot.Questions = getQuestions(ballot);
}
return(ballots);
}
public List <Question> getQuestions(Ballot ballot)
{
connection.Open();
List <Question> questions = new List <Question>();
try
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM [Question] WHERE [ballotID]=@ballotID";
command.Parameters.AddWithValue("@ballotID", ballot.BallotId);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int questionID = reader.GetInt32(0);
int ballotID = reader.GetInt32(1);
String question = reader.GetString(2);
Question newQuestion = new Question(questionID, ballotID, question);
questions.Add(newQuestion);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
foreach (Question question in questions)
{
question.Options = GetOptions(question);
}
return(questions);
}
public void changeBallotDueDate(Ballot ballot, DateTime date)
{
connection.Open();
try
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "UPDATE Ballot SET dueDate = @dueDate WHERE ballotID = @ballotID";
command.Parameters.AddWithValue("@ballotID", ballot.BallotId);
command.Parameters.AddWithValue("@dueDate", date);
command.ExecuteNonQuery();
}
}
catch
{
}
finally
{
connection.Close();
}
}
public void storeUserBallot(User user, Ballot ballot, DateTime timestamp)
{
connection.Open();
try
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO User_Ballot(userId, ballotId, timestamp) VALUES(@userId, @ballotId, @timestamp)";
command.Parameters.AddWithValue("@userId", user.UserID);
command.Parameters.AddWithValue("@ballotId", ballot.BallotId);
command.Parameters.AddWithValue("@timestamp", timestamp);
command.ExecuteNonQuery();
}
}
catch
{
}
finally
{
connection.Close();
}
}