public Medicine Find(int key)
        {
            IDatabase db = new MSSqlDatabase();
            db.Connect();

            db.BeginTransaction();

            DbCommand command = db.CreateCommand(sqlSELECT);
            command.Parameters.Add(db.CreateParameter("@id", "int"));
            command.Parameters["@id"].Value = key;

            DbDataReader reader = db.Select(command);

            Dictionary<int, Medicine> medicines = this.Read(reader, db);

            reader.Close();

            db.EndTransaction();
            db.Close();

            if (medicines.Count == 0)
                return null;
            else
                return medicines.Values.ToList()[0];
        }
        public void LoadAndAddResults()
        {
            try
            {
                XmlDocument xml = new XmlDocument();
                xml.Load(Config.Settings["XML_request"]);
                IDatabase db = new MSSqlDatabase();
                db.Connect();

                db.BeginTransaction();

                XmlNodeList requests = xml.DocumentElement.SelectNodes("//samplerequest");

                foreach (XmlNode node in requests)
                {
                    int id;
                    if (!Int32.TryParse(node.ChildNodes[0].InnerText, out id))
                        throw new ApplicationException(ErrorMessages.Messages["REQ_S_xml_id"] + node.ChildNodes[0].InnerText);
                    string results = node.ChildNodes[4].InnerText;
                    DateTime processed;
                    if (!DateTime.TryParse(node.ChildNodes[5].InnerText, out processed))
                        throw new ApplicationException(ErrorMessages.Messages["REQ_S_xml_processed"] + node.ChildNodes[5].InnerText);
                    this.AddResults(id, results, processed, db);
                }
                db.EndTransaction();
                db.Close();
            }
            catch (Exception e)
            {
                throw new ApplicationException(ErrorMessages.Messages["REQ_S_xml"] + e.Message);
            }
        }
        public void DeletePatientRequests(string p_id, IDatabase db = null)
        {
            IDatabase pdb = db;
            if (db == null)
            {
                db = new MSSqlDatabase();
                db.Connect();

                db.BeginTransaction();
            }

            DbCommand command = db.CreateCommand(sqlDELETEPATIENT1);
            command.Parameters.Add(db.CreateParameter("@id", "char", p_id.Length));
            command.Parameters["@id"].Value = p_id;

            db.ExecuteNonQuery(command);

            command = db.CreateCommand(sqlDELETEPATIENT2);
            command.Parameters.Add(db.CreateParameter("@id", "char", p_id.Length));
            command.Parameters["@id"].Value = p_id;

            db.ExecuteNonQuery(command);

            if (pdb == null)
            {
                db.EndTransaction();

                db.Close();
            }
        }
        public Dictionary<int, Medicine> LoadAsDict()
        {
            IDatabase db = new MSSqlDatabase();
            db.Connect();

            db.BeginTransaction();

            DbCommand command = db.CreateCommand(sqlSELECTALL);

            DbDataReader reader = db.Select(command);

            Dictionary<int, Medicine> medicines = this.Read(reader, db);

            reader.Close();

            db.EndTransaction();
            db.Close();

            return medicines;
        }
        public void DeleteRequest(Request r)
        {
            Prescription er = (Prescription)r;
            IDatabase db = new MSSqlDatabase();
            db.Connect();

            db.BeginTransaction();

            this.DeleteMedicines(er);

            DbCommand command = db.CreateCommand(sqlDELETE);
            command.Parameters.Add(db.CreateParameter("@id", "int"));
            command.Parameters["@id"].Value = er.Id;

            db.ExecuteNonQuery(command);

            db.EndTransaction();

            db.Close();
        }
        public void InsertRequest(string p_id, Request r)
        {
            Prescription er = (Prescription)r;
            if (er.Medicines.Count == 0)
            {
                throw new ApplicationException(ErrorMessages.Messages["REQ_P_medicines"]);
            }

            IDatabase db = new MSSqlDatabase();
            db.Connect();

            db.BeginTransaction();

            DbCommand command = db.CreateCommand(sqlINSERT);
            PrepareCommand(db, command, er, p_id);
            er.Id = db.ExecuteScalar(command);

            int added = this.InsertMedicines(er, db);

            db.EndTransaction();

            if (added != er.Medicines.Count)
            {
                db.Rollback();
            }

            db.Close();
        }
        public void UpdateRequest(Request r)
        {
            Prescription er = (Prescription)r;
            IDatabase db = new MSSqlDatabase();
            db.Connect();

            db.BeginTransaction();

            DbCommand command = db.CreateCommand(sqlUPDATE);
            command.Parameters.Add(db.CreateParameter("@date", "datetime"));
            command.Parameters["@date"].Value = er.Created;
            command.Parameters.Add(db.CreateParameter("@id", "int"));
            command.Parameters["@id"].Value = er.Id;
            db.ExecuteNonQuery(command);

            this.DeleteMedicines(er);
            int added = this.InsertMedicines(er, db);

            db.EndTransaction();

            if (added != er.Medicines.Count)
            {
                db.Rollback();
            }

            db.Close();
        }