public bool InsertAppointmentIntoDB(PatientAppointmentInsertGet app) { using (SqlConnection conn = new SqlConnection(Connection.ConnStr())) { conn.Open(); SqlTransaction myTrans = conn.BeginTransaction(); SqlDataAdapter adapter = new SqlDataAdapter(); //i Have to insert date into future_medicalCards adapter.InsertCommand = new SqlCommand("Use Hospitology;" + "INSERT INTO dbo.GP_Appointment_History(doctor_id,patient_id,appointment_date,[description],patient_pain)" + " VALUES(@idDoctor,@idPatient,@app_date,@pain,@descripton);" // "INSERT INTO dbo.Fututre_MedicalCards(doctor_id,patient_id,appointment_date)"+ //"VALUES(@idDoctor,@idPatient,@app_date);" , conn); adapter.InsertCommand.Transaction = myTrans; string idDoctor = app.IDDoctor; string idPatient = app.IDPatient; string appDate = app.appointment_date; string Description = app.description; string pain = app.patient_paint; adapter.InsertCommand.Parameters.AddWithValue("@idDoctor", idDoctor); adapter.InsertCommand.Parameters.AddWithValue("@idPatient", idPatient); adapter.InsertCommand.Parameters.AddWithValue("@app_date", appDate); adapter.InsertCommand.Parameters.AddWithValue("@pain", pain); adapter.InsertCommand.Parameters.AddWithValue("@descripton", Description); try { int affectedRows = adapter.InsertCommand.ExecuteNonQuery(); if (affectedRows == 0) { myTrans.Rollback("myTrans"); } else { myTrans.Commit(); } conn.Close(); } catch (Exception) { return(false); } return(true); } }
List <PatientAppointmentInsertGet> getPatientAppFutureData(PatientAppGetHistoryData data) { using (SqlConnection conn = new SqlConnection(Connection.ConnStr())) { //close it conn.Open(); SqlDataAdapter adapter = new SqlDataAdapter(); List <PatientAppointmentInsertGet> appList = new List <PatientAppointmentInsertGet>(); DateTime now = DateTime.Now; string nowString = now.ToString("yyyy-MM-dd"); string Q = "USE Hospitology;" + "SELECT d.last_name, h.patient_id,h.appointment_date,h.[description],h.[patient_pain] " + "FROM dbo.GP_Appointment_History h " + "FULL OUTER JOIN dbo.Doctors d ON d.id_doctor=h.doctor_id " + "WHERE patient_id=@PatientID and " + "(appointment_date >= @today)"; adapter.SelectCommand = new SqlCommand(Q, conn); adapter.SelectCommand.Parameters.AddWithValue("@today", nowString); adapter.SelectCommand.Parameters.AddWithValue("@PatientID", data.PatientID); DataTable tb = new DataTable(); adapter.Fill(tb); foreach (DataRow appItem in tb.Rows) { PatientAppointmentInsertGet app = new PatientAppointmentInsertGet(); app.IDDoctor = appItem["last_name"].ToString(); DateTime appTime = Convert.ToDateTime(appItem["appointment_date"]); app.appointment_date = appTime.ToString("dd-MM-yyyy HH:mm"); app.description = appItem["description"].ToString(); app.patient_paint = appItem["patient_pain"].ToString(); appList.Add(app); } return(appList); } }