public PhysicalPerson GetPhysicalPerson_ByID()
{
try
{
this.cmd = new SqlCommand();
this.cmd.Connection = this.conn;
this.cmd.CommandText = "SELECT * FROM FC_GET__PHYSICALPERSON_BYID(@ID)";
this.cmd.Parameters.AddWithValue("@ID", this.pp.Id);
SqlDataReader sqr = this.cmd.ExecuteReader();
if (sqr.HasRows)
{
PhysicalPerson p = new PhysicalPerson();
sqr.Read();
p.Id = sqr.GetInt32(0);
p.Name = sqr.GetString(1);
p.Email = sqr.GetString(2);
p.Salary = sqr.GetDecimal(3);
p.DateBirth = sqr.GetDateTime(4);
p.Genre = sqr.GetString(5)[0];
return(p);
}
return(null);
}
catch (Exception)
{
throw;
}
}
public List <PhysicalPerson> GetPhysicalPerson_BySalaryRange(decimal sal1, decimal sal2)
{
try
{
this.cmd = new SqlCommand();
this.cmd.Connection = this.conn;
this.cmd.CommandText = "SELECT * FROM FC_GET__PHYSICALPERSON_BYSALARYRANGE(@SAL1, @SAL2)";
this.cmd.Parameters.Add(new SqlParameter("@SAL1", sal1));
this.cmd.Parameters.Add(new SqlParameter("@SAL2", sal2));
SqlDataReader sqr = this.cmd.ExecuteReader();
if (sqr.HasRows)
{
List <PhysicalPerson> list = new List <PhysicalPerson>();
while (sqr.Read())
{
PhysicalPerson p = new PhysicalPerson();
p.Id = sqr.GetInt32(0);
p.Name = sqr.GetString(1);
p.Email = sqr.GetString(2);
p.Salary = sqr.GetDecimal(3);
p.DateBirth = sqr.GetDateTime(4);
p.Genre = sqr.GetString(5)[0];
list.Add(p);
}
return(list);
}
return(null);
}
catch (Exception)
{
throw;
}
}
public List <PhysicalPerson> GetPhysicalPerson_ByName()
{
try
{
this.cmd = new SqlCommand();
this.cmd.Connection = this.conn;
this.cmd.CommandText = "SELECT * FROM FC_GET__PHYSICALPERSON_BYNAME(@NAME)";
this.cmd.Parameters.AddWithValue("@NAME", this.pp.Name + '%');
SqlDataReader sqr = this.cmd.ExecuteReader();
if (sqr.HasRows)
{
List <PhysicalPerson> list = new List <PhysicalPerson>();
while (sqr.Read())
{
PhysicalPerson p = new PhysicalPerson();
p.Id = sqr.GetInt32(0);
p.Name = sqr.GetString(1);
p.Email = sqr.GetString(2);
p.Salary = sqr.GetDecimal(3);
p.DateBirth = sqr.GetDateTime(4);
p.Genre = sqr.GetString(5)[0];
list.Add(p);
}
return(list);
}
return(null);
}
catch (Exception)
{
throw;
}
}
public PhysicalPerson GetPhysicalPerson_LowerSalary()
{
try
{
this.cmd = new SqlCommand();
this.cmd.Connection = this.conn;
this.cmd.CommandText = "SP_GET__PHYSICALPERSON_LOWSAL";
this.cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader sqr = this.cmd.ExecuteReader();
if (sqr.HasRows)
{
PhysicalPerson p = new PhysicalPerson();
sqr.Read();
p.Id = sqr.GetInt32(0);
p.Name = sqr.GetString(1);
p.Email = sqr.GetString(2);
p.Salary = sqr.GetDecimal(3);
p.DateBirth = sqr.GetDateTime(4);
p.Genre = sqr.GetString(5)[0];
return(p);
}
return(null);
}
catch (Exception)
{
throw;
}
}
public List <PhysicalPerson> GetPhysicalPerson_SalaryEqualAVG()
{
try
{
this.cmd = new SqlCommand();
this.cmd.Connection = this.conn;
this.cmd.CommandText = "SP_GET__PHYSICALPERSON_SAL_EQUALAVG";
this.cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader sqr = this.cmd.ExecuteReader();
if (sqr.HasRows)
{
List <PhysicalPerson> list = new List <PhysicalPerson>();
while (sqr.Read())
{
PhysicalPerson p = new PhysicalPerson();
p.Id = sqr.GetInt32(0);
p.Name = sqr.GetString(1);
p.Email = sqr.GetString(2);
p.Salary = sqr.GetDecimal(3);
p.DateBirth = sqr.GetDateTime(4);
p.Genre = sqr.GetString(5)[0];
list.Add(p);
sqr.NextResult();
}
return(list);
}
return(null);
}
catch (Exception)
{
throw;
}
}
public static PhysicalPerson GetPhysicalPerson_ByID(Int32 id)
{
PhysicalPerson pp = new PhysicalPerson();
pp.Id = id;
Controller ctr = new Controller(pp);
return(ctr.GetPhysicalPerson_ByID());
}
public static int Delete_PhysicalPerson(Int32 id)
{
PhysicalPerson pp = new PhysicalPerson();
pp.Id = id;
Controller ctr = new Controller(pp);
return(ctr.Delete_PhysicalPerson_tr());
}
public static List <PhysicalPerson> GetPhysicalPerson_ByName(String name)
{
PhysicalPerson pp = new PhysicalPerson();
pp.Name = name;
Controller ctr = new Controller(pp);
return(ctr.GetPhysicalPerson_ByName());
}
public static int Insert_PhysicalPerson(String name, String email, Decimal salary, DateTime dateBirth, Char genre)
{
PhysicalPerson pp = new PhysicalPerson();
pp.Name = name;
pp.Email = email;
pp.Salary = salary;
pp.DateBirth = dateBirth;
pp.Genre = genre;
Controller ctr = new Controller(pp);
return(ctr.Insert_PhysicalPerson_tr());
}
public Controller(PhysicalPerson pp)
{
this.conn = Connection.Connect();
this.pp = pp;
}