public static List <Prescription> GetPrescriptions(string firstname, string lastname)
{
int pinfoid = 0;
List <Prescription> prescriptions = new List <Prescription>();
try
{
using (SqlConnection con = new SqlConnection(conString))
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
string query = "SELECT pinfoid from PatientInfo where firstname=@firstname and lastname = @lastname";
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.Add(new SqlParameter("@firstname", firstname));
cmd.Parameters.Add(new SqlParameter("@lastname", lastname));
SqlDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
if (reader.HasRows)
{
if (reader.Read())
{
{
pinfoid = int.Parse(reader["pinfoid"].ToString());
};
}
reader.Close();
}
}
query = "SELECT DoctorInfo.firstname,DoctorInfo.lastname,PrescriptionMaster.date,PrescriptionMaster.Details, PrescriptionMaster.timeslot FROM PrescriptionMaster INNER JOIN DoctorMaster ON PrescriptionMaster.dinfoid = DoctorMaster.dinfoid INNER JOIN DoctorInfo ON DoctorMaster.dinfoid = DoctorInfo.dinfoid WHERE(PrescriptionMaster.pinfoid = @pinfoid)";
cmd = new SqlCommand(query, con);
cmd.Parameters.Add(new SqlParameter("@pinfoid", pinfoid));
reader = cmd.ExecuteReader();
if (reader != null)
{
if (reader.HasRows)
{
while (reader.Read())
{
Prescription prescription = new Prescription()
{
Dfirstname = reader["firstname"].ToString(),
Dlastname = reader["lastname"].ToString(),
Details = reader["details"].ToString(),
Date = reader["date"].ToString(),
Timeslot = reader["timeslot"].ToString()
};
prescriptions.Add(prescription);
}
reader.Close();
}
}
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(prescriptions);
}
public static bool CreatePrescription(Prescription prescription)
{
bool status = false;
try
{
using (SqlConnection con = new SqlConnection(conString))
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
string query = "SELECT pinfoid from PatientInfo where firstname=@pfirstname";
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.Add(new SqlParameter("@pfirstname", prescription.Pfirstname));
SqlDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
if (reader.HasRows)
{
if (reader.Read())
{
{
prescription.Pinfoid = int.Parse(reader["pinfoid"].ToString());
};
}
reader.Close();
}
}
query = "SELECT dinfoid from DoctorInfo where username=@username";
cmd = new SqlCommand(query, con);
cmd.Parameters.Add(new SqlParameter("@username", prescription.Dusername));
reader = cmd.ExecuteReader();
if (reader != null)
{
if (reader.HasRows)
{
if (reader.Read())
{
{
prescription.Dinfoid = int.Parse(reader["dinfoid"].ToString());
};
}
reader.Close();
}
}
query = "INSERT INTO PrescriptionMaster (dinfoid, pinfoid, appointmentid, date, timeslot, details) " +
"VALUES (@dinfoid, @pinfoid, @appointmentid, @date, @timeslot, @details)";
cmd = new SqlCommand(query, con);
cmd.Parameters.Add(new SqlParameter("@dinfoid", prescription.Dinfoid));
cmd.Parameters.Add(new SqlParameter("@pinfoid", prescription.Pinfoid));
cmd.Parameters.Add(new SqlParameter("@appointmentid", prescription.Appointmentid));
cmd.Parameters.Add(new SqlParameter("@date", prescription.Date));
cmd.Parameters.Add(new SqlParameter("@timeslot", prescription.Timeslot));
cmd.Parameters.Add(new SqlParameter("@details", prescription.Details));
cmd.ExecuteNonQuery();
query = "UPDATE AppointmentMaster SET status = 1 where appointmentid = @appointmentid";
cmd = new SqlCommand(query, con);
cmd.Parameters.Add(new SqlParameter("@appointmentid", prescription.Appointmentid));
cmd.ExecuteNonQuery();
if (con.State == ConnectionState.Open)
{
con.Close();
}
status = true;
}
}
catch (Exception ex)
{
throw ex;
}
return(status);
}