/// <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"); }