Пример #1
0
        /// <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;
        }
Пример #2
0
        /// <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);
        }
Пример #3
0
        /// <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;
        }
Пример #4
0
        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");
        }