2
I have an SQL database with two tables. One of them stores measures created(table 1) and other actions of these measures(table 2).
I have entered on form one datagridView with the data of table 1 and created a column with the name "SITUACAO" which indicates the status of the last action created for that measure. For example "late", "concluded" or "on time". This status is referenced to table 2.
The column "SITUACAO" I filled it with a FOR that goes all table 2 and checks the status of the action taken on that measure.
dtg.Rows[i].Cells["SITUACAO"].Value = "no prazo";
datagridView was populated correctly with the data and the column "SITUATION".
I would like to make a filter using the column "SITUATION"
`((DataTable)dtg.DataSource).DefaultView.RowFilter = string.Format("SITUACAO = 'no prazo'");`
Using the row filter shows an error indicating that the column does not exist. I know the column "SITUATION" is not in the data source.
- Is there any other way to make this filter?
Code
private void frmDossie_Load(object sender, EventArgs e)
{
ListarDossie();
}
private void ListarDossie()
{
try
{
novoDossie = new RegraNegocio.DossieRegraNegocio();
dtgDossie.DataSource = novoDossie.ListarDossie();
Status();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Status()
{
int linhas = dtgDossie.Rows.Count;
double dataSituacao;
DateTime dataFinal, dataInicial;
DataTable dadosTabela = new DataTable();
novoDossie = new RegraNegocio.DossieRegraNegocio();
dadosTabela = novoDossie.ListarAcoes();
int linhas2 = dadosTabela.Rows.Count;
if (linhas > 0)
{
for (int i = 0; i < linhas; i++)
{
for (int j = 0; j < linhas2; j++)
{
dataInicial = Convert.ToDateTime(dadosTabela.Rows[j]["DATA_INICIAL"]);
Convert.ToInt32(dataSituacao = DateTime.Today.Subtract(dataInicial).TotalDays);
string tabela = dadosTabela.Rows[j]["ID_DOSSIE"].ToString();
string dtg = dtgDossie.Rows[i].Cells["ID_DOSSIE"].Value.ToString();
switch (Convert.ToInt32(dadosTabela.Rows[j]["ID_TIPO_ACAO"]))
{
case 6: //Obra Concluida
if (tabela == dtg && string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString())==false)
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "OBRA CONCLUÍDA";
break;
case 5: //Envio CCO
if (tabela == dtg)
{
if (Convert.ToDateTime(dtgDossie.Rows[i].Cells["COMPROMISSO_OBRA"].Value).AddDays(Convert.ToInt32(dtgDossie.Rows[i].Cells["REPACTUACAO"].Value)) < DateTime.Today.AddDays(+15))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Prazo Obra Vencendo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) == false)
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "CCO Concluida";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao < Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "CCO no Prazo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao > Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "CCO atrasada";
}
}
break;
case 4: //Construcao
if (tabela == dtg)
{
if (Convert.ToDateTime(dtgDossie.Rows[i].Cells["COMPROMISSO_OBRA"].Value).AddDays(Convert.ToInt32(dtgDossie.Rows[i].Cells["REPACTUACAO"].Value)) < DateTime.Today.AddDays(+15))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Prazo Obra Vencendo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) == false)
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Construção Concluida";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao < Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Construção no Prazo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao > Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Construção atrasada";
}
}
break;
case 3: //Liberacao Obra
if (tabela == dtg)
{
if (Convert.ToDateTime(dtgDossie.Rows[i].Cells["COMPROMISSO_OBRA"].Value).AddDays(Convert.ToInt32(dtgDossie.Rows[i].Cells["REPACTUACAO"].Value)) < DateTime.Today.AddDays(+15))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Prazo Obra Vencendo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) == false)
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Liberação Concluida";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao < Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Liberação no Prazo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao > Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Liberação atrasada";
}
}
break;
case 2: //Envio AF
if (tabela == dtg)
{
if (Convert.ToDateTime(dtgDossie.Rows[i].Cells["COMPROMISSO_OBRA"].Value).AddDays(Convert.ToInt32(dtgDossie.Rows[i].Cells["REPACTUACAO"].Value)) < DateTime.Today.AddDays(+15))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Prazo Obra Vencendo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) == false)
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "AF Concluida";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao < Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Assinatura AF no Prazo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao > Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Assinatura AF atrasada";
}
}
break;
case 1: //Analise
if (tabela == dtg)
{
if (Convert.ToDateTime(dtgDossie.Rows[i].Cells["COMPROMISSO_OBRA"].Value).AddDays(Convert.ToInt32(dtgDossie.Rows[i].Cells["REPACTUACAO"].Value)) < DateTime.Today.AddDays(+15))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Prazo Obra Vencendo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) == false)
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Análise Concluida";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao < Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Análise no Prazo";
}
else if (string.IsNullOrEmpty(dadosTabela.Rows[j]["DATA_FINAL"].ToString()) && dataSituacao > Convert.ToInt32(dadosTabela.Rows[j]["PRAZO"]))
{
dtgDossie.Rows[i].Cells["SITUACAO"].Value = "Análise atrasada";
}
}
break;
}
}
}
}
}
private void btnPesquisa_Click(object sender, EventArgs e)
{
((DataTable)dtgDossie.DataSource).DefaultView.RowFilter = string.Format("SITUACAO = 'Análise no Prazo'");
}
Layer Code Business Rule
public DataTable ListarDossie()
{
try
{
novoDossie = new AcessoDados.DossieAcessoDados();
DataTable dadosTabela = new DataTable();
dadosTabela = novoDossie.ListarDossie();
return dadosTabela;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable ListarAcoes()
{
try
{
novoDossie = new AcessoDados.DossieAcessoDados();
DataTable dadosTabela = new DataTable();
dadosTabela = novoDossie.ListarAcoes();
return dadosTabela;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
Access layer code data
public DataTable ListarDossie()
{
try
{
using (SqlConnection conexao = new SqlConnection(Conexao.stringConexao))
{
conexao.Open();
sql.Append("SELECT PROJETO.NUMERO_PROJETO, CLIENTES.NOME_CLIENTE, DOSSIE.*, TIPO_DOSSIE.TIPO_DOSSIE FROM DOSSIE");
sql.Append(" INNER JOIN PROJETO ON PROJETO.ID_PROJETO = DOSSIE.ID_PROJETO");
sql.Append(" INNER JOIN DADOS_OBRA ON PROJETO.ID_PROJETO = DADOS_OBRA.ID_PROJETO");
sql.Append(" INNER JOIN CLIENTES ON DADOS_OBRA.ID_CLIENTE = CLIENTES.ID_CLIENTE");
sql.Append(" INNER JOIN TIPO_DOSSIE ON TIPO_DOSSIE.ID_TIPO_DOSSIE = DOSSIE.ID_TIPO_DOSSIE");
comandoSql.CommandText = sql.ToString();
comandoSql.Connection = conexao;
dadosTabela.Load(comandoSql.ExecuteReader());
return dadosTabela;
}
}
catch (Exception)
{
throw new Exception("Ocorreu um erro no método ListarDossie. Caso o problema persista, entre em contato com o Administrador do sistema.");
}
}
public DataTable ListarAcoes()
{
try
{
using (SqlConnection conexao = new SqlConnection(Conexao.stringConexao))
{
conexao.Open();
sql.Append("SELECT * FROM ACAO_DOSSIE");
comandoSql.CommandText = sql.ToString();
comandoSql.Connection = conexao;
dadosTabela.Load(comandoSql.ExecuteReader());
return dadosTabela;
}
}
catch (Exception)
{
throw new Exception("Ocorreu um erro no método ListarAcoes. Caso o problema persista, entre em contato com o Administrador do sistema.");
}
}
Exists, post your code the name and structure of the tables
– Leandro Angelo
I added a piece of code to help solve my problem. See if it fits or needs some more. @Leandroangelo
– Antonio Ferreira