public static List <Medical> GetAllPrisonerMedicals(int id) { using (SqlConnection ServerConnection = new SqlConnection(ConnectionString)) { ServerConnection.Open(); List <Medical> MedicalsList = new List <Medical>(); try { SqlCommand cmd = new SqlCommand(); DataTable sqlPrisoners = new DataTable(); SqlDataAdapter Data = new SqlDataAdapter("Select HRID from [HealthRecord] where PID = " + id, ServerConnection); Data.Fill(sqlPrisoners); foreach (DataRow row in sqlPrisoners.Rows) { Medical tmedical = GetMedical((int)row["HRID"]); if (tmedical != null) { MedicalsList.Add(tmedical); } } } catch (SqlException ex) { Console.Write("SQL Server Error" + ex); } finally { ServerConnection.Close(); } return(MedicalsList); } }
//Others public static Medical GetMedical(int id) { using (SqlConnection Server = new SqlConnection(ConnectionString)) { Server.Open(); SqlCommand cmd = new SqlCommand(); try { cmd.CommandText = "GetHealthRecord"; cmd.Connection = Server; cmd.CommandType = System.Data.CommandType.StoredProcedure; //input para cmd.Parameters.Add(new SqlParameter("@hrid", id)); //output parameters cmd.Parameters.Add(new SqlParameter("@sym", SqlDbType.VarChar, 1000)); cmd.Parameters["@sym"].Direction = ParameterDirection.Output; cmd.Parameters.Add(new SqlParameter("@dig", SqlDbType.VarChar, 1000)); cmd.Parameters["@dig"].Direction = ParameterDirection.Output; cmd.Parameters.Add(new SqlParameter("@datee", SqlDbType.DateTime)); cmd.Parameters["@datee"].Direction = ParameterDirection.Output; cmd.Parameters.Add(new SqlParameter("@pd", SqlDbType.Int)); cmd.Parameters["@pd"].Direction = ParameterDirection.Output; cmd.Parameters.Add(new SqlParameter("@flag", SqlDbType.Int)); cmd.Parameters["@flag"].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); } catch (SqlException ex) { Console.Write("SQL Server Error" + ex); } int Flag = (int)cmd.Parameters["@flag"].Value; if (Flag == 1) { Medical m = new Medical(); m.MedicalID = id; m.Symptoms = (string)cmd.Parameters["@sym"].Value; m.Diagnosis = (string)cmd.Parameters["@dig"].Value; m.EntryDate = ((DateTime)cmd.Parameters["@datee"].Value).ToString("dd/MM/yyyy"); m.EntryTime = ((DateTime)cmd.Parameters["@datee"].Value).ToString("HH:mm:ss"); m.Prisoner = new Prisoner(); m.Prisoner.PrisonerID = Convert.ToInt32(cmd.Parameters["@pd"].Value); return(m); } else { return(null); } } }
public static bool UpdateMedical(Medical medical) { using (SqlConnection ServerConnection = new SqlConnection(ConnectionString)) { ServerConnection.Open(); //calling procedure from db SqlCommand cmd = new SqlCommand(); try { cmd.CommandText = "UpdateHealthRecord"; cmd.Connection = ServerConnection; cmd.CommandType = System.Data.CommandType.StoredProcedure; //passing parameters to procedure cmd.Parameters.Add(new SqlParameter("@hrid", medical.MedicalID)); cmd.Parameters.Add(new SqlParameter("@sym", medical.Symptoms)); cmd.Parameters.Add(new SqlParameter("@dig", medical.Diagnosis)); //passing output para cmd.Parameters.Add(new SqlParameter("@flag", SqlDbType.Int)); cmd.Parameters["@flag"].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); //run procedure } catch (SqlException ex) { Console.Write("SQL Server Error" + ex); } finally { ServerConnection.Close(); } int Flag = (int)cmd.Parameters["@flag"].Value; return(Flag == 1); } }