public void Inserir(SalaXProfessor obj)
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Db"].ConnectionString))
{
string strSQL = @"insert into salaXprofessor (codProfessor, codSala)
values (@codProfessor, @codSala);";
using (SqlCommand cmd = new SqlCommand(strSQL))
{
cmd.Connection = conn;
cmd.Parameters.Add("@codProfessor", SqlDbType.Int).Value = obj.Professor.Cod;
cmd.Parameters.Add("@codSala", SqlDbType.Int).Value = obj.Sala.Cod;
foreach (SqlParameter parameter in cmd.Parameters)
{
if (parameter.Value == null)
{
parameter.Value = DBNull.Value;
}
}
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
public List <SalaXProfessor> BuscarPorSala(Sala obj)
{
var lst = new List <SalaXProfessor>();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Db"].ConnectionString))
{
string strSQL = @"SELECT
SALAXPROFESSOR.*,
USUARIO.NOME,
USUARIO.EMAIL ,
SALA.NOME as NOME_SALA
FROM SALAXPROFESSOR
INNER JOIN USUARIO ON (SALAXPROFESSOR.CODPROFESSOR = USUARIO.COD)
INNER JOIN SALA ON (SALAXPROFESSOR.CODSALA = SALA.COD)
WHERE CODSALA = @CODSALA;";
using (SqlCommand cmd = new SqlCommand(strSQL))
{
conn.Open();
cmd.Connection = conn;
cmd.Parameters.Add("@CODSALA", SqlDbType.Int).Value = obj.Cod;
cmd.CommandText = strSQL;
var dataReader = cmd.ExecuteReader();
var dt = new DataTable();
dt.Load(dataReader);
conn.Close();
foreach (DataRow row in dt.Rows)
{
var salaXprofessor = new SalaXProfessor()
{
Professor = new Usuario()
{
Cod = Convert.ToInt32(row["CODPROFESSOR"]),
Nome = row["NOME"].ToString(),
Email = row["EMAIL"].ToString()
},
Sala = new Sala()
{
Cod = Convert.ToInt32(row["CODSALA"]),
Nome = row["NOME_SALA"].ToString()
}
};
lst.Add(salaXprofessor);
}
}
}
return(lst);
}
public List <SalaXProfessor> BuscarTodos()
{
var lst = new List <SalaXProfessor>();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Db"].ConnectionString))
{
string strSQL = @"select
u.cod,
u.nome,
u.email
from SalaXProfessor s
inner join Usuario u on s.codProfessor = u.cod";
using (SqlCommand cmd = new SqlCommand(strSQL))
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = strSQL;
var dataReader = cmd.ExecuteReader();
var dt = new DataTable();
dt.Load(dataReader);
conn.Close();
foreach (DataRow row in dt.Rows)
{
var SalaXProfessor = new SalaXProfessor()
{
Professor = new Usuario()
{
Nome = row["nome"].ToString(),
Email = row["email"].ToString()
},
Sala = new Sala()
{
Nome = row["nome"].ToString()
}
};
lst.Add(SalaXProfessor);
}
}
}
return(lst);
}
public ActionResult InserirProfessor(int codSala, int codProfessor)
{
var obj = new SalaXProfessor()
{
Sala = new Sala()
{
Cod = codSala
},
Professor = new Usuario()
{
Cod = codProfessor
}
};
new SalaXProfessorDAO().Inserir(obj);
var sala = new SalaDAO().BuscarPorCod(codSala);
sala.Professores = new SalaXProfessorDAO().BuscarPorSala(sala);
return(PartialView("_Professores", sala));
}