The Big Programming Thread - Page 214

using System;
using System.Collections.Generic;
using System.ComponentModel;   //              *****************************
using System.Data;             //              
using System.Drawing;          //                     
using System.Linq;             //              *****************************
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient; // We're going to be using sqlclient library

namespace WindowsFormsApplication1
{
    public partial class StudentPage : Form // Here's our StudentPage form
    {
        private string connectionString = "user id=TestUser;password=password;server=PERCY-PC\\SQLEXPRESS;Trusted_Connection=yes;database=Enrolment";
        // Here we give our database connection info, including path, title; in this case we've called the database "Enrolment"

        public StudentPage()
        {
            InitializeComponent(); 

            combospStudentID.Items.Clear(); // Make sure our combo box is clear
           
          SqlConnection dbconn = new SqlConnection(connectionString); // Create the database connection using our (connectionString)

            try 
            {
                dbconn.Open(); // Open the connection
            }
            catch // If the above fails, show this error message..
            {
                MessageBox.Show("Connection to database failed. Please check and try again.");
            }

            SqlDataAdapter sqlAdapt = new SqlDataAdapter("SELECT * FROM StudentInfo", dbconn);
            // Query the database. We are wanting to fill our combobox with StudentIDs, so we retrieve all from the database... 

            DataTable dt = new DataTable(); 
            sqlAdapt.Fill(dt); // ...and put this data into a temp datatable.

            foreach (DataRow dr in dt.Rows) // We can now fill our combobox with StudentIDs (do it for each row in the datatable until exhausted).
            {
                combospStudentID.Items.Add(dr["StudentID"];
            }

            dbconn.Close(); // Close the database connection now it is no longer needed.
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
            AddStudent ac = new AddStudent(); // This button closes the current page and takes us on to Add Student page.
            ac.ShowDialog();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            this.Close(); // This is our "back" button which will get us back to the Welcome page menu.
        }

        private void spcomboStudentID_SelectedIndexChanged(object sender, EventArgs e)
        // This is going to be what happens when we select an item in the StudentID combo that we filled earlier

        {
            SqlConnection dbconn = new SqlConnection(connectionString); // Get ready with a new connection
            try
            {
                dbconn.Open(); // Open the connection
            }
            catch
            {
                MessageBox.Show("Connection to database failed. Please check and try again."); // Just incase!
            }

            SqlDataAdapter sqlAdapt = new SqlDataAdapter("SELECT * FROM StudentInfo WHERE StudentID = '" + combospStudentID.Text + "'", dbconn);
            // Here we retrieve everything in a row from StudentInfo table where that row's StudentID is equal to the StudentID we
            // selected in our combo box!

            DataTable dt = new DataTable();

            sqlAdapt.Fill(dt); // Stick it in a datatable

            foreach (DataRow row in dt.Rows) // And now use it to fill our textboxes, outputting the relevant Firstname, Surname
            {
                txtspFirstName.Text = row["StudentFirstName"].ToString();
                txtspSurname.Text = row["StudentSurname"].ToString();
            }

            SqlDataAdapter sqlAdapt2 = new SqlDataAdapter("SELECT * FROM StudentInfo INNER JOIN CourseInfo ON StudentInfo.CourseID=CourseInfo.CourseID WHERE StudentID = '" + combospStudentID.Text + "'", dbconn);
            // We're not finished yet. We're outputting a Firstname & Surname alongside the associated StudentID..
            // But we also want to output a CourseName & CourseID that are associated with our selected student's CourseID. 
            // We join the StudentInfo table to the CourseInfo table, and the CourseIDs on these match up together, allowing us to retrieve..

            DataTable dt2 = new DataTable();
            sqlAdapt2.Fill(dt2); // ...fill a datatable...

            foreach (DataRow dr2 in dt2.Rows)
            {
                txtspCourse.Text = dr2["CourseName"].ToString(); // ...and output the relevant values to our GUI textboxes.
                txtCourseIDgo.Text = dr2["CourseID"].ToString();
            }

            dbconn.Close(); // Close the connection. Take a deep breath.
        
        }

        private void button4_Click(object sender, EventArgs e) // This is surely our delete button. 
        {
            SqlConnection dbconn = new SqlConnection(connectionString);
            try
            {
                dbconn.Open();
            }
            catch
            {
                MessageBox.Show("Connection to database failed. Please check and try again.");
            }

            SqlCommand sqlCom = new SqlCommand("DELETE FROM StudentInfo WHERE StudentID = " + combospStudentID.Text, dbconn);
            // Here we have connected to the database, and are now requesting a deletion from StudentInfo where StudentID is equal
            // to the Student ID we have selected in our combo box. 

            try
            {
                sqlCom.ExecuteNonQuery();
            }
            catch // Our code doesn't know what to delete if we don't have a Student ID selected in the combo box (ie if nothing is selected).. 
                  // ..so to avoid any problems we provide an informative error message if the ExecuteNonQuery() fails.
            {
                MessageBox.Show("Please select a Student ID to delete a student.");
            }

            dbconn.Close();  
            this.Close();  // Close the window after the deletion. Necessary to update the form's values, otherwise the changes won't 
                           // be apparent to the user. 
        } 

        private void btnspUpdate_Click(object sender, EventArgs e) // Our update button.
        {
            string takeStudentFirstName = txtspFirstName.Text; // We are going to take whatever values the user inputs into these 
            string takeStudentSurname = txtspSurname.Text;     // textboxes and then submit them for update!
            string takeStudentCourse = txtspCourse.Text;
            string takeCourseID = txtCourseIDgo.Text;

            SqlConnection dbconn = new SqlConnection(connectionString); // Prepare and open connection.

            try
            {
                dbconn.Open();
            }
            catch 
            {
                MessageBox.Show("Connection to database failed. Please check and try again.");
            }

            try
            {
                SqlCommand sqlCom = new SqlCommand("UPDATE StudentInfo SET StudentFirstName ='" + takeStudentFirstName + "', StudentSurname ='" + takeStudentSurname + "', CourseID ='" + takeCourseID + "'WHERE StudentID = " + combospStudentID.Text, dbconn);
                sqlCom.ExecuteNonQuery();
                // A simple update. Replaces the database values in a row relevant to our selected Student ID. Notice that we don't want 
                // the user editing/updating CourseName because they could write any old thing in there! CourseName is explicitly linked to
                // CourseID and we only want someone to change a Course Name when they go to the appropriate Course Details page. This prevents
                // the user from thinking that in order to change the course of a student he can just edit in any old thing on the Student Page
                // without adhearing to any formatting standards or realising that an associated Course ID is required for database function. 

            }
            catch
            {
                MessageBox.Show("Please check your input. Course ID must be a valid Course ID. Refer to Course Page for more information.");
            } // Damn right you will check your input.

            dbconn.Close();
        }
    }
}