// Insert data into Database public bool Insert(AppointmentClass a) { // Create a default return type and set its value to false bool isSuccess = false; // Step 1: Connect database SqlConnection conn = new SqlConnection(myconnstr); try { // Step 2: Create a SQL Query to insert data string sql = "INSERT INTO Appointment (Patient_ID, Doctor_ID, CSR_ID, Service_ID, Date, Time) " + "VALUES (@PatientId, @DoctorId, @CsrId, @ServiceId, @Date, @Time)"; // Create SQL command using sql and conn SqlCommand cmd = new SqlCommand(sql, conn); // Create parameters to add data cmd.Parameters.AddWithValue("@PatientId", a.PatientId); cmd.Parameters.AddWithValue("@DoctorId", a.DoctorId); cmd.Parameters.AddWithValue("@CsrId", a.CsrId); cmd.Parameters.AddWithValue("@ServiceId", a.ServiceId); cmd.Parameters.AddWithValue("@Date", a.Date); cmd.Parameters.AddWithValue("@Time", a.Time); // Open connection here conn.Open(); int rows = cmd.ExecuteNonQuery(); // If the query runs successfully then the value of rows will be // greater than zero else its value will be 0 if (rows > 0) { isSuccess = true; } else { isSuccess = false; } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } return(isSuccess); }
// Method to update data in database from our application public bool Update(AppointmentClass a) { // Create a default return type and set its default value to false bool isSuccess = false; SqlConnection conn = new SqlConnection(myconnstr); try { // SQL to update data in our database string sql = "UPDATE Appointment SET Patient_ID=@PatientId, Doctor_ID=@DoctorId, CSR_ID=@CsrId, Service_ID=@ServiceId, Date=@Date, Time=@Time " + "WHERE Appointment_ID=@AppointmentId"; // Create SQL command SqlCommand cmd = new SqlCommand(sql, conn); // Create parameters to add value cmd.Parameters.AddWithValue("@PatientId", a.PatientId); cmd.Parameters.AddWithValue("@DoctorId", a.DoctorId); cmd.Parameters.AddWithValue("@CsrId", a.CsrId); cmd.Parameters.AddWithValue("@ServiceId", a.ServiceId); cmd.Parameters.AddWithValue("@Date", a.Date); cmd.Parameters.AddWithValue("@Time", a.Time); cmd.Parameters.AddWithValue("AppointmentId", a.AppointmentId); // Open database connection conn.Open(); int rows = cmd.ExecuteNonQuery(); // If the query runs successfully then the value of rows will be // greater than zero else its value will be 0 if (rows > 0) { isSuccess = true; } else { isSuccess = false; } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } return(isSuccess); }
// Method to delete data from database public bool Delete(AppointmentClass a) { // Create a default return type and set its default value to false bool isSuccess = false; // Create SQL connection SqlConnection conn = new SqlConnection(myconnstr); try { // SQL to delete data string sql = "DELETE FROM Appointment WHERE Appointment_ID=@AppointmentId"; // Create SQL command SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@AppointmentId", a.AppointmentId); // Open connection conn.Open(); int rows = cmd.ExecuteNonQuery(); // If the query runs successfully then the value of rows will be // greater than zero else its value will be 0 if (rows > 0) { isSuccess = true; } else { isSuccess = false; } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { // Close connection conn.Close(); } return(isSuccess); }