SQL – запросы для создания базы данных
CREATE SCHEMA IF NOT EXISTS `Hospital` DEFAULT CHARACTER SET cp1251 ;
USE `Hospital` ;
-- -----------------------------------------------------
-- Table `Hospital`.`Departament`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Hospital`.`Departament` (
`id` INT NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR(50) NOT NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Hospital`.`Personnel`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Hospital`.`Personnel` (
`idPersonnel` INT NOT NULL ,
`Name` VARCHAR(50) NOT NULL ,
`Addres` VARCHAR(60) NOT NULL ,
`Birthday` DATE NOT NULL ,
`Phone` INT NOT NULL ,
`Post` VARCHAR(45) NOT NULL ,
`Salary` DECIMAL NULL ,
`Departament_id` INT NOT NULL ,
INDEX `fk_Personnel_Departament1` (`Departament_id` ASC) ,
PRIMARY KEY (`idPersonnel`) ,
CONSTRAINT `fk_Personnel_Departament1`
FOREIGN KEY (`Departament_id` )
REFERENCES `Hospital`.`Departament` (`id` )
ON DELETE SET NULL
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Hospital`.`Diagnosis`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Hospital`.`Diagnosis` (
`idDiagnosis` INT NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR(100) NOT NULL ,
`Departament_id` INT NOT NULL ,
`Personnel_id` INT NOT NULL ,
`Description` TEXT NULL ,
PRIMARY KEY (`idDiagnosis`) ,
INDEX `fk_Diagnosis_Departament1` (`Departament_id` ASC) ,
INDEX `fk_Diagnosis_Personnel1` (`Personnel_id` ASC) ,
CONSTRAINT `fk_Diagnosis_Departament1`
FOREIGN KEY (`Departament_id` )
REFERENCES `Hospital`.`Departament` (`id` )
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT `fk_Diagnosis_Personnel1`
FOREIGN KEY (`Personnel_id` )
REFERENCES `Hospital`.`Personnel` (`idPersonnel` )
ON DELETE SET NULL
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Hospital`.`Pacienty`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Hospital`.`Pacienty` (
`id` INT NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR(50) NOT NULL ,
`Address` VARCHAR(60) NOT NULL ,
`Birthday` DATE NOT NULL ,
`Data_arrival` DATE NOT NULL ,
`Data_depart` DATE NULL ,
`Departament_id` INT NOT NULL ,
`Diagnosis_id` INT NOT NULL ,
PRIMARY KEY (`id`) ,
INDEX `fk_Pacienty_Departament1` (`Departament_id` ASC) ,
INDEX `fk_Pacienty_Diagnosis1` (`Diagnosis_id` ASC) ,
CONSTRAINT `fk_Pacienty_Departament1`
FOREIGN KEY (`Departament_id` )
REFERENCES `Hospital`.`Departament` (`id` )
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT `fk_Pacienty_Diagnosis1`
FOREIGN KEY (`Diagnosis_id` )
REFERENCES `Hospital`.`Diagnosis` (`idDiagnosis` )
ON DELETE SET NULL
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Hospital`.`Medications`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Hospital`.`Medications` (
`idMedications` INT NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR(60) NOT NULL ,
`Numb_per_day` INT NULL ,
`Duration` INT NULL ,
`Price` DECIMAL NULL ,
`Description` TEXT NULL ,
PRIMARY KEY (`idMedications`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Hospital`.`Diagnosis_has_Medications`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Hospital`.`Diagnosis_has_Medications` (
`Diagnosis_idDiagnosis` INT NOT NULL ,
`Medications_idMedications` INT NOT NULL ,
PRIMARY KEY (`Diagnosis_idDiagnosis`, `Medications_idMedications`) ,
INDEX `fk_Diagnosis_has_Medications_Medications1` (`Medications_idMedications` ASC) ,
INDEX `fk_Diagnosis_has_Medications_Diagnosis1` (`Diagnosis_idDiagnosis` ASC) ,
CONSTRAINT `fk_Diagnosis_has_Medications_Diagnosis1`
FOREIGN KEY (`Diagnosis_idDiagnosis` )
REFERENCES `Hospital`.`Diagnosis` (`idDiagnosis` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Diagnosis_has_Medications_Medications1`
FOREIGN KEY (`Medications_idMedications` )
REFERENCES `Hospital`.`Medications` (`idMedications` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Текст программы
FormDepart.cs
using System;
using System.Windows.Forms;
using NHibernate;
using NHibernate.Criterion;
using db.Entities;
namespace db
{
public partial class FormDepart : Form
{
private readonly int operation;
private readonly MainForm parent;
private DataGridView DepartTable;
public FormDepart(MainForm aparent, int aoperation,DataGridView dgv)
{
InitializeComponent();
parent = aparent;
operation = aoperation;
DepartTable = dgv;
}
public FormDepart(MainForm aparent, DataGridView dgv)
{
InitializeComponent();
parent = aparent;
DepartTable = dgv;
}
public Departament GetDepByName(ISession session, string sName)
{
var idCriteria = session.CreateCriteria<Departament>();
idCriteria.Add(Restrictions.Like("Name", sName, MatchMode.Anywhere));
return idCriteria.List<Departament>()[0];
}
public void Fill()
{
using (var session = parent.MySession.OpenSession())
{
try
{
DepartTable.Rows.Clear();
var deps = session.CreateCriteria(typeof (Departament)).List<Departament>();
foreach (var d in deps)
DepartTable.Rows.Add(d.Name);
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
}
finally
{
session.Close();
}
}
}
void Update(string sName)
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
try
{
var newdep = GetDepByName(session, DepartTable.CurrentRow.Cells[0].Value.ToString());
newdep.Name = sName;
session.Update(newdep);
transaction.Commit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
transaction.Rollback();
}
finally
{
session.Close();
}
}
}
void Insert(string sName)
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
try
{
session.Save(new Departament {Name = sName});
transaction.Commit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
transaction.Rollback();
}
finally
{
session.Close();
}
}
}
public void Delete(string sName)
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
try
{
var olddep = GetDepByName(session, DepartTable.CurrentRow.Cells[0].Value.ToString());
session.Delete(olddep);
transaction.Commit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
transaction.Rollback();
}
finally
{
session.Close();
}
}
}
private void FormDepartLoad(object sender, EventArgs e)
{
if (operation == Const.UPDATE)
textBoxName.Text = DepartTable.CurrentRow.Cells[0].Value.ToString();
}
private void OkClick(object sender, EventArgs e)
{
if (textBoxName.Text != "")
{
switch (operation)
{
case Const.INSERT:
Insert(textBoxName.Text);
break;
case Const.UPDATE:
Update(textBoxName.Text);
break;
}
Fill();
Close();
}
else MessageBox.Show("Введены неверные данные");
}
private void CancelClick(object sender, EventArgs e)
{
Close();
}
}
}
FormDiagnosis.cs
using System;
using System.Windows.Forms;
using NHibernate.Criterion;
using db.Entities;
namespace db
{
public partial class FormDiagnosis : Form
{
private int operation;
public MainForm parent;
private DataGridView DiagnosisTable;
public FormDiagnosis(MainForm aparent, int aoperation,DataGridView dgv)
{
InitializeComponent();
parent = aparent;
operation = aoperation;
DiagnosisTable = dgv;
}
public FormDiagnosis(MainForm aparent, DataGridView dgv)
{
InitializeComponent();
parent = aparent;
DiagnosisTable = dgv;
}
private void FillcbDepartament()
{
using (var session = parent.MySession.OpenSession())
{
try
{
comboBoxDepartament.Items.Clear();
var deps = session.CreateCriteria(typeof(Departament)).List<Departament>();
foreach (var d in deps)
comboBoxDepartament.Items.Add(d.Name);
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
}
finally
{
session.Close();
}
}
}
private void FillcbPersonnel()
{
using (var session = parent.MySession.OpenSession())
{
try
{
comboBoxPersonnal.Items.Clear();
var pers = session.CreateCriteria(typeof(Personnel)).List<Personnel>();
foreach (var d in pers)
comboBoxPersonnal.Items.Add(d.Name);
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
}
finally
{
session.Close();
}
}
}
public void FillMed()
{
using (var session = parent.MySession.OpenSession())
{
try
{
int id = (int)DiagnosisTable.CurrentRow.Cells[0].Value;
var Diagnosis = session.Get<Diagnosis>(id);
dataGridViewMed.Rows.Clear();
foreach (var m in Diagnosis.Medications)
dataGridViewMed.Rows.Add(m.Id, m.Name, m.Numb_per_day, m.Duration, m.Price);
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
}
finally
{
session.Close();
}
}
}
public void Fill()
{
using (var session = parent.MySession.OpenSession())
{
try
{
DiagnosisTable.Rows.Clear();
var pers = session.CreateCriteria(typeof(Diagnosis)).List<Diagnosis>();
foreach (var p in pers)
DiagnosisTable.Rows.Add(p.Id, p.Name, p.Departament.Name, p.Personnel.Name, p.Description);
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
}
finally
{
session.Close();
}
}
}
void Insert(string sName, string DepName, string PersonnelName,string sDescription)
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
try
{
var idCriteria = session.CreateCriteria<Departament>();
idCriteria.Add(Restrictions.Like("Name", DepName, MatchMode.Anywhere));
var dep = idCriteria.List<Departament>()[0];
idCriteria = session.CreateCriteria<Personnel>();
idCriteria.Add(Restrictions.Like("Name", PersonnelName, MatchMode.Anywhere));
var pers = idCriteria.List<Personnel>()[0];
var newDiagn = new Diagnosis
{
Name = sName,
Departament = dep,
Personnel = pers,
Description = sDescription,
};
session.Save(newDiagn);
transaction.Commit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
transaction.Rollback();
}
finally
{
session.Close();
}
}
}
new void Update(string sName, string DepName, string PersonnelName, string sDescription)
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
try
{
var idCriteria = session.CreateCriteria<Departament>();
idCriteria.Add(Restrictions.Like("Name", DepName, MatchMode.Anywhere));
var dep = idCriteria.List<Departament>()[0];
idCriteria = session.CreateCriteria<Personnel>();
idCriteria.Add(Restrictions.Like("Name", PersonnelName, MatchMode.Anywhere));
var pers = idCriteria.List<Personnel>()[0];
int id = (int)DiagnosisTable.CurrentRow.Cells[0].Value;
var Diagnosis = session.Get<Diagnosis>(id);
Diagnosis.Name = sName;
Diagnosis.Departament = dep;
Diagnosis.Personnel = pers;
Diagnosis.Description = sDescription;
session.Save(Diagnosis);
transaction.Commit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
transaction.Rollback();
}
finally
{
session.Close();
}
}
}
public void Delete()
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
try
{
int id = (int)DiagnosisTable.CurrentRow.Cells[0].Value;
var olddiag = session.Get<Diagnosis>(id);
olddiag.Medications.Clear();
session.Delete(olddiag);
transaction.Commit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
transaction.Rollback();
}
finally
{
session.Close();
}
}
}
private void FormDiagnosisLoad(object sender, EventArgs e)
{
FillcbDepartament();
FillcbPersonnel();
if (operation == Const.UPDATE)
{
textBoxName.Text = DiagnosisTable.CurrentRow.Cells[1].Value.ToString();
comboBoxDepartament.SelectedItem = DiagnosisTable.CurrentRow.Cells[2].Value.ToString();
comboBoxPersonnal.SelectedItem = DiagnosisTable.CurrentRow.Cells[3].Value.ToString();
textBoxDescription.Text = DiagnosisTable.CurrentRow.Cells[4].Value.ToString();
FillMed();
}
}
private void ButtonOkClick(object sender, EventArgs e)
{
if (textBoxName.Text != "")
{
var dep = comboBoxDepartament.SelectedItem.ToString();
var pers = comboBoxPersonnal.SelectedItem.ToString();
switch (operation)
{
case Const.INSERT:
Insert(textBoxName.Text, dep, pers, textBoxDescription.Text);
break;
case Const.UPDATE:
Update(textBoxName.Text, dep, pers, textBoxDescription.Text);
break;
}
Fill();
Close();
}
else MessageBox.Show("Введены неверные данные");
}
private void ButtonCancelClick(object sender, EventArgs e)
{
Close();
}
private void MedAddClick(object sender, EventArgs e)
{
var md = new FormMed(this, parent.dataGridViewMedications, (int)DiagnosisTable.CurrentRow.Cells[0].Value);
md.Show();
}
private void MedDeleteClick(object sender, EventArgs e)
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
try
{
int idDiag = (int)DiagnosisTable.CurrentRow.Cells[0].Value;
int idMed = (int) dataGridViewMed.CurrentRow.Cells[0].Value;
var olddiag = session.Get<Diagnosis>(idDiag);
var oldMed = session.Get<Medication>(idMed);
olddiag.Medications.Remove(oldMed);
transaction.Commit();
FillMed();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
transaction.Rollback();
}
finally
{
session.Close();
}
}
}
}
}
FormMedications.cs
using System;
using System.Windows.Forms;
using db.Entities;
namespace db
{
public partial class FormMedications : Form
{
public int operation;
private MainForm parent;
private DataGridView MedicationsTable;
public FormMedications(MainForm aparent, int aoperation,DataGridView dgv)
{
InitializeComponent();
parent = aparent;
operation = aoperation;
MedicationsTable = dgv;
}
public FormMedications(MainForm aparent, DataGridView dgv)
{
InitializeComponent();
parent = aparent;
MedicationsTable = dgv;
}
public void Fill()
{
using (var session = parent.MySession.OpenSession())
{
try
{
MedicationsTable.Rows.Clear();
var meds = session.CreateCriteria(typeof(Medication)).List<Medication>();
foreach (var m in meds)
MedicationsTable.Rows.Add(m.Id, m.Name, m.Numb_per_day, m.Duration, m.Price, m.Description);
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
}
finally
{
session.Close();
}
}
}
void Insert(string sName, int iNumb_per_day, int iDuration, decimal dPrice, string sDescription)
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
try
{
var newMed = new Medication
{
Name = sName,
Numb_per_day = iNumb_per_day,
Duration = iDuration,
Price = dPrice,
Description = sDescription
};
session.Save(newMed);
transaction.Commit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
transaction.Rollback();
}
finally
{
session.Close();
}
}
}
new void Update(string sName, int iNumb_per_day, int iDuration, decimal dPrice, string sDescription)
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
try
{
int id = (int)MedicationsTable.CurrentRow.Cells[0].Value;
var medic = session.Get<Medication>(id);
medic.Name = sName;
medic.Numb_per_day = iNumb_per_day;
medic.Duration = iDuration;
medic.Price = dPrice;
medic.Description = sDescription;
session.Update(medic);
transaction.Commit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
transaction.Rollback();
}
finally
{
session.Close();
}
}
}
public void Delete()
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
try
{
int id = (int)MedicationsTable.CurrentRow.Cells[0].Value;
var oldmedic = session.Get<Medication>(id);
session.Delete(oldmedic);
transaction.Commit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
transaction.Rollback();
}
finally
{
session.Close();
}
}
}
private void FormMedicationsLoad(object sender, EventArgs e)
{
if (operation == Const.UPDATE)
{
textBoxName.Text = MedicationsTable.CurrentRow.Cells[1].Value.ToString();
textBoxNumPerDay.Text = MedicationsTable.CurrentRow.Cells[2].Value.ToString();
textBoxDuration.Text = MedicationsTable.CurrentRow.Cells[3].Value.ToString();
textBoxPrice.Text = MedicationsTable.CurrentRow.Cells[4].Value.ToString();
textBoxDescription.Text = MedicationsTable.CurrentRow.Cells[5].Value.ToString();
}
}
private void ButtonOkClick(object sender, EventArgs e)
{
int Duration = 0;
int Numb_per_day = 0;
decimal Price = 0;
try
{
if (textBoxName.Text == "") throw new ArgumentNullException("Name");
Duration = Convert.ToInt32(textBoxDuration.Text);
Numb_per_day = Convert.ToInt32(textBoxNumPerDay.Text);
Price = Convert.ToDecimal(textBoxPrice.Text);
}
catch (Exception ex)
{
MessageBox.Show("Введены неверные данные");
}
switch (operation)
{
case Const.INSERT:
Insert(textBoxName.Text, Numb_per_day, Duration, Price, textBoxDescription.Text);
break;
case Const.UPDATE:
Update(textBoxName.Text, Numb_per_day, Duration, Price, textBoxDescription.Text);
break;
}
Fill();
Close();
}
private void ButtonCancelClick(object sender, EventArgs e)
{
Close();
}
}
}
FormPacienty.cs
using System;
using System.Windows.Forms;
using NHibernate.Criterion;
using db.Entities;
namespace db
{
public partial class FormPacienty : Form
{
private int operation;
private MainForm parent;
private DataGridView PacientyTable;
public FormPacienty(MainForm aparent, int aoperation,DataGridView dgv)
{
InitializeComponent();
parent = aparent;
operation = aoperation;
PacientyTable = dgv;
}
public FormPacienty(MainForm aparent, DataGridView dgv)
{
InitializeComponent();
parent = aparent;
PacientyTable = dgv;
}
private void FillcbDepartament()
{
using (var session = parent.MySession.OpenSession())
{
try
{
comboBoxDepartament.Items.Clear();
var deps = session.CreateCriteria(typeof(Departament)).List<Departament>();
foreach (var d in deps)
comboBoxDepartament.Items.Add(d.Name);
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
}
finally
{
session.Close();
}
}
}
private void FillcbDiagnosis()
{
using (var session = parent.MySession.OpenSession())
{
try
{
comboBoxDiagnosis.Items.Clear();
var diag = session.CreateCriteria(typeof(Diagnosis)).List<Diagnosis>();
foreach (var d in diag)
comboBoxDiagnosis.Items.Add(d.Name);
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
}
finally
{
session.Close();
}
}
}
public void Fill()
{
using (var session = parent.MySession.OpenSession())
{
try
{
PacientyTable.Rows.Clear();
var pacients = session.CreateCriteria(typeof(Pacient)).List<Pacient>();
foreach (var p in pacients)
PacientyTable.Rows.Add(p.Id, p.Name, p.Address, p.Birthday, p.Diagnosis.Name, p.Departament.Name,p.Data_arrival,p.Data_depart);
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
}
finally
{
session.Close();
}
}
}
void Insert(string sName, string sAddress, DateTime sBirthday,
DateTime sData_arrival, DateTime sData_depart,string DepName,string DiagnosName)
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
try
{
var idCriteria = session.CreateCriteria<Departament>();
idCriteria.Add(Restrictions.Like("Name", DepName, MatchMode.Anywhere));
var dep = idCriteria.List<Departament>()[0];
idCriteria = session.CreateCriteria<Diagnosis>();
idCriteria.Add(Restrictions.Like("Name", DiagnosName, MatchMode.Anywhere));
var diag = idCriteria.List<Diagnosis>()[0];
var newPecient = new Pacient
{
Name = sName,
Address = sAddress,
Birthday = sBirthday,
Data_arrival = sData_arrival,
Data_depart = sData_depart,
Departament = dep,
Diagnosis = diag
};
session.Save(newPecient);
transaction.Commit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
transaction.Rollback();
}
finally
{
session.Close();
}
}
}
void Update(string sName, string sAddress, DateTime sBirthday,
DateTime sData_arrival, DateTime sData_depart, string DepName, string DiagnosName)
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
try
{
var idCriteria = session.CreateCriteria<Departament>();
idCriteria.Add(Restrictions.Like("Name", DepName, MatchMode.Anywhere));
var dep = idCriteria.List<Departament>()[0];
idCriteria = session.CreateCriteria<Diagnosis>();
idCriteria.Add(Restrictions.Like("Name", DiagnosName, MatchMode.Anywhere));
var diag = idCriteria.List<Diagnosis>()[0];
int id = (int)PacientyTable.CurrentRow.Cells[0].Value;
var pacient = session.Get<Pacient>(id);
pacient.Name = sName;
pacient.Address = sAddress;
pacient.Birthday = sBirthday;
pacient.Data_arrival = sData_arrival;
pacient.Data_depart = sData_depart;
pacient.Diagnosis = diag;
pacient.Departament = dep;
session.Update(pacient);
transaction.Commit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
transaction.Rollback();
}
finally
{
session.Close();
}
}
}
public void Delete()
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
try
{
int id = (int)PacientyTable.CurrentRow.Cells[0].Value;
var oldpacient = session.Get<Pacient>(id);
session.Delete(oldpacient);
transaction.Commit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
transaction.Rollback();
}
finally
{
session.Close();
}
}
}
private void FormPacientyLoad(object sender, EventArgs e)
{
FillcbDepartament();
FillcbDiagnosis();
if (operation == Const.UPDATE)
{
textBoxName.Text = PacientyTable.CurrentRow.Cells[1].Value.ToString();
textBoxAddress.Text = PacientyTable.CurrentRow.Cells[2].Value.ToString();
dateTimePickerBirthday.Value = (DateTime)PacientyTable.CurrentRow.Cells[3].Value;
comboBoxDiagnosis.SelectedItem = PacientyTable.CurrentRow.Cells[4].Value.ToString();
comboBoxDepartament.SelectedItem = PacientyTable.CurrentRow.Cells[5].Value.ToString();
dateTimePickerArrival.Value = (DateTime)PacientyTable.CurrentRow.Cells[6].Value;
dateTimePickerDepart.Value = (DateTime)PacientyTable.CurrentRow.Cells[7].Value;
}
}
private void ButtonOkClick(object sender, EventArgs e)
{
if (textBoxName.Text != "" && textBoxAddress.Text != "")
{
var dep = comboBoxDepartament.SelectedItem.ToString();
var diag = comboBoxDiagnosis.SelectedItem.ToString();
switch (operation)
{
case Const.INSERT:
Insert(textBoxName.Text, textBoxAddress.Text, dateTimePickerBirthday.Value,
dateTimePickerArrival.Value, dateTimePickerDepart.Value, dep, diag);
break;
case Const.UPDATE:
Update(textBoxName.Text, textBoxAddress.Text, dateTimePickerBirthday.Value,
dateTimePickerArrival.Value, dateTimePickerDepart.Value, dep, diag);
break;
}
Fill();
Close();
}
else MessageBox.Show("Введены неверные данные");
}
private void ButtonCancelClick(object sender, EventArgs e)
{
Close();
}
public void SaveXML(string FileName)
{
try
{
using (var session = parent.MySession.OpenSession())
{
var Pacienty = session.CreateCriteria(typeof(Pacient)).List<Pacient>();
using (XmlTextWriter writer = new XmlTextWriter(FileName, Encoding.Unicode))
{
writer.WriteStartDocument();
writer.WriteStartElement("Pacients");
foreach (var pac in Pacienty)
{
writer.WriteStartElement("Pacient");
writer.WriteAttributeString("id", pac.Id.ToString());
writer.WriteAttributeString("Name", pac.Name);
writer.WriteAttributeString("Address", pac.Address);
writer.WriteAttributeString("Birthday", pac.Birthday.ToShortDateString());
writer.WriteAttributeString("Data_arrival", pac.Data_arrival.ToShortDateString());
writer.WriteAttributeString("Data_depart", pac.Data_depart.ToShortDateString());
writer.WriteAttributeString("Departament", pac.Departament.Name);
writer.WriteStartElement("Diagnos");
writer.WriteAttributeString("id", pac.Diagnosis.Id.ToString());
writer.WriteAttributeString("Name", pac.Diagnosis.Name);
writer.WriteAttributeString("Departament", pac.Diagnosis.Departament.Name);
writer.WriteAttributeString("Personnel", pac.Diagnosis.Personnel.Name);
writer.WriteAttributeString("Description", pac.Diagnosis.Description);
writer.WriteEndElement();
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndDocument();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка");
}
}
public Departament GetDepByName(ISession session, string sName)
{
var idCriteria = session.CreateCriteria<Departament>();
idCriteria.Add(Restrictions.Like("Name", sName, MatchMode.Anywhere));
return idCriteria.List<Departament>()[0];
}
public void LoadXML(string FileName)
{
try
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
var Pacients = session.CreateCriteria(typeof(Pacient)).List<Pacient>();
using (XmlTextReader reader = new XmlTextReader(FileName))
{
Pacient pac = null;
reader.WhitespaceHandling = WhitespaceHandling.None; // пропускаем пустые узлы
while (reader.Read())
if (reader.NodeType == XmlNodeType.Element)
{
if (reader.Name == "Pacient")
{
Departament d = GetDepByName(session, reader.GetAttribute("Departament"));
pac = new Pacient
{
Id = int.Parse(reader.GetAttribute("id")),
Name = reader.GetAttribute("Name"),
Address = reader.GetAttribute("Address"),
Birthday = DateTime.Parse(reader.GetAttribute("Birthday")),
Data_arrival =
DateTime.Parse(reader.GetAttribute("Data_arrival")),
Data_depart =
DateTime.Parse(reader.GetAttribute("Data_depart")),
Departament = d
};
}
if (reader.Name == "Diagnos")
{
var idCriteria = session.CreateCriteria<Personnel>();
idCriteria.Add(Restrictions.Like("Name", reader.GetAttribute("Personnel"), MatchMode.Anywhere));
Diagnosis diag = new Diagnosis
{
Id = int.Parse(reader.GetAttribute("id")),
Name = reader.GetAttribute("Name"),
Departament =
GetDepByName(session,
reader.GetAttribute("Departament")),
Personnel = idCriteria.List<Personnel>()[0],
Description = reader.GetAttribute("Description"),
Medications = new List<Medication>()
};
if (pac != null)
{
pac.Diagnosis = diag;
bool b = false;
foreach (var p in Pacients)
{
if (p.Diagnosis.Description == null) p.Diagnosis.Description = "";
if (p.Equals(pac)) b = true;
}
if (!b)
session.Save(pac);
pac = null;
}
}
}
}
transaction.Commit();
}
Fill();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка");
}
}
}
}
FormPersonnel.cs
using System;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using NHibernate;
using NHibernate.Criterion;
using db.Entities;
namespace db
{
public partial class FormPersonnel : Form
{
private int operation;
private MainForm parent;
private DataGridView PersonnelTable;
public FormPersonnel(MainForm aparent, int aoperation,DataGridView dgv)
{
InitializeComponent();
parent = aparent;
operation = aoperation;
PersonnelTable = dgv;
}
public FormPersonnel(MainForm aparent, DataGridView dgv)
{
InitializeComponent();
parent = aparent;
PersonnelTable = dgv;
}
public Departament GetDepByName(ISession session, string sName)
{
var idCriteria = session.CreateCriteria<Departament>();
idCriteria.Add(Restrictions.Like("Name", sName, MatchMode.Anywhere));
return idCriteria.List<Departament>()[0];
}
public void Fill()
{
using (var session = parent.MySession.OpenSession())
{
try
{
PersonnelTable.Rows.Clear();
var pers = session.CreateCriteria(typeof(Personnel)).List<Personnel>();
foreach (var p in pers)
PersonnelTable.Rows.Add(p.Id,p.Name,p.Address,p.Birthday,p.Phone,p.Post,p.Salary,p.Departament.Name);
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
}
finally
{
session.Close();
}
}
}
void Insert(string sName, string sAddress, DateTime sBirthday ,string sPhone, string sPost,decimal dSalary, string depName)
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
try
{
var dep = GetDepByName(session, depName);
var newPers = new Personnel
{
Name = sName,
Address = sAddress,
Birthday = sBirthday,
Phone = sPhone,
Post = sPost,
Salary = dSalary,
Departament = dep
};
dep.AddPersonnel(newPers);
session.Save(newPers);
transaction.Commit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
transaction.Rollback();
}
finally
{
session.Close();
}
}
}
void Update(string sName, string sAddress, DateTime sBirthday ,string sPhone, string sPost,decimal dSalary, string depName)
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
try
{
int id = (int)PersonnelTable.CurrentRow.Cells["idPersonnel"].Value;
var pers = session.Get<Personnel>(id);
pers.Name = sName;
pers.Address = sAddress;
pers.Birthday = sBirthday;
pers.Phone = sPhone;
pers.Post = sPost;
pers.Salary = dSalary;
pers.Departament = GetDepByName(session, depName);
session.Update(pers);
transaction.Commit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
transaction.Rollback();
}
finally
{
session.Close();
}
}
}
public void Delete()
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
try
{
int id = (int) PersonnelTable.CurrentRow.Cells["idPersonnel"].Value;
var oldpers = session.Get<Personnel>(id);
session.Delete(oldpers);
transaction.Commit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
transaction.Rollback();
}
finally
{
session.Close();
}
}
}
private void FillcbDepartament()
{
using (var session = parent.MySession.OpenSession())
{
try
{
comboBoxDepartament.Items.Clear();
var deps = session.CreateCriteria(typeof(Departament)).List<Departament>();
foreach (var d in deps)
comboBoxDepartament.Items.Add(d.Name);
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Ошибка подключения к БД");
}
finally
{
session.Close();
}
}
}
private void FormPersonnelLoad(object sender, EventArgs e)
{
FillcbDepartament();
if (operation == Const.UPDATE)
{
textBoxName.Text = PersonnelTable.CurrentRow.Cells["PersonnalName"].Value.ToString();
textBoxAddress.Text = PersonnelTable.CurrentRow.Cells[2].Value.ToString();
dateTimePickerBirthday.Value = (DateTime)PersonnelTable.CurrentRow.Cells[3].Value;
textBoxPhone.Text = PersonnelTable.CurrentRow.Cells[4].Value.ToString();
textBoxPost.Text = PersonnelTable.CurrentRow.Cells[5].Value.ToString();
textBoxSalary.Text = PersonnelTable.CurrentRow.Cells[6].Value.ToString();
comboBoxDepartament.SelectedItem = PersonnelTable.CurrentRow.Cells[7].Value.ToString();
}
}
bool PhoneChecker (string Phone)
{
string pattern = @"((8|\+7)[\- ]?)?(\(?\d{3}\)?[\- ]?)?[\d\- ]{7,10}";
Regex regex = new Regex(pattern);
Match match = regex.Match(Phone);
return match.Success;
}
private void OkClick(object sender, EventArgs e)
{
if (textBoxName.Text != "" && textBoxAddress.Text != "" &&
PhoneChecker(textBoxPhone.Text) && textBoxPost.Text != "")
{
var dep = comboBoxDepartament.SelectedItem.ToString();
switch (operation)
{
case Const.INSERT:
Insert(textBoxName.Text, textBoxAddress.Text, dateTimePickerBirthday.Value,
textBoxPhone.Text, textBoxPost.Text, decimal.Parse(textBoxSalary.Text), dep);
break;
case Const.UPDATE:
Update(textBoxName.Text, textBoxAddress.Text, dateTimePickerBirthday.Value,
textBoxPhone.Text, textBoxPost.Text, decimal.Parse(textBoxSalary.Text), dep);
break;
}
Fill();
Close();
}
else MessageBox.Show("Введены неверные данные");
}
private void CancelClick(object sender, EventArgs e)
{
Close();
}
public void SaveXML (string FileName)
{
IList<Personnel> Personnels = null;
try
{
using (var session = parent.MySession.OpenSession())
{
Personnels = session.CreateCriteria(typeof(Personnel)).List<Personnel>();
using (XmlTextWriter writer = new XmlTextWriter(FileName, Encoding.Unicode))
{
writer.WriteStartDocument();
writer.WriteStartElement("Personnals");
foreach (var personnel in Personnels)
{
writer.WriteStartElement("Row");
writer.WriteAttributeString("id", personnel.Id.ToString());
writer.WriteAttributeString("Name", personnel.Name);
writer.WriteAttributeString("Address", personnel.Address);
writer.WriteAttributeString("Birthday", personnel.Birthday.ToShortDateString());
writer.WriteAttributeString("Phone", personnel.Phone);
writer.WriteAttributeString("Post", personnel.Post);
writer.WriteAttributeString("Salary", personnel.Salary.ToString());
writer.WriteAttributeString("Departament", personnel.Departament.Name);
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndDocument();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка");
}
}
public void LoadXML (string FileName)
{
try
{
using (var session = parent.MySession.OpenSession())
using (var transaction = session.BeginTransaction())
{
var Personnels = session.CreateCriteria(typeof(Personnel)).List<Personnel>();
using (XmlTextReader reader = new XmlTextReader(FileName))
{
reader.WhitespaceHandling = WhitespaceHandling.None; // пропускаем пустые узлы
while (reader.Read())
if (reader.NodeType == XmlNodeType.Element)
if (reader.Name == "Row")
{
Departament d = GetDepByName(session, reader.GetAttribute("Departament"));
Personnel pers = new Personnel
{
Id = int.Parse(reader.GetAttribute("id")),
Name = reader.GetAttribute("Name"),
Address = reader.GetAttribute("Address"),
Birthday = DateTime.Parse(reader.GetAttribute("Birthday")),
Phone = reader.GetAttribute("Phone"),
Post = reader.GetAttribute("Post"),
Salary = decimal.Parse(reader.GetAttribute("Salary")),
Departament = d
};
bool b = false;
foreach (var p in Personnels)
if (p.Equals(pers)) b = true;
if (!b)
session.Save(pers);
}
}
transaction.Commit();
}
Fill();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка");
}
}
}
}
MainForm.cs
using System;
using System.Windows.Forms;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using db.Mappings;
namespace db
{
public partial class MainForm : Form
{
private string host = "localhost";
private string database = "hospital";
private string user = "root";
private string password = "admin";
public ISessionFactory MySession;
public string Host
{
get { return host; }
set { host = value; }
}
public string DataBase
{
get { return database; }
set { database = value; }
}
public string User
{
get { return user; }
set { user = value; }
}
public string Password
{
get { return password; }
set { password = value; }
}
public MainForm()
{
InitializeComponent();
}
public void CreateSessionFactory()
{
try
{
var config = Fluently.Configure()
.Database(MySQLConfiguration.Standard
.ConnectionString(x => x.Database(DataBase)
.Server(Host)
.Username(User)
.Password(Password)))
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<DepartMap>())
.BuildConfiguration();
MySession = config.BuildSessionFactory();
}
catch (Exception ex)
{
MySession = null;
MessageBox.Show(ex.Message, "Ошибка создания сессии");
var OptionsForm = new Options(this);
OptionsForm.Show();
}
}
public void Form1Load(object sender, EventArgs e)
{
CreateSessionFactory();
if (MySession != null)
{
FormDepart Departament = new FormDepart(this, dataGridViewDepart);
FormPersonnel Personnel = new FormPersonnel(this, dataGridViewPersonnal);
FormPacienty Pacienty = new FormPacienty(this, dataGridViewPacienty);
FormDiagnosis Diagnosis = new FormDiagnosis(this, dataGridViewDiagnosis);
FormMedications Medications = new FormMedications(this, dataGridViewMedications);
Departament.Fill();
Personnel.Fill();
Pacienty.Fill();
Diagnosis.Fill();
Medications.Fill();
}
}
}
private void PersonnalAdd_Click(object sender, EventArgs e)
{
FormPersonnel Personnel = new FormPersonnel(this,Const.INSERT, dataGridViewPersonnal);
Personnel.Show();
}
private void PersonnalChange_Click(object sender, EventArgs e)
{
FormPersonnel Personnel = new FormPersonnel(this, Const.UPDATE, dataGridViewPersonnal);
Personnel.Show();
}
private void PersonnalDelete_Click(object sender, EventArgs e)
{
FormPersonnel Personnel = new FormPersonnel(this, dataGridViewPersonnal);
Personnel.Delete();
Personnel.Fill();
}
private void tabControl1_Selecting(object sender, TabControlCancelEventArgs e)
{
switch (e.TabPageIndex)
{
case 0:
{
FormDepart Departament = new FormDepart(this, dataGridViewDepart);
Departament.Fill();
break;
}
case 1:
{
FormPersonnel Personnel = new FormPersonnel(this, dataGridViewPersonnal);
Personnel.Fill();
break;
}
case 2:
{
FormPacienty Pacienty = new FormPacienty(this, dataGridViewPacienty);
Pacienty.Fill();
break;
}
case 3:
{
FormDiagnosis Diagnosis = new FormDiagnosis(this, dataGridViewDiagnosis);
Diagnosis.Fill();
break;
}
case 4:
{
FormMedications Medications = new FormMedications(this, dataGridViewMedications);
Medications.Fill();
break;
}
}
}
private void tsbPersonnalSave_Click(object sender, EventArgs e)
{
if (sfdPersonnal.ShowDialog() == DialogResult.OK)
{
FormPersonnel fp = new FormPersonnel(this,dataGridViewPersonnal);
fp.SaveXML(sfdPersonnal.FileName);
}
}
private void tsbPersonnalLoad_Click(object sender, EventArgs e)
{
if (ofdPersonnel.ShowDialog() == DialogResult.OK)
{
FormPersonnel fp = new FormPersonnel(this, dataGridViewPersonnal);
fp.LoadXML(ofdPersonnel.FileName);
}
}
private void tsbPacientSave_Click(object sender, EventArgs e)
{
if (sfdPacienty.ShowDialog() == DialogResult.OK)
{
FormPacienty fp = new FormPacienty(this,dataGridViewPacienty);
fp.SaveXML(sfdPacienty.FileName);
}
}
private void tsbPacientLoad_Click(object sender, EventArgs e)
{
if (ofdPacienty.ShowDialog() == DialogResult.OK)
{
FormPacienty fp = new FormPacienty(this, dataGridViewPacienty);
fp.LoadXML(ofdPacienty.FileName);
}
}
}
class Const
{
public const int SELECT = 0;
public const int INSERT = 1;
public const int UPDATE = 2;
public const int DELETE = 3;
}
}