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; } }