/// <summary> /// Updates an 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.DateOfBirth); updateCmd.Parameters.AddWithValue("@program", s.ProgramOfChoice); updateCmd.Parameters.AddWithValue("@sid", s.StudentId); try { con.Open(); int rowsAffected = updateCmd.ExecuteNonQuery(); } finally { con.Dispose(); } }
/// <summary> /// Deletes an existing student /// </summary> /// <param name="id"></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; //return (rowsAffected == 1) ? true : false; return(rowsAffected == 1); } 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 USED 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.DateOfBirth); addCmd.Parameters.AddWithValue("@program", s.ProgramOfChoice); try { con.Open(); 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(); selCmd.Connection = con; selCmd.CommandText = query; //Open connection to DB con.Open(); //Execute query SqlDataReader rdr = selCmd.ExecuteReader(); //Do something with results List <Student> stuList = new List <Student>(); while (rdr.Read()) { Student tempStu = new Student(); tempStu.StudentId = Convert.ToInt32(rdr["SID"]); tempStu.FirstName = Convert.ToString(rdr["FirstName"]); tempStu.LastName = Convert.ToString(rdr["LastName"]); tempStu.DateOfBirth = Convert.ToDateTime(rdr["DOB"]); tempStu.ProgramOfChoice = Convert.ToString(rdr["Program"]); stuList.Add(tempStu); } //Close connection con.Close(); return(stuList); }