public int updateUsuario(BeanResponsavel re)
{
String sql = "UPDATE SchoolManager.dbo.responsavel SET nome_responsavel = @nome, login = @login, senha = @senha "
+ " WHERE idresponsavel= @id ;";
int i = 0;
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@nome", re.Nome_responsavel);
command.Parameters.AddWithValue("@login", re.Login);
command.Parameters.AddWithValue("@senha", re.Senha);
command.Parameters.AddWithValue("@id", re.Idresponsavel);
try
{
i = command.ExecuteNonQuery();
return(i);
}
catch (Exception e)
{
Debug.WriteLine("Falha ao atualizar dados: " + e);
return(i);
}
}
}
}
public int insertUsuario(BeanResponsavel re)
{
int i = 0;
String sql = "INSERT INTO SchoolManager.dbo.responsavel(nome_responsavel,login,senha) VALUES(@nome,@login,@senha);";
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@nome", re.Nome_responsavel);
command.Parameters.AddWithValue("@login", re.Login);
command.Parameters.AddWithValue("@senha", re.Senha);
try
{
i = command.ExecuteNonQuery();
conn.Close();
}
catch (Exception e)
{
Debug.WriteLine("Falha ao inserir dados: " + e);
}
}
}
return(i);
}
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 updateResponsavel(String json)
{
JavaScriptSerializer js = new JavaScriptSerializer();
BeanResponsavel obje = js.Deserialize <BeanResponsavel>(json);
ResponsavelDAO resp = new ResponsavelDAO();
int i = 0;
i = resp.updateUsuario(obje);
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 responsavelDel(String json)
{
int i = 0;
try
{
// Deserializando a string para Object
JavaScriptSerializer serializer = new JavaScriptSerializer();
BeanResponsavel obje = serializer.Deserialize <BeanResponsavel>(json);
ResponsavelDAO resp = new ResponsavelDAO();
i = resp.deleteUsuario(obje);
}
catch (WebException ex)
{
Debug.WriteLine("Erro: " + ex);
}
return(i.ToString());
}
public List <BeanResponsavel> selectAll()
{
List <BeanResponsavel> list = new List <BeanResponsavel>();
String sql = "SELECT DISTINCT * FROM SchoolManager.dbo.responsavel ORDER BY idresponsavel 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())
{
BeanResponsavel re = new BeanResponsavel();
re.Idresponsavel = int.Parse(objDataReader["idresponsavel"].ToString());
re.Nome_responsavel = objDataReader["nome_responsavel"].ToString();
re.Login = objDataReader["login"].ToString();
re.Senha = objDataReader["senha"].ToString();
list.Add(re);
}
objDataReader.Close();
}
conn.Close();
}
catch (Exception e)
{
Debug.WriteLine("Erros ao selecionar todos usuarios: " + e);
}
}
}
return(list);
}
protected void listaUsuarios_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int i = e.RowIndex;
String idresponsavel = ((Label)listaUsuarios.Rows[i].FindControl("Lid")).Text;
Debug.WriteLine("RowDeleting chamado Indice da Linha: " + i + "ID:" + idresponsavel);
BeanResponsavel u = new BeanResponsavel();
u.Idresponsavel = int.Parse(idresponsavel);
JavaScriptSerializer serializer = new JavaScriptSerializer();
String result = ws.responsavelDel(serializer.Serialize(u));
if (result == "0")
{
Session["msg"] = "Responsável não foi excluido!";
// Response.Redirect(Request.RawUrl);
// Response.Redirect("cadastroresponsavel.aspx");
}
else
{
Session["msg"] = "Responsável excluído com sucesso!";
Response.Redirect("cadastroresponsavel.aspx");
}
}
public int deleteUsuario(BeanResponsavel re)
{
String sql = "DELETE FROM SchoolManager.dbo.responsavel WHERE idresponsavel = @pid";
SqlConnection conn;
int i = 0;
using (conn = new Connection().abrirConexao())
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@pid", re.Idresponsavel);
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");
}
}
}
