/// <summary>
/// altera Um resumo na tabela resumo
/// No conteudo somente necessitando do ID do Conteudo
/// </summary>
/// <param name="R">parametro do tipo Resumo | com id</param>
public void Alterar(Resumo R)
{
SqlCommand comm = new SqlCommand("", Banco.Abrir());
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "AlterarResumo";
comm.Parameters.Add("@ID", SqlDbType.Int).Value = R.ID;
comm.Parameters.Add("@Conteudo", SqlDbType.Int).Value = R.Conteudo.ID;
comm.Parameters.Add("@NomeArquivo", SqlDbType.VarChar).Value = R.NomeArquivo;
comm.Parameters.Add("@Arquivo", SqlDbType.VarBinary).Value = R.Arquivo;
comm.Parameters.Add("@Extensao", SqlDbType.Char).Value = R.Extensao;
comm.Parameters.Add("@Usuario", SqlDbType.Int).Value = R.Usuario;
comm.ExecuteNonQuery();
comm.Connection.Close();
}
/// <summary>
/// Insere Um resumo na tabela resumo
/// No conteudo somente necessitando do ID do Conteudo
/// </summary>
/// <param name="R"> parametro do tipo Resumo | sem id </param>
public int Inserir(Resumo R)
{
SqlCommand comm = new SqlCommand("",Banco.Abrir());
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "InserirResumo";
comm.Parameters.Add("@Conteudo", SqlDbType.Int).Value =R.Conteudo.ID;
comm.Parameters.Add("@NomeArquivo", SqlDbType.VarChar).Value = R.NomeArquivo;
comm.Parameters.Add("@Arquivo", SqlDbType.VarBinary).Value = R.Arquivo;
comm.Parameters.Add("@Extensao", SqlDbType.Char).Value = R.Extensao;
comm.Parameters.Add("@Usuario", SqlDbType.Int).Value = R.Usuario;
comm.ExecuteNonQuery();
comm.CommandType = CommandType.Text;
comm.CommandText = "Select top 1 ID_Resumo from Resumo where ID_Conteudo = "+R.Conteudo.ID+" order by ID_Resumo desc";
int id = Convert.ToInt32(comm.ExecuteScalar());
comm.Connection.Close();
return id;
}
/// <summary>
/// Retorna um Objeto do tipo resumo
/// Com o conteudo somente retornando com o ID
/// </summary>
/// <param name="id"> parametro do tipo inteiro representando o ID do Resumo </param>
/// <returns></returns>
public Resumo Consultar(int id)
{
SqlCommand comm = new SqlCommand("Select * from Resumo Where ID_Resumo = "+id,Banco.Abrir());
SqlDataReader dr = comm.ExecuteReader();
Resumo r = new Resumo();
while (dr.Read())
{
r = new Resumo
{
ID = Convert.ToInt32(dr.GetValue(0)),
Conteudo = new Conteudo { ID = Convert.ToInt32(dr.GetValue(1)) },
NomeArquivo=dr.GetValue(2).ToString(),
Arquivo = dr.GetValue(3) as byte[],
Extensao = dr.GetValue(4).ToString(),
Usuario = Convert.ToInt32(dr.GetValue(5))
};
}
comm.Connection.Close();
return r;
}
/// <summary>
/// retorna um objeto do tipo Conteudo
/// Contendo so o ID Da Materia
/// E um List de ConteudoTexto (completo) já organizado em ordem crescente
/// list de resumos vinculado e exercicios completos
/// </summary>
/// <param name="id"> parametro inteiro do id do conteudo</param>
/// <returns></returns>
public Conteudo Consultar(int id)
{
SqlCommand comm = new SqlCommand("Select * from Conteudo where ID_Conteudo = " + id, Banco.Abrir());
SqlDataReader dr = comm.ExecuteReader();
Conteudo c = new Conteudo();
while (dr.Read())
{
Materia m = new Materia();
m.ID = Convert.ToInt32(dr.GetValue(1));
c = new Conteudo
{
ID = Convert.ToInt32(dr.GetValue(0)),
Materia = m,
Nome = dr.GetValue(2).ToString(),
Ordem = Convert.ToInt32(dr.GetValue(4)),
Usuario = Convert.ToInt32(dr.GetValue(5)),
ConteudoTexto = null,
Exercicio = null,
Imagem = null,
Resumo = null
};
if (dr.GetValue(3) != null)
{
c.Imagem = dr.GetValue(3) as byte[];
}
}
dr.Close();
comm.CommandText = "Select ID_ConteudoTexto,Ordem_ConteudoTexto from ConteudoTexto where ID_Conteudo = " + id + " order by Ordem_ConteudoTexto";
dr = comm.ExecuteReader();
List <ConteudoTexto> listacont = new List <ConteudoTexto>();
while (dr.Read())
{
ConteudoTextoDAL dalcontext = new ConteudoTextoDAL();
ConteudoTexto ct = new ConteudoTexto();
ct = dalcontext.Consultar(Convert.ToInt32(dr.GetValue(0)));
listacont.Add(ct);
}
c.ConteudoTexto = listacont;
dr.Close();
comm.CommandText = "Select ID_Resumo from Resumo where ID_Conteudo = " + id;
dr = comm.ExecuteReader();
List <Resumo> listaresumo = new List <Resumo>();
while (dr.Read())
{
ResumoDAL dalresu = new ResumoDAL();
Resumo r = new Resumo();
r = dalresu.Consultar(Convert.ToInt32(dr.GetValue(0)));
listaresumo.Add(r);
}
c.Resumo = listaresumo;
dr.Close();
comm.CommandText = "Select ID_Exercicio from Exercicio where ID_Conteudo = " + id;
dr = comm.ExecuteReader();
List <Exercicio> listaexrs = new List <Exercicio>();
while (dr.Read())
{
ExercicioDAL dalex = new ExercicioDAL();
Exercicio e = new Exercicio();
e = dalex.Consultar(Convert.ToInt32(dr.GetValue(0)));
listaexrs.Add(e);
}
c.Exercicio = listaexrs;
comm.Connection.Close();
return(c);
}