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