/// <summary> /// Deletes existing student /// </summary> /// <param name="id">ID of the existing student.</param> /// <returns></returns> public static bool Delete(int id) { SqlConnection con = SmsDB.GetConnection(); SqlCommand delCmd = new SqlCommand(); delCmd.Connection = con; delCmd.CommandText = "DELETE FROM Students WHERE SID = @id"; delCmd.Parameters.AddWithValue("@id", id); try { con.Open(); int rowsAffected = delCmd.ExecuteNonQuery(); //if (rowsAffected == 1) // return true; //else // return false; // Same as above with a conditional operator. return((rowsAffected == 1) ? true : false); } finally { con.Dispose(); } }
/// <summary> /// Adds a student to the database /// </summary> /// /// <param name="s">The student to be added</param> /// <exception cref="SqlException"></exception> public static void Add(Student s) { SqlConnection con = SmsDB.GetConnection(); SqlCommand addCmd = new SqlCommand(); addCmd.Connection = con; // ALWAYS USE PARAMETERIZED QUERIES!! addCmd.CommandText = "INSERT INTO Students(FirstName, LastName, DOB, Program)" + "VALUES (@fName, @lName, @dob, @program)"; //Add Values into parameters addCmd.Parameters.AddWithValue("@fName", s.FirstName); addCmd.Parameters.AddWithValue("@lName", s.LastName); addCmd.Parameters.AddWithValue("@dob", s.DOB); addCmd.Parameters.AddWithValue("@program", s.ProgramOfChoice); try { con.Open(); int rows = addCmd.ExecuteNonQuery(); // this will insert correctly OR throw a SQLException } finally { //con.Close(); con.Dispose(); // dispose calls close internally } }
public static List <Student> GetAllStudents() { //Get a connection to the DB SqlConnection con = SmsDB.GetConnection(); //Set up query string query = "SELECT SID, FirstName, LastName, DOB, Program " + "FROM Students "; SqlCommand selCmd = new SqlCommand { Connection = con, CommandText = query }; //Open a connection to the DB con.Open(); //Execute query SqlDataReader rdr = selCmd.ExecuteReader(); //returns a sql data reader object //Do something with results List <Student> stuList = new List <Student>(); while (rdr.Read()) //similar to hasNext() (goes through each row) { Student tempStu = new Student { StudentID = Convert.ToInt32(rdr["SID"]), FirstName = Convert.ToString(rdr["FirstName"]), LastName = Convert.ToString(rdr["LastName"]), DOB = Convert.ToDateTime(rdr["DOB"]), ProgramOfChoice = Convert.ToString(rdr["Program"]) }; stuList.Add(tempStu); } //Close connection con.Close(); return(stuList); }
/// <summary> /// Update existing student. /// </summary> /// <param name="s">New student data</param> /// <exception cref="SqlException"></exception> public static void Update(Student s) { SqlConnection con = SmsDB.GetConnection(); SqlCommand updateCmd = new SqlCommand(); updateCmd.Connection = con; updateCmd.CommandText = "UPDATE Students SET FirstName = @fName, LastName = @lName, DOB = @dob, Program = @program " + "WHERE SID = @sid"; updateCmd.Parameters.AddWithValue("@fName", s.FirstName); updateCmd.Parameters.AddWithValue("@lName", s.LastName); updateCmd.Parameters.AddWithValue("@dob", s.DOB); updateCmd.Parameters.AddWithValue("@program", s.ProgramOfChoice); updateCmd.Parameters.AddWithValue("@sid", s.StudentID); try { con.Open(); int rowsAffected = updateCmd.ExecuteNonQuery(); } finally { con.Dispose(); } }