// Insert data into Database public bool Insert(PrescriptionClass p) { // 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 Prescription (Patient_ID, Doctor_ID, Drug_ID, Date, Period, Dosage, Description) " + "VALUES (@PatientId, @DoctorId, @DrugId, @Date, @Period, @Dosage, @Description)"; // Create SQL command using sql and conn SqlCommand cmd = new SqlCommand(sql, conn); // Create parameters to add data cmd.Parameters.AddWithValue("@PatientId", p.PatientId); cmd.Parameters.AddWithValue("@DoctorId", p.DoctorId); cmd.Parameters.AddWithValue("@DrugId", p.DrugId); cmd.Parameters.AddWithValue("@Date", p.Date); cmd.Parameters.AddWithValue("@Period", p.Period); cmd.Parameters.AddWithValue("@Dosage", p.Dosage); cmd.Parameters.AddWithValue("@Description", p.Description); // 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(PrescriptionClass p) { // 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 Prescription SET Patient_ID=@PatientId, Doctor_ID=@DoctorId, Drug_ID=@DrugId, Date=@Date, Period=@Period, Dosage=@Dosage, Description=@Description " + "WHERE Prescription_ID=@PrescriptionId"; // Create SQL command SqlCommand cmd = new SqlCommand(sql, conn); // Create parameters to add value cmd.Parameters.AddWithValue("@PatientId", p.PatientId); cmd.Parameters.AddWithValue("@DoctorId", p.DoctorId); cmd.Parameters.AddWithValue("@DrugId", p.DrugId); cmd.Parameters.AddWithValue("@Date", p.Date); cmd.Parameters.AddWithValue("@Period", p.Period); cmd.Parameters.AddWithValue("@Dosage", p.Dosage); cmd.Parameters.AddWithValue("@Description", p.Description); cmd.Parameters.AddWithValue("PrescriptionId", p.PrescriptionId); // 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(PrescriptionClass p) { // 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 Prescription WHERE Prescription_ID=@PrescriptionId"; // Create SQL command SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@PrescriptionId", p.PrescriptionId); // 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); }