public static PGSqlAccessHelper GetInstance() { if (instance == null) { instance = new PGSqlAccessHelper(); } return(instance); }
// Return the count of the existing patients public int GetPatientCount() { NpgsqlConnection connection = null; int patCount = 0; try { PGSqlAccessHelper pgaccess = PGSqlAccessHelper.GetInstance(); if (!pgaccess.ConnectToDB(out connection)) { throw new Exception("Failed to connect to the PGSQL DB"); } Int64 counter = 0; // TODO: This is of no use now. Need to find out how to discard this string sqlCmd = String.Format(@"call sppatientcount('{0}')", counter); using (var cmd = new NpgsqlCommand(sqlCmd, connection)) { //cmd.Parameters.AddWithValue("counter", counter); using (var reader = cmd.ExecuteReader()) { reader.Read(); if (reader.HasRows == false) { return(0); // Error in reading the patient count } else { var value = reader.GetInt64(0); patCount = Int32.Parse(reader[0].ToString()); } } } return(patCount); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } finally { if ((connection != null) && (connection.State != ConnectionState.Open)) { connection.Close(); } } return(patCount); }
// TODO: Physician name not used and stored anywhere. Create a separate table for this public bool CreateNewPatient(PatientInfo patInfo, InsuranceInfo insuranceInfo, PhysicianDetails physDetails) { NpgsqlConnection connection = null; try { PGSqlAccessHelper pgaccess = PGSqlAccessHelper.GetInstance(); if (!pgaccess.ConnectToDB(out connection)) { throw new Exception("Failed to connect to the PGSQL DB"); } string sqlCmd = String.Format(@"CALL spcreatepatient('{0}', '{1}', {2}, '{3}', '{4}', '{5}', '{6}', '{7}')", patInfo.m_patientId, patInfo.m_patientName, patInfo.m_phoneNum, patInfo.m_emailId, patInfo.m_address, insuranceInfo.m_insuranceId, insuranceInfo.m_insurancePlan, physDetails.m_PhysicianId); using (var cmd = new NpgsqlCommand(sqlCmd, connection)) { cmd.ExecuteNonQuery(); } return(true); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } finally { if ((connection != null) && (connection.State != ConnectionState.Open)) { connection.Close(); } } return(false); }
// Delete all the created patients public bool DeleteAllPatients() { NpgsqlConnection connection = null; try { PGSqlAccessHelper pgaccess = PGSqlAccessHelper.GetInstance(); if (!pgaccess.ConnectToDB(out connection)) { throw new Exception("Failed to connect to the PGSQL DB"); } string sqlCmd = "call spdeleteallpatients()"; using (var cmd = new NpgsqlCommand(sqlCmd, connection)) { using (var reader = cmd.ExecuteReader()) { // Nothing to do here } } return(true); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } finally { if ((connection != null) && (connection.State != ConnectionState.Open)) { connection.Close(); } } return(false); }
// Fetch a specific patient's details // Using a direct SQL query here public bool GetPatient(Guid patientId, out PatientInfo patInfo, out InsuranceInfo insuranceInfo, out PhysicianDetails physDetails) { patInfo = null; insuranceInfo = null; physDetails = null; NpgsqlConnection connection = null; int patCount = 0; try { PGSqlAccessHelper pgaccess = PGSqlAccessHelper.GetInstance(); if (!pgaccess.ConnectToDB(out connection)) { throw new Exception("Failed to connect to the PGSQL DB"); } string sqlCmd = "SELECT \"Name\", public.\"PatientDetails\".\"PatientId\", \"PhoneNum\", \"EmailId\", \"Address\", " + "\"PhysicianId\", \"FirstAdmissionDate\", \"LatestVisitDate\", public.\"InsuranceInfo\".\"InsuranceId\", " + "\"InsurancePlan\" FROM public.\"PatientDetails\", public.\"PatientInfo\", public.\"InsuranceInfo\" where " + string.Format("\"PatientDetails\".\"PatientId\" = '{0}' and \"PatientInfo\".\"PatientID\" = '{0}' and\"InsuranceInfo\".\"PatientId\" = '{0}';", patientId); using (var cmd = new NpgsqlCommand(sqlCmd, connection)) { cmd.CommandType = CommandType.Text; using (var reader = cmd.ExecuteReader()) { reader.Read(); if (reader.HasRows == false) { return(false); // Matching patient not found } else { /* * SELECT "Name", public."PatientDetails"."PatientId", "PhoneNum", * "EmailId", "Address", "PhysicianId", "FirstAdmissionDate", * "LatestVisitDate", public."InsuranceInfo"."InsuranceId", "InsurancePlan" * FROM public."PatientDetails", public."PatientInfo", public."InsuranceInfo" * where "PatientDetails"."PatientId" = patientid * and * "PatientInfo"."PatientID" = patientid * and * "InsuranceInfo"."PatientId" = patientid; */ var name = reader[0]; patInfo = new PatientInfo(reader.GetString(0), reader.GetInt32(2), reader.GetString(3), reader.GetString(4), reader.GetGuid(8), reader.GetGuid(1)); insuranceInfo = new InsuranceInfo(patientId, reader.GetString(9)); physDetails = new PhysicianDetails("ABCD", "DEFH"); // TODO: Physician details are not stored. Need to add these } } } return(true); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } finally { if ((connection != null) && (connection.State != ConnectionState.Open)) { connection.Close(); } } return(false); }