public List <Medicine> ShowMedicine() { List <Medicine> meds = new List <Medicine>(); var conn = HospitalConnection.CreateDbConnection(); string SQLcmd = "sp_AllMedicine"; try { using (conn) { conn.Open(); var cmd = (SqlCommand)HospitalConnection.CreateDbCommand(conn, SQLcmd, CommandType.StoredProcedure); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { string name = (string)reader["Name"]; string country = (string)reader["Country"]; DateTime expirationDate = (DateTime)reader["ExpirationDate"]; decimal price = (decimal)reader["Price"]; byte[] pic = (reader["Picture"] == System.DBNull.Value ? null : (byte[])reader["Picture"]); Medicine med = new Medicine(name, country, price, expirationDate); med.Picture = pic; meds.Add(med); } } } return(meds); } catch (Exception e) { MessageBox.Show(e.Message); } return(meds); }
public void DeleteMedicine(Medicine medicine) { var conn = HospitalConnection.CreateDbConnection(); string sSQL = "sp_DeleteMedicine"; try { using (conn) { conn.Open(); var cmd = (SqlCommand)HospitalConnection.CreateDbCommand(conn, sSQL, CommandType.StoredProcedure); cmd.Parameters.Add("@Name", SqlDbType.VarChar, 20).Value = medicine.Name; cmd.ExecuteNonQuery(); } } catch (Exception e) { MessageBox.Show(e.Message); } }
public List <Diagnosis> PatientDiagnosis(Patient patient) { List <Diagnosis> result = null; var conn = HospitalConnection.CreateDbConnection(); string sSQl2 = "dbo.GetDiagnoseMedicine"; string sSQL1 = "dbo.GetDiagnose"; Dictionary <Medicine, int> medList = new Dictionary <Medicine, int>(); DateTime diagnoseDate = new DateTime(1997, 5, 6); string disease = ""; int diagnoseID = -1; try { using (conn) { conn.Open(); var cmd2 = (SqlCommand)HospitalConnection.CreateDbCommand(conn, sSQL1, CommandType.StoredProcedure); cmd2.Parameters.Add("@patientID", SqlDbType.VarChar, 9).Value = patient.PassportID; using (var reader2 = (SqlDataReader)cmd2.ExecuteReader()) { if (reader2.HasRows) { while (reader2.Read()) { disease = reader2.GetString(reader2.GetOrdinal("Description")); diagnoseDate = reader2.GetDateTime(reader2.GetOrdinal("DateOfDiagnosis")); diagnoseID = reader2.GetInt32(reader2.GetOrdinal("DiagnosesId")); } } } var cmd = (SqlCommand)HospitalConnection.CreateDbCommand(conn, sSQl2, CommandType.StoredProcedure); cmd.Parameters.Add("@patientID", SqlDbType.VarChar, 9).Value = patient.PassportID; cmd.Parameters.Add("@diagnoseID", SqlDbType.Int).Value = diagnoseID; using (var reader = (SqlDataReader)cmd.ExecuteReader(CommandBehavior.CloseConnection)) { if (reader.HasRows) { bool hasMoreResults = true; while (hasMoreResults) { while (reader.Read()) { string nameMed = reader.GetString(reader.GetOrdinal("name")).ToString(); string countryMed = reader.GetString(reader.GetOrdinal("country")).ToString(); decimal priceMed = reader.GetDecimal(reader.GetOrdinal("price")); DateTime expiryDate = reader.GetDateTime(reader.GetOrdinal("expiryDate")); int count = reader.GetInt32(reader.GetOrdinal("count")); Medicine m = new Medicine(nameMed, countryMed, priceMed, expiryDate); medList.Add(m, count); } hasMoreResults = reader.NextResult(); } } } } result.Add(new Diagnosis(disease, diagnoseDate, medList)); return(result); } catch (Exception e) { MessageBox.Show(e.Message); } return(result); }
private void Initialization() { List <Diagnosis> history = new List <Diagnosis>(); var conn = HospitalConnection.CreateDbConnection(); Diagnosis diagnose = null; string SQLcmd0 = "dbo.ReadMyHistory"; try { using (conn) { conn.Open(); var cmd0 = (SqlCommand)HospitalConnection.CreateDbCommand(conn, SQLcmd0, CommandType.StoredProcedure); cmd0.Parameters.Add("@PassportID", SqlDbType.Char, 9).Value = PassportID; using (var reader0 = (SqlDataReader)cmd0.ExecuteReader()) { while (reader0.Read()) { string SQLcmd1 = "dbo.Drugs"; var cmd1 = (SqlCommand)HospitalConnection.CreateDbCommand(conn, SQLcmd1, CommandType.StoredProcedure); cmd1.Parameters.Add("@DiagnosisID", SqlDbType.Int).Value = reader0["DiagnosesID"]; using (var reader1 = (SqlDataReader)cmd1.ExecuteReader()) { if (reader1.HasRows) { bool hasMoreResults = true; Dictionary <Medicine, int> medicine = new Dictionary <Medicine, int>(); //while (hasMoreResults) { while (reader1.Read()) { string n = (string)reader1["Name"]; string c = (string)reader1["Country"]; decimal p = (decimal)reader1["Price"]; DateTime e = (DateTime)reader1["ExpirationDate"]; Medicine med = new Medicine(n, c, p, e); if (medicine.ContainsKey(med)) { medicine[med]++; } else { medicine[med] = 1; } } diagnose = new Diagnosis((string)reader0["Description"], (DateTime)reader0["DateOfDiagnosis"], medicine); history.Add(diagnose); hasMoreResults = reader1.NextResult(); } } } } } } } catch (Exception e) { MessageBox.Show(e.Message); } MyHistory = history; }