public Object SavePerson(Person p)
{
if (!p.Validate())
{
throw new InvalidRequestFormat("Структура Person заполена некорректно");
}
DbCommand dc = connection.CreateCommand();
if (p.id > 0)
{
dc.CommandText = "update person set name = ?, surname = ?, cours = ?, grp = ? where id = ?;";
DbParameter par = dc.CreateParameter();
par.DbType = DbType.String;
par.Value = p.name;
dc.Parameters.Add(par);
par = dc.CreateParameter();
par.DbType = DbType.String;
par.Value = p.surname;
dc.Parameters.Add(par);
par = dc.CreateParameter();
par.DbType = DbType.Int32;
par.Value = p.cours;
dc.Parameters.Add(par);
par = dc.CreateParameter();
par.DbType = DbType.Int32;
par.Value = p.group;
dc.Parameters.Add(par);
par = dc.CreateParameter();
par.DbType = DbType.Int32;
par.Value = p.id;
dc.Parameters.Add(par);
dc.ExecuteNonQuery();
}
else
{
dc.CommandText = "insert into person (name, surname, cours, grp) values(?, ?, ?, ?) returning id;";
DbParameter par = dc.CreateParameter();
par.DbType = DbType.String;
par.Value = p.name;
dc.Parameters.Add(par);
par = dc.CreateParameter();
par = dc.CreateParameter();
par.DbType = DbType.String;
par.Value = p.surname;
dc.Parameters.Add(par);
par = dc.CreateParameter();
par.DbType = DbType.Int32;
par.Value = p.cours;
dc.Parameters.Add(par);
par = dc.CreateParameter();
par.DbType = DbType.Int32;
par.Value = p.group;
dc.Parameters.Add(par);
p.id = (int)dc.ExecuteScalar();
}
return p;
}
public Person[] GetAllPersons()
{
List<Person> persons = new List<Person>();
DbCommand dc = connection.CreateCommand();
dc.CommandText = "select id, name, surname, cours, grp from person order by surname, name;";
using (DbDataReader dr = dc.ExecuteReader())
{
while (dr.Read())
{
Person p = new Person();
p.id = dr.GetInt32(0);
p.name = dr.GetString(1);
p.surname = dr.GetString(2);
p.cours = dr.GetInt32(3);
p.group = dr.GetInt32(4);
persons.Add(p);
};
}
return persons.ToArray();
}
public Person GetPerson(int id)
{
Person p = null;
DbCommand dc = connection.CreateCommand();
dc.CommandText = "select id, name, surname, cours, grp from person where id = ?;";
DbParameter par = dc.CreateParameter();
par.DbType = DbType.Int32;
par.Value = id;
dc.Parameters.Add(par);
using (DbDataReader dr = dc.ExecuteReader())
{
if (dr.HasRows)
{
p = new Person();
p.id = dr.GetInt32(0);
p.name = dr.GetString(1);
p.surname = dr.GetString(2);
p.cours = dr.GetInt32(3);
p.group = dr.GetInt32(4);
}
}
return p;
}
private Item GetItemFromSelectedRow(DataGridView grid)
{
DataGridViewRow row = grid.CurrentRow;
if (row == null)
{
throw new Exception("Элемент не выбран в таблице");
}
if (grid == personGrid)
{
if (!Person.ValidatName(row.Cells["name"].Value) ||
!Person.ValidatName(row.Cells["surname"].Value) ||
!Person.ValidateCourse(row.Cells["cours"].Value) ||
!Person.ValidatGroup(row.Cells["Group"].Value))
{
return null;
}
Person p = new Person();
p.id = Int32.Parse(row.Cells[0].Value.ToString());
p.name = row.Cells["name"].Value.ToString();
p.surname = row.Cells["surname"].Value.ToString();
p.group = Int32.Parse(row.Cells["Group"].Value.ToString());
p.cours = Int32.Parse(row.Cells["cours"].Value.ToString());
return p;
}
else if (grid == subjectGrid)
{
if (!Subject.ValidatName(row.Cells["SubjectName"].Value) ||
!Subject.ValidatName(row.Cells["SubjectTeacher"].Value) ||
!Subject.ValidateHour(row.Cells["SubjectHour"].Value))
{
return null;
}
Subject s = new Subject();
s.id = Int32.Parse(row.Cells["SubjectId"].Value.ToString());
s.name = row.Cells["SubjectName"].Value.ToString();
s.teacher = row.Cells["SubjectTeacher"].Value.ToString();
s.hour = Int32.Parse(row.Cells["SubjectHour"].Value.ToString());
return s;
}
else if (grid == markGrid)
{
Mark m = (Mark)row.Cells["MarkId"].Value;
return m;
}
return null;
}