public int Alterar(RELATORIOS_DTO DTO)
{
using (SqlConnection cn = new SqlConnection(strConnection))
{
try
{
SqlDataReader dr = null;
StringBuilder SQL_ = new StringBuilder();
SQL_.Append("UPDATE ");
SQL_.Append("RELATORIOS ");
SQL_.Append("SET ");
SQL_.Append("NOME = @NOME, ");
SQL_.Append("HTML = @HTML, ");
SQL_.Append("LISTAHTML1 = @LISTAHTML1, ");
SQL_.Append("LISTAHTML2 = @LISTAHTML2, ");
SQL_.Append("COLUNAS_GROUP = @COLUNAS_GROUP, ");
SQL_.Append("USUARIO = @USUARIO, ");
SQL_.Append("ULT_ATUAL = @ULT_ATUAL, ");
SQL_.Append("QUERY = @QUERY ");
SQL_.Append("WHERE ID = @ID ");
cn.Open();
SqlCommand cmd = new SqlCommand(SQL_.ToString(), cn);
PopularParametros(DTO, cmd);
cmd.ExecuteNonQuery();
return(DTO.ID);
}
catch (SqlException ex)
{
throw new Exception(ex.ToString());
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
cn.Close();
}
}
}
public RELATORIOS_DTO Get_Relatorio_By_Id(int ID)
{
using (SqlConnection cn = new SqlConnection(strConnection))
{
RELATORIOS_DTO DTO = new RELATORIOS_DTO();
try
{
SqlDataReader dr = null;
StringBuilder SQL_ = new StringBuilder();
SQL_.Append("SELECT ");
SQL_.Append("* ");
SQL_.Append("FROM ");
SQL_.Append("RELATORIOS ");
SQL_.Append("WHERE ");
SQL_.Append("ID = @ID ");
cn.Open();
SqlCommand cmd = new SqlCommand(SQL_.ToString(), cn);
cmd.Parameters.AddWithValue("@ID", ID);
dr = cmd.ExecuteReader();
if (dr.Read())
{
PopularDados(dr, DTO);
SysDAL.GuardarDTO((IDTO)DTO.Clone());
}
return(DTO);
}
catch (SqlException ex)
{
throw new Exception(ex.ToString());
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
cn.Close();
}
}
}
public List <RELATORIOS_DTO> Listar()
{
using (SqlConnection cn = new SqlConnection(strConnection))
{
List <RELATORIOS_DTO> list = new List <RELATORIOS_DTO>();
try
{
SqlDataReader dr = null;
StringBuilder SQL_ = new StringBuilder();
SQL_.Append("SELECT ");
SQL_.Append("* ");
SQL_.Append("FROM ");
SQL_.Append("RELATORIOS ");
SQL_.Append("WHERE ");
SQL_.Append("ATIVO = 1 ");
cn.Open();
SqlCommand cmd = new SqlCommand(SQL_.ToString(), cn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
RELATORIOS_DTO relatorios = new RELATORIOS_DTO();
PopularDados(dr, relatorios);
list.Add(relatorios);
}
return(list);
}
catch (SqlException ex)
{
throw new Exception(ex.ToString());
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
cn.Close();
}
}
}
public void PopularDados(SqlDataReader dtr, RELATORIOS_DTO DTO)
{
try
{
DTO.ID = Convert.ToInt32(dtr["ID"]);
DTO.NOME = dtr["NOME"].ToString();
DTO.HTML = dtr["HTML"].ToString();
DTO.LISTAHTML1 = dtr["LISTAHTML1"].ToString();
DTO.LISTAHTML2 = dtr["LISTAHTML2"].ToString();
DTO.QUERY = dtr["QUERY"].ToString();
DTO.COLUNAS_GROUP = dtr["COLUNAS_GROUP"].ToString();
DTO.USUARIO = dtr["USUARIO"].ToString();
DTO.ULT_ATUAL = dtr["ULT_ATUAL"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dtr["ULT_ATUAL"]);
DTO.GERARHTML = Convert.ToBoolean(dtr["GERARHTML"]);
}
catch (Exception ex)
{
throw ex;
}
}
public int Registrar(RELATORIOS_DTO DTO)
{
try
{
if (DTO.OPERACAO == SysDTO.Operacoes.Inclusao)
{
return(DAO.Inserir(DTO));
}
else if (DTO.OPERACAO == SysDTO.Operacoes.Alteracao)
{
return(DAO.Alterar(DTO));
}
else
{
return(0);
}
}
catch (Exception ex)
{
throw ex;
}
}
public void PopularParametros(RELATORIOS_DTO DTO, SqlCommand cmd)
{
cmd.Parameters.AddWithValue("@ID", DTO.ID);
cmd.Parameters.AddWithValue("@NOME", DTO.NOME);
cmd.Parameters.AddWithValue("@HTML", DTO.HTML);
cmd.Parameters.AddWithValue("@LISTAHTML1", DTO.LISTAHTML1);
cmd.Parameters.AddWithValue("@LISTAHTML2", DTO.LISTAHTML2);
cmd.Parameters.AddWithValue("@COLUNAS_GROUP", DTO.COLUNAS_GROUP);
cmd.Parameters.AddWithValue("@QUERY", DTO.QUERY);
cmd.Parameters.AddWithValue("@USUARIO", DTO.USUARIO);
cmd.Parameters.AddWithValue("@ULT_ATUAL", DTO.ULT_ATUAL);
cmd.Parameters.AddWithValue("@GERARHTML", DTO.GERARHTML);
//Substitui o null por DBnull
foreach (SqlParameter Parameter in cmd.Parameters)
{
if (Parameter.Value == null)
{
Parameter.Value = DBNull.Value;
}
}
}
public int Inserir(RELATORIOS_DTO DTO)
{
using (SqlConnection cn = new SqlConnection(strConnection))
{
try
{
SqlDataReader dr = null;
StringBuilder SQL_ = new StringBuilder();
SQL_.Append("INSERT INTO ");
SQL_.Append("RELATORIOS ");
SQL_.Append("( ");
SQL_.Append("NOME, ");
SQL_.Append("HTML, ");
SQL_.Append("LISTAHTML1, ");
SQL_.Append("LISTAHTML2, ");
SQL_.Append("COLUNAS_GROUP, ");
SQL_.Append("USUARIO, ");
SQL_.Append("ULT_ATUAL, ");
SQL_.Append("QUERY ");
SQL_.Append(") ");
SQL_.Append("VALUES ");
SQL_.Append("( ");
SQL_.Append("@NOME, ");
SQL_.Append("@HTML, ");
SQL_.Append("@LISTAHTML1, ");
SQL_.Append("@LISTAHTML2, ");
SQL_.Append("@COLUNAS_GROUP, ");
SQL_.Append("@USUARIO, ");
SQL_.Append("@ULT_ATUAL, ");
SQL_.Append("@QUERY ");
SQL_.Append("); SELECT SCOPE_IDENTITY(); ");
cn.Open();
SqlCommand cmd = new SqlCommand(SQL_.ToString(), cn);
PopularParametros(DTO, cmd);
if ((DTO.ID = Convert.ToInt32(cmd.ExecuteScalar())) > 0)
{
return(DTO.ID);
}
return(0);
}
catch (SqlException ex)
{
throw new Exception(ex.ToString());
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
cn.Close();
}
}
}
private void PopularGrid(int Id, bool SomenteCarregaDto = false, bool ReloadSql = false)
{
try
{
//Setando o mousepointer para ocupado.
Cursor.Current = Cursors.WaitCursor;
this.Cursor = Cursors.WaitCursor;
RELATORIOS_DTO relatorios = new RELATORIOS_DTO();
tssMSG.Text = "Aguarde. Processando Dados...";
tssMSG.Visible = true;
Application.DoEvents();
if (Id != 0)
{
relatorios = new RELATORIOS_BLL().Get_Relatorio_By_Id(Id);
if (ReloadSql == true)
{
relatorios.ID = Id;
relatorios.LISTAHTML1 = txtListaHTML1.Text;
relatorios.LISTAHTML2 = txtListaHTML2.Text;
relatorios.NOME = txtNOME.Text;
relatorios.QUERY = txtQuery.Text;
relatorios.HTML = txtHTML.Text;
relatorios.COLUNAS_GROUP = txtGroupBy.Text;
}
}
else //String sql
{
relatorios.LISTAHTML1 = txtListaHTML1.Text;
relatorios.LISTAHTML2 = txtListaHTML2.Text;
relatorios.NOME = txtNOME.Text;
relatorios.QUERY = txtQuery.Text;
relatorios.HTML = txtHTML.Text;
relatorios.COLUNAS_GROUP = txtGroupBy.Text;
}
//Atribui os inputbox a string sql
if (SomenteCarregaDto == true)
{
relatorios.SqlChanged = relatorios.QUERY;
}
else
{
relatorios.SqlChanged = GeraInput(relatorios.QUERY);
}
//Se voltar o GeraInput = "" aborta a rotina
if (relatorios.SqlChanged == "")
{
return;
}
//Tranfere a DTO recuperada para a página main
frmRelatorios.relatorio_dto = relatorios;
//Se for apenas para carregar a DTO
if (SomenteCarregaDto == true)
{
return;
}
////Monta o grid e recupera as colunas utilizadas para pesquisa
DataTable dtt = new PesquisaGeralBLL().Pesquisa(frmRelatorios.relatorio_dto.SqlChanged);
frmRelatorios.dtgDados.DataSource = dtt; //Vincula o datatable ao datagrid
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
throw ex;
}
finally
{
this.Cursor = Cursors.Default;
tssMSG.Visible = false;
}
}
