Exemple #1
0
        /// <summary>
        /// This event help to manipulate whole form with the data.
        ///
        /// Every time the index of the combobox gets changed this event select data from database and put it in every fields.
        ///
        ///
        /// This method use for both SQL queries and Linq Queries
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>

        private void DoctorComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Only while sql radio button is checked
            if (SqlRadioButton.Checked)
            {
                Connection.Close();
                CompanionListBox.Items.Clear();
                Connection.Open();
                //query to select the data
                SqlCommand command = new SqlCommand("select * from DOCTOR WHERE DOCTORID= " + DoctorComboBox.Text + ";", Connection);

                SqlDataReader reader;

                reader = command.ExecuteReader();

                //reading the data and putting it in a appropriate textbox
                while (reader.Read())
                {
                    string name = (string)reader["ACTOR"];
                    ActorTextBox.Text = name;

                    int series = (int)reader["SERIES"];
                    SeriesTextBox.Text = series.ToString();

                    int age = (int)reader["AGE"];
                    AgeTextBox.Text = age.ToString();

                    //*************************************************Image ********************************/
                    byte[]       photo  = (byte[])reader["Picture"];
                    MemoryStream stream = new MemoryStream(photo);
                    Image        image  = Image.FromStream(stream);
                    DoctorPictureBox.Image = Image.FromStream(stream);
                }

                //***********Another query with joining tables to get the year field
                command = new SqlCommand("select min(SEASONYEAR) AS year from EPISODE Join COMPANION on COMPANION.STORYID = EPISODE.STORYID  WHERE DOCTORID= " + DoctorComboBox.Text + ";", Connection);
                reader  = command.ExecuteReader();

                while (reader.Read())
                {
                    int year = (int)reader["year"];
                    YearTextBox.Text = year.ToString();
                }

                //*********************************************************************
                //Select query to select title from database

                command = new SqlCommand("select TITLE from EPISODE JOIN COMPANION on COMPANION.STORYID = EPISODE.STORYID where EPISODE.SEASONYEAR=" + YearTextBox.Text + "and DOCTORID= " + DoctorComboBox.Text + ";", Connection);
                reader  = command.ExecuteReader();

                while (reader.Read())
                {
                    string title = reader[0].ToString();
                    EpisodeTextBox.Text = title;
                }

                //************************************************************************
                //Select query for selecting the name, actor, title and seasonyear for a doctor and putting it in a listbox to display it

                command = new SqlCommand("select TITLE, SEASONYEAR, COMPANION.NAME, COMPANION.ACTOR from EPISODE JOIN COMPANION on COMPANION.STORYID = EPISODE.STORYID where DOCTORID= " + DoctorComboBox.Text + ";", Connection);
                reader  = command.ExecuteReader();

                while (reader.Read())
                {
                    string name = (string)reader["NAME"];

                    string actor = (string)reader["ACTOR"];

                    string title = (string)reader["TITLE"];

                    int year = (int)reader["SEASONYEAR"];

                    //***************************************************************Formatting to display in a list****************************//
                    string listItem  = name + "(" + actor + ")";
                    string listItem2 = "'" + title + "'" + "(" + year + ")\n\n";

                    CompanionListBox.Items.Add(listItem);
                    CompanionListBox.Items.Add(listItem2);
                    CompanionListBox.Items.Add("  ");
                }


                //doctors.Add(new Doctor((int)reader["DOCTORID"], (string)reader["ACTOR"], (int)reader["SERIES"], (int)reader["AGE"], (Image)reader["Picture"], (string)reader["DEBUT"]));
                Connection.Close();//Connection Close
            }

            //if Linqradiobutton is checked
            if (LinqRadioButton.Checked)
            {
                Connection.Close();

                Connection.Open();

                //**************************************************************************************Selecting and putting data in a list for doctor
                SqlCommand command = new SqlCommand("SELECT * FROM DOCTOR", Connection);

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Doctor d = new Doctor();

                    d.Original = (int)reader["DOCTORID"];
                    d.Actor    = reader["ACTOR"].ToString();
                    d.Age      = (int)reader["AGE"];
                    d.Debut    = reader["DEBUT"].ToString();
                    d.Series   = (int)reader["SERIES"];
                    d.Picture  = (byte[])reader["PICTURE"];

                    doctors.Add(d);
                }



                //***************************************For EPISODE********************************************

                command = new SqlCommand("Select * from EPISODE", Connection);

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    //Episode ep = new Episode((int)reader["STORYID"], (int)reader["SEASON"], (int)reader["SEASONYEAR"], (string)reader["TITLE"]);
                    //episodes.Add(ep);

                    Episode ep = new Episode();

                    try
                    {
                        ep.Story  = (int)reader["STORYID"];
                        ep.Season = (int)reader["SEASON"];
                        ep.Year   = (int)reader["SEASONYEAR"];
                        ep.Title  = (string)reader["TITLE"];

                        episodes.Add(ep);
                    }catch (Exception ex)
                    {
                        label1.Text = ex.Message;
                    }
                }

                ////***********************************************For Campanions******************************************

                command = new SqlCommand("SELECT * FROM COMPANION", Connection);

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    //Campanion ca = new Campanion((string)reader["NAME"], (string)reader["ACTOR"], (int)reader["DOCTORID"], (string)reader["DEBUT"]);
                    //campanions.Add(ca);

                    Campanion c = new Campanion();

                    try
                    {
                        c.Name     = (string)reader["NAME"];
                        c.Actor    = (string)reader["ACTOR"];
                        c.DoctorID = (int)reader["DOCTORID"];
                        c.Debut    = (string)reader["DEBUT"];

                        campanions.Add(c);
                    }catch (Exception ex)
                    {
                        label1.Text = ex.Message;
                    }
                }

                //Linq Query

                var dp = from d in doctors
                         where d.Original == int.Parse(DoctorComboBox.Text)
                         select new { s = d.Series, p = d.Picture, dp = d.Debut };
            }
        }
Exemple #2
0
        /// <summary>
        /// if the selected index is chosen then display data.
        /// </summary>
        /// <param name="sender">doctor combo box</param>
        /// <param name="e">event of the index of combo box changes</param>
        private void doctorComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            //clear listbox from previous selected index.
            companionsListBox.Items.Clear();

            //check the selected value and show that doctor #'s info and companion.
            var selectedDoctor = from doc in doctorWhos
                                 where doc.ordinal == Convert.ToInt32(doctorComboBox.SelectedItem)
                                 select doc;
            //create a doctor object to hold the selected doctor.
            Doctor timeLord = null;

            //Set the selected Doctor to timeLord.
            foreach (var d in selectedDoctor)
            {
                timeLord = d;
            }
            //get selected episode that matches with selected doctor debut by episode story.
            var selectedAdventure = from ep in adventures
                                    where ep.story == timeLord.debut
                                    select ep;
            //create an episode to hold on to the doctor's episode
            Episode chapter = null;

            foreach (var a in selectedAdventure)
            {
                chapter = a;
            }

            Episode friendEpisode = null;

            //get selected companion that matches with selected doctor ordinal by companion doctor.
            var selectedCompanion = from compan in assistants
                                    where compan.doctor == timeLord.ordinal
                                    select compan;

            //go through each companion that the doctor has to match companion episode with episode story.
            foreach (var c in selectedCompanion)
            {
                //get selected companion's episode that matches with selected Episode Story by companion debut.
                var companionDebut = from companEp in adventures
                                     where companEp.story == c.debut
                                     select companEp;
                //go through selected companionDebut to assign friendEpisode an object of episode.
                foreach (var cd in companionDebut)
                {
                    friendEpisode = cd;
                }

                //Show Companion information from friendEpisode and selected companion.
                string friendInfo = c.name + " (" + c.actor + ")";
                companionsListBox.Items.Add(friendInfo);
                friendInfo = "\"" + friendEpisode.title + "\" (" + friendEpisode.year + ")";
                companionsListBox.Items.Add(friendInfo);
                companionsListBox.Items.Add(" ");
            }

            //Show Doctor information.
            actorTextBox.Text  = timeLord.actor;
            seriesTextBox.Text = timeLord.series.ToString();
            ageTextBox.Text    = timeLord.age.ToString();

            //Show Episode Infomation
            yearTextBox.Text         = chapter.year.ToString();
            firstEpisodeTextBox.Text = chapter.title.ToString();
        }