/// <summary>
/// Gets Survey from database by Survey ID
/// </summary>
/// <param name="surveyID">Survey ID that tells which Survey to get</param>
/// <returns></returns>
public static Survey GetSurvey(int surveyID)
{
if (!isConnected)
{
Init();
}
SQLiteCommand sqliteCommand = dbConnection.CreateCommand();
sqliteCommand.CommandText = "SELECT * FROM " + SURVEYS_TABLE + " WHERE id=:id";
sqliteCommand.Parameters.AddWithValue(":id", surveyID.ToString());
SQLiteDataReader sqliteSurvey = sqliteCommand.ExecuteReader();
Survey survey = new Survey();
if (!sqliteSurvey.HasRows)
{
return survey;
}
survey.Id = Convert.ToInt32(sqliteSurvey["id"]);
survey.Name = sqliteSurvey["name"].ToString();
survey.TestMode = Convert.ToBoolean(sqliteSurvey["testmode"]);
survey.MinScore = Convert.ToDouble(sqliteSurvey["minscore"]);
survey.Polls = new List<Poll>();
sqliteSurvey.Close();
sqliteCommand.CommandText = "SELECT p.* FROM " + SURVEY_POLLS_TABLE + " pxp LEFT JOIN " + POLLS_TABLE + " p ON (pxp.poll_id=p.id) WHERE pxp.survey_id=:id";
SQLiteDataReader sqlPolls = sqliteCommand.ExecuteReader();
SQLiteCommand sqliteCommand2 = dbConnection.CreateCommand();
sqliteCommand2.CommandText = "SELECT c.* FROM " + POLL_CHOICES_TABLE + " pxc LEFT JOIN " + CHOICES_TABLE + " c ON (pxc.choice_id=c.id) WHERE pxc.poll_id=:id";
sqliteCommand2.Parameters.Add(new SQLiteParameter(":id"));
while (sqlPolls.Read())
{
Poll newPoll = new Poll(sqlPolls["name"].ToString());
newPoll.Id = Convert.ToInt32(sqlPolls["id"]);
newPoll.Description = sqlPolls["description"].ToString();
newPoll.CorrectChoiceID = Convert.ToInt32(sqlPolls["correctchoice"]);
newPoll.CustomChoiceEnabled = Convert.ToBoolean(sqlPolls["customenabled"]);
sqliteCommand2.Parameters[":id"].Value = newPoll.Id;
SQLiteDataReader sqlChoices = sqliteCommand2.ExecuteReader();
while (sqlChoices.Read())
{
Choice newChoice = new Choice(sqlChoices["name"].ToString());
newChoice.Id = Convert.ToInt32(sqlChoices["id"]);
newPoll.Choices.Add(newChoice);
}
sqlChoices.Close();
survey.Polls.Add(newPoll);
}
sqlPolls.Close();
return survey;
}
/// <summary>
/// Creates new Poll in database
/// </summary>
/// <param name="poll">object of Poll that is to be created in database</param>
/// <returns>id of created item</returns>
public static int CreatePoll(Poll poll)
{
SQLiteCommand sqliteCommand = dbConnection.CreateCommand();
sqliteCommand.Parameters.Add(new SQLiteParameter(":name"));
foreach (Choice curChoice in poll.Choices)
{
int oldId = curChoice.Id;
sqliteCommand.Parameters[":name"].Value = curChoice.choice;
sqliteCommand.CommandText = "INSERT INTO " + CHOICES_TABLE + "(name) VALUES(:name)";
sqliteCommand.ExecuteNonQuery();
sqliteCommand.CommandText = "SELECT last_insert_rowid()";
curChoice.Id = Convert.ToInt32(sqliteCommand.ExecuteScalar());
}
sqliteCommand.Parameters.Clear();
sqliteCommand.Parameters.AddWithValue(":name", poll.Name);
sqliteCommand.Parameters.AddWithValue(":description", poll.Description);
sqliteCommand.Parameters.AddWithValue(":correctchoice", -1);
sqliteCommand.Parameters.AddWithValue(":customenabled", false);
sqliteCommand.CommandText = "INSERT INTO " + POLLS_TABLE + "(name, description, correctchoice, customenabled) VALUES(:name, :description, :correctchoice, :customenabled)";
sqliteCommand.ExecuteNonQuery();
sqliteCommand.CommandText = "SELECT last_insert_rowid()";
poll.Id = Convert.ToInt32(sqliteCommand.ExecuteScalar());
sqliteCommand.Parameters.Clear();
sqliteCommand.CommandText = "INSERT INTO " + POLL_CHOICES_TABLE + "(poll_id, choice_id) VALUES(:poll_id, :choice_id)";
sqliteCommand.Parameters.AddWithValue(":poll_id", poll.Id);
sqliteCommand.Parameters.Add(new SQLiteParameter(":choice_id"));
foreach (Choice curChoice in poll.Choices)
{
sqliteCommand.Parameters[":choice_id"].Value = curChoice.Id;
sqliteCommand.ExecuteNonQuery();
}
return Convert.ToInt32(poll.Id);
}
/// <summary>
/// Gets Poll from database by poll ID
/// </summary>
/// <param name="surveyID">Poll ID that tells which poll to get</param>
/// <returns></returns>
public static Poll GetPoll(int pollID)
{
if (!isConnected)
{
Init();
}
SQLiteCommand sqlitePollCommand = dbConnection.CreateCommand();
sqlitePollCommand.Parameters.AddWithValue(":id", pollID.ToString());
sqlitePollCommand.CommandText = "SELECT * FROM " + POLLS_TABLE + " WHERE id=:id";
SQLiteDataReader sqlitePoll = sqlitePollCommand.ExecuteReader();
Poll poll = new Poll();
if (!sqlitePoll.HasRows)
{
return poll;
}
poll.Id = pollID;
poll.Description = sqlitePoll["description"].ToString();
SQLiteCommand sqliteChoicesCommand = dbConnection.CreateCommand();
sqliteChoicesCommand.CommandText = "SELECT c.* FROM " + POLL_CHOICES_TABLE + " pxc LEFT JOIN " + CHOICES_TABLE + " c ON (pxc.choice_id=c.id) WHERE pxc.poll_id=:id";
sqliteChoicesCommand.Parameters.AddWithValue(":id", poll.Id);
SQLiteDataReader sqliteChoices = sqliteChoicesCommand.ExecuteReader();
while (sqliteChoices.Read())
{
Choice newChoice = new Choice(sqliteChoices["name"].ToString());
newChoice.Id = Convert.ToInt32(sqliteChoices["id"]);
poll.Choices.Add(newChoice);
}
sqliteChoices.Close();
return poll;
}
public static void ParseXml(string xmlData)
{
if (xmlData[0] == '!')
{
Console.WriteLine(xmlData);
Console.WriteLine("Press any key to continue...");
Console.ReadKey(true);
Environment.Exit(-1);
}
//---------------Init---------------
List<Poll> pollDoc = new List<Poll>();
XmlDocument xmlDoc = new XmlDocument();
try
{
xmlDoc.LoadXml(xmlData);
}
catch(Exception)
{
Console.WriteLine("Corrupt xml data sent by server!");
Console.ReadKey(true);
Environment.Exit(-1);
}
System.Globalization.CultureInfo cultureInfo = (System.Globalization.CultureInfo)System.Globalization.CultureInfo.CurrentCulture.Clone();
cultureInfo.NumberFormat.NumberDecimalSeparator = ".";
XmlNodeList xmlPollSessionList = xmlDoc.GetElementsByTagName(POLL_SESSION_ELEMENT);
// Get pollSessions list
foreach (XmlNode xmlPollSession in xmlPollSessionList)
{
pollSession.id = Convert.ToInt32(xmlPollSession.Attributes["id"].Value);
pollSession.name = xmlPollSession.Attributes["name"].Value;
pollSession.testMode = Convert.ToBoolean(xmlPollSession.Attributes["testMode"].Value);
pollSession.minScore = Convert.ToDouble(xmlPollSession.Attributes["minScore"].Value, cultureInfo);
// Get polls list
foreach (XmlNode xmlPoll in xmlPollSession.ChildNodes)
{
Poll curPoll = new Poll();
XmlAttributeCollection xmlAttr = xmlPoll.Attributes;
// Get current poll id
curPoll.id = Convert.ToInt32(xmlAttr["id"].Value);
// Get current poll name
curPoll.name = xmlAttr["name"].Value;
// Get current poll customChoiceEnabled option
if (xmlAttr["customChoiceEnabled"] != null)
curPoll.customChoice = Convert.ToBoolean(xmlAttr["customChoiceEnabled"].Value);
// Get correct choice in current Poll
curPoll.correctChoiceId = Convert.ToInt32(xmlAttr["correctChoice"].Value);
// Get choices list
foreach( XmlNode node in xmlPoll.ChildNodes )
{
if (node.Name == "choices")
{
// Get list of choices of current Poll
foreach (XmlNode xmlChoice in node.ChildNodes)
{
Choice curChoice = new Choice();
XmlAttributeCollection xmlAttrChoice = xmlChoice.Attributes;
// Get current choice id
curChoice.id = Convert.ToInt32(xmlAttrChoice["id"].Value);
// Get current choice name
curChoice.choice = xmlAttrChoice["name"].Value;
// Save current poll in current choice for future convenience
curChoice.parent = curPoll;
curPoll.choices.Add(curChoice);
}
}
else if (node.Name == "description")
{
// Get current Poll description
curPoll.description = node.InnerText;
}
}
pollSession.polls.Add(curPoll);
}
}
Console.WriteLine("XML parsed");
}