public int updateUsuario(BeanTurma tu, String tuA)//perguntar pro professor esse metodo
{
String sql = "UPDATE SchoolManager.dbo.turmas SET nome_turma = @nome "
+ " WHERE nome_turma= @turmaA ;";
int i = 0;
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@nome", tu.Nome_turma);
command.Parameters.AddWithValue("@turmaA", tuA);
try
{
i = command.ExecuteNonQuery();
return(i);
}
catch (Exception e)
{
Debug.WriteLine("Falha ao atualizar dados: " + e);
return(i);
}
}
}
}
public List <BeanTurma> selectAll()
{
List <BeanTurma> list = new List <BeanTurma>();
String sql = "SELECT DISTINCT * FROM SchoolManager.dbo.turmas ORDER BY nome_turma DESC";
SqlConnection conn;
// BeanUsuario usu = new BeanUsuario();
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
try
{
SqlDataReader objDataReader = command.ExecuteReader();
if (objDataReader.HasRows)
{
while (objDataReader.Read())
{
BeanTurma tu = new BeanTurma();
tu.Nome_turma = objDataReader["nome_turma"].ToString();
list.Add(tu);
}
objDataReader.Close();
}
conn.Close();
}
catch (Exception e)
{
Debug.WriteLine("Erros ao selecionar todos usuarios: " + e);
}
}
}
return(list);
}
public List <BeanAluno> selectAll()
{
List <BeanAluno> list = new List <BeanAluno>();
String sql = "SELECT DISTINCT * FROM SchoolManager.dbo.cadaluno ca,SchoolManager.dbo.responsavel res, "
+ " SchoolManager.dbo.turmas tu WHERE ca.responsavel_fk = res.idresponsavel AND ca.turma_fk = tu.nome_turma "
+ " ORDER BY ca.idaluno DESC";
SqlConnection conn;
// BeanUsuario usu = new BeanUsuario();
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
try
{
SqlDataReader objDataReader = command.ExecuteReader();
if (objDataReader.HasRows)
{
while (objDataReader.Read())
{
BeanAluno al = new BeanAluno();
BeanEscola esc = new BeanEscola();
BeanResponsavel res = new BeanResponsavel();
BeanTurma tu = new BeanTurma();
al.Idaluno = int.Parse(objDataReader["idaluno"].ToString());
al.Nome_aluno = objDataReader["nome_aluno"].ToString();
al.Dt_nasc = Convert.ToDateTime(objDataReader["dt_nasc"].ToString());
tu.Nome_turma = objDataReader["turma_fk"].ToString();
al.Turma = tu;
/*esc.Cnpj = objDataReader["cadescola_fk"].ToString();
* esc.Nome_escola = objDataReader["nome_escola"].ToString();
* esc.Email = objDataReader["email"].ToString();
* al.Escola = esc;*/
res.Idresponsavel = int.Parse(objDataReader["responsavel_fk"].ToString());
res.Nome_responsavel = objDataReader["nome_responsavel"].ToString();
al.Responsavel = res;
list.Add(al);
}
objDataReader.Close();
}
conn.Close();
}
catch (Exception e)
{
Debug.WriteLine("Erros ao selecionar todos usuarios: " + e);
}
}
}
return(list);
}
public String updateTurma(String json, String turmaantes)
{
JavaScriptSerializer js = new JavaScriptSerializer();
BeanTurma obje = js.Deserialize <BeanTurma>(json);
TurmaDAO tu = new TurmaDAO();
int i = 0;
i = tu.updateUsuario(obje, turmaantes);
return(js.Serialize(i));
}
public List <BeanAluno> selectID(BeanAluno al)
{
List <BeanAluno> list = new List <BeanAluno>();
String sql = "SELECT DISTINCT * FROM SchoolManager.dbo.cadaluno WHERE idaluno = @id";
SqlConnection conn;
// BeanUsuario usu = new BeanUsuario();
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@id", al.Idaluno);
try
{
SqlDataReader objDataReader = command.ExecuteReader();
if (objDataReader.HasRows)
{
while (objDataReader.Read())
{
BeanAluno a = new BeanAluno();
BeanEscola esc = new BeanEscola();
BeanResponsavel res = new BeanResponsavel();
BeanTurma tu = new BeanTurma();
a.Idaluno = int.Parse(objDataReader["idaluno"].ToString());
a.Nome_aluno = objDataReader["nome_aluno"].ToString();
a.Dt_nasc = Convert.ToDateTime(objDataReader["dt_nasc"].ToString());
tu.Nome_turma = objDataReader["turma_fk"].ToString();
a.Turma = tu;
esc.Cnpj = objDataReader["cadescola_fk"].ToString();
a.Escola = esc;
res.Idresponsavel = int.Parse(objDataReader["idresponsavel_fk"].ToString());
a.Responsavel = res;
list.Add(a);
}
objDataReader.Close();
}
conn.Close();
}
catch (Exception e)
{
Debug.WriteLine("Erros ao selecionar todos usuarios: " + e);
}
}
}
return(list);
}
public String turmaDel(String json)
{
int i = 0;
try
{
// Deserializando a string para Object
JavaScriptSerializer serializer = new JavaScriptSerializer();
BeanTurma obje = serializer.Deserialize <BeanTurma>(json);
TurmaDAO tu = new TurmaDAO();
i = tu.deleteUsuario(obje);
}
catch (WebException ex)
{
Debug.WriteLine("Erro: " + ex);
}
return(i.ToString());
}
public int insertUsuario(BeanTurma tu)
{
int i = 0;
String sql = "INSERT INTO SchoolManager.dbo.turmas(nome_turma) VALUES(@nome);";
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@nome", tu.Nome_turma);
try
{
i = command.ExecuteNonQuery();
conn.Close();
}
catch (Exception e)
{
Debug.WriteLine("Falha ao inserir dados: " + e);
}
}
}
return(i);
}
protected void listaUsuarios_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int i = e.RowIndex;
String nome_turma = ((Label)listaUsuarios.Rows[i].FindControl("Lturma")).Text;
Debug.WriteLine("RowDeleting chamado Indice da Linha: " + i + "TURMA:" + nome_turma);
BeanTurma u = new BeanTurma();
u.Nome_turma = nome_turma;
JavaScriptSerializer serializer = new JavaScriptSerializer();
String result = ws.turmaDel(serializer.Serialize(u));
if (result == "0")
{
Session["msg"] = "Turma não foi excluida!";
Response.Redirect("cadastroturma.aspx");
}
else
{
Session["msg"] = "Turma excluída com sucesso!";
Response.Redirect("cadastroturma.aspx");
}
}
public int deleteUsuario(BeanTurma tu)
{
String sql = "DELETE FROM SchoolManager.dbo.turmas WHERE nome_turma = @pid";
SqlConnection conn;
int i = 0;
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@pid", tu.Nome_turma);
try
{
i = command.ExecuteNonQuery();
}
catch (Exception e)
{
Debug.WriteLine("Falha ao deletar dados: " + e);
}
conn.Close();
}
}
return(i);
}
protected void enviar_Click(object sender, EventArgs e)
{
Utils utils = new Utils();
String rel = "";
String json = "";
BeanAluno ba = new BeanAluno();
BeanTurma bt = new BeanTurma();
BeanResponsavel br = new BeanResponsavel();
if (id.Text == "")
{
ba.Nome_aluno = tb_nome.Text;
ba.Dt_nasc = DateTime.Parse(tb_datanasc.Text);
bt.Nome_turma = tb_turma.Text;
ba.Turma = bt;
br.Idresponsavel = int.Parse(tb_responsavel.Text);
ba.Responsavel = br;
/*nome_aluno = tb_nome.Text,
* dt_nasc = tb_datanasc.Text,
* turma_fk = tb_turma.Text,
* responsavel_fk = tb_responsavel.Text*/
json = new JavaScriptSerializer().Serialize(ba);
rel = ws.alunoIn(json);
if (rel.Equals("\"0\""))
{
Session["msg"] = "Problema ao inserir aluno";
Response.Redirect("cadastroaluno.aspx");
}
else if (rel.Equals("\"1\""))
{
Session["msg"] = "Aluno inserido com sucesso!";
Response.Redirect("cadastroaluno.aspx");
}
else if (rel.Equals("\"2\""))
{
Session["msg"] = "Aluno já cadastrado no sistema!";
Response.Redirect("cadastroaluno.aspx");
}
}
else
{
json = new JavaScriptSerializer().Serialize(new
{
idaluno = id.Text,
nome_aluno = tb_nome.Text,
dt_nasc = tb_datanasc.Text,
turma_fk = tb_turma.Text,
responsavel_fk = tb_responsavel.Text
});
rel = ws.alunoDel(json);
Debug.WriteLine("Valor do retorno: " + rel);
if (rel == "0")
{
Session["msg"] = "Problemas ao atualizar aluno";
Response.Redirect("painelescola.aspx");
}
else if (rel == "1")
{
Session["msg"] = "Aluno atualizado com sucesso!";
Response.Redirect("painelescola.aspx");
}
else if (rel == "2")
{
Session["msg"] = "Aluno já cadastrado no sistema!";
Response.Redirect("painelescola.aspx");
}
}
}
