public IEnumerable <ItemEmployee> GetAllEmp(int id)
{
List <ItemEmployee> listEmp = new List <ItemEmployee>();
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "select e.id,e.name, e.surname,e.patronymic from itememp e inner join itemrel r on r.emp_id = e.id where r.dep_id = @id;";
cmd.Parameters.AddWithValue("@id", id);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
ItemEmployee emp = new ItemEmployee();
emp.id = Convert.ToInt32(rdr["id"]);
emp.name = rdr["name"].ToString();
emp.surname = rdr["surname"].ToString();
emp.patronymic = rdr["patronymic"].ToString();
emp.position = GetPos(emp.id);
emp.listPermision = GetAllPer(emp.id).ToList();
// Employee employee = new Employee();
// employee.id = System.Convert.ToInt32(rdr["id"]);
// employee.name = rdr["name"].ToString();
// employee.surname = rdr["surname"].ToString();
// employee.patronymic = rdr["patronymic"].ToString();
listEmp.Add(emp);
}
con.Close();
}
return(listEmp);
}
public void InsertEmp(ItemEmployee emp)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "insert into itememp(name,surname, patronymic) values(@name,@surname,@patronymic)";
cmd.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = emp.name;
cmd.Parameters.Add("@surname", SqlDbType.NVarChar, 100).Value = emp.surname;
cmd.Parameters.Add("@patronymic", SqlDbType.NVarChar, 100).Value = emp.patronymic;
con.Open();
cmd.ExecuteNonQuery();
}
}
public void insertPer(ItemEmployee emp, int per_id)
{
int emp_id = getId(emp);
if (emp_id != -1)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "insert into perrel(emp_id,per_id) values(@emp_id,@per_id)";
cmd.Parameters.AddWithValue("@emp_id", emp_id);
cmd.Parameters.AddWithValue("@dep_id", per_id);
con.Open();
cmd.ExecuteNonQuery();
}
}
}
public void insertIntoRel(ItemEmployee emp, int pos_id, int dep_id)
{
int emp_id = getId(emp);
if (emp_id != -1)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "insert into itemrel(emp_id,pos_id, dep_id) values(@emp_id,@pos_id,@dep_id)";
cmd.Parameters.Add("@emp_id", SqlDbType.NVarChar, 100).Value = emp_id;
cmd.Parameters.Add("@pos_id", SqlDbType.NVarChar, 100).Value = pos_id;
cmd.Parameters.Add("@dep_id", SqlDbType.NVarChar, 100).Value = dep_id;
con.Open();
cmd.ExecuteNonQuery();
}
}
}
public int getId(ItemEmployee emp)
{
int id = -1;
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "select id from itememp where name = @name and surname = @surname and patronymic = @patronymic;";
cmd.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = emp.name;
cmd.Parameters.Add("@surname", SqlDbType.NVarChar, 100).Value = emp.surname;
cmd.Parameters.Add("@patronymic", SqlDbType.NVarChar, 100).Value = emp.patronymic;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
id = Convert.ToInt32(rdr["id"]);
}
con.Close();
}
return(id);
}